Understanding Implicit_Transaction on - confusing behaviour

  • 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

  • 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

  • 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

  • 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.

  • 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