March 27, 2016 at 2:16 am
Hi all,
I do not want autocommit, as i am afraid of making mistakes.
So i set implicit_transactions on , but i did encounter the following behavior -
Behavior 1 - still able to rollback 1 time after setting implicit transaction off after turning it on.
set implicit_transactions on
select @@options & 2 -- return 2;
select * from testtab; -- no rows
insert into testtab values ('a');
select * from testtab; -- return a
rollback work;
select * from testtab; -- no rows
From the above, we can see that implicit transaction on is working. Autocommit is not longer in work.
=================
-- testtab still has no rows at the moment.
Now i tried to turn implicit_transactions off;
set implicit_transactions off
select @@options & 2; -- return 0
insert into testtab values ('a');
select * From testtab; -- return a
rollback work; -- COMMAND COMPLETED SUCCESSFULLY
select * From testtab; -- return no rows
q1) Why am i able to still ROLLBACK after setting implication_transactions off ?
It is only after the above, then it resume as normal (with autocommit) as shown below
insert into testtab values ('a');
select * From testtab; -- return a
rollback work;
-- now it prompt that the corresponding rollback transaction request has no corresponding BEGIN TRANSACTION
select * from testtab; -- return a , autocommit is back in place.
Is what i am experiencing normal ?
Regards,
Noob
March 27, 2016 at 4:00 am
szejiekoh (3/27/2016)
q1) Why am i able to still ROLLBACK after setting implication_transactions off ?
This is simply because the last select within the IMPLICIT_TRANSACTIONS ON section implicitly begins a new transaction which is the one you then roll back in the IMPLICIT_TRANSACTIONS OFF section, after that no new transactions are opened, hence the second rollback fails.
😎
Consider this example
USE Test;
GO
SET NOCOUNT ON;
GO
IF OBJECT_ID(N'dbo.TBL_TEST_TRAN') IS NOT NULL DROP TABLE dbo.TBL_TEST_TRAN;
CREATE TABLE dbo.TBL_TEST_TRAN
(
TTR_ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_DBO_TBL_TEST_TRAN_TTR_ID PRIMARY KEY CLUSTERED
,TTR_VALUE VARCHAR(50) NOT NULL
);
GO
SET IMPLICIT_TRANSACTIONS ON
DECLARE @IMPLICIT_TRANSACTIONS VARCHAR(3) = 'OFF';
IF ( (2 & @@OPTIONS) = 2 ) SET @IMPLICIT_TRANSACTIONS = 'ON';
SELECT @IMPLICIT_TRANSACTIONS AS IMPLICIT_TRANSACTIONS;
INSERT INTO DBO.TBL_TEST_TRAN (TTR_VALUE)
VALUES ('A');
SELECT @@TRANCOUNT AS TRANCOUNT; -- 1 OPEN TRANSACTION
INSERT INTO DBO.TBL_TEST_TRAN (TTR_VALUE)
VALUES ('B');
SELECT @@TRANCOUNT AS TRANCOUN; -- 1 OPEN TRANSACTION
INSERT INTO DBO.TBL_TEST_TRAN (TTR_VALUE)
VALUES ('C');
SELECT @@TRANCOUNT AS TRANCOUN; -- 1 OPEN TRANSACTION
ROLLBACK WORK;
SELECT @@TRANCOUNT AS TRANCOUN; -- 0 OPEN TRANSACTION
SELECT
TTR.TTR_ID
,TTR.TTR_VALUE
FROM DBO.TBL_TEST_TRAN TTR; -- NO ROWS
SELECT @@TRANCOUNT AS TRANCOUN; -- 1 OPEN TRANSACTION
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK WORK;
SELECT '1 TRANSACTION ROLLED BACK';
END
ELSE
SELECT 'NO OPEN TRANSACTION';
Output
IMPLICIT_TRANSACTIONS
---------------------
ON
TRANCOUNT
-----------
1
TRANCOUN
-----------
1
TRANCOUN
-----------
1
TRANCOUN
-----------
0
TTR_ID TTR_VALUE
----------- --------------------------------------------------
TRANCOUN
-----------
1
-------------------------
1 TRANSACTION ROLLED BACK
March 27, 2016 at 9:56 am
Hi Eirikur,
Thanks for your reply!
So actually SELECT will start a transaction as well...
q1) can you actually let me know what does @@option & 2 means ? ( i know we can use it to see is implicit transaction is on or off) but what actually does it meant, especially on the "& 2" ?
Regards,
Noob
March 27, 2016 at 10:17 am
szejiekoh (3/27/2016)
Hi Eirikur,Thanks for your reply!
So actually SELECT will start a transaction as well...
q1) can you actually let me know what does @@option & 2 means ? ( i know we can use it to see is implicit transaction is on or off) but what actually does it meant, especially on the "& 2" ?
Regards,
Noob
@@OPTION returns a bitmask for all the 15 user options for the SET or (sp_configure) options, @@option & 2 will return 2 if that option is set.
😎
More about the options on BOL here.
March 27, 2016 at 10:26 am
thank you! would greatly appreciate if you can see my 2nd post on implicit transaction - with insert blocking select.
Thanks!
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply