Fun with Transactions - Part II

  • Comments posted to this topic are about the item Fun with Transactions - Part II

    Thanks & Regards,
    Nakul Vachhrajani.
    http://nakulvachhrajani.com
    Be courteous. Drive responsibly.

    Follow me on
    Twitter: @sqltwins

  • Another good question, thanks!

  • Good question. I think it is worth noting that this behavior is exhibited because nested transactions do not truly exist in SQL Server.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • CirquedeSQLeil (9/15/2010)


    Good question. I think it is worth noting that this behavior is exhibited because nested transactions do not truly exist in SQL Server.

    I have to disagree with you, Jason. Nested transactions do exist, and they work exactly as I think common sense dictates.

    Transactions are primarily intended to implement the A and I of the ACID properties: atomicity and isolation. Nessting fringes with that goal, and I can easily imagine that the people on the ANSI committee have considered not allowing nested transactions. But that would invalidate many common use cases. If I write a stored procedure that does multiple things but should be considered as a single unit of work, I use BEGIN TRAN and COMMIT TRAN (or ROLLBACK TRAN in case of error) in the procedure code. But what if I next have to implement a stored procedure that is also considered a single unit of work, but that includes the first stored procedure? I use BEGIN TRAN/COMMIT TRAN in the outer procedure; I call the inner procedure, and there we have the nesting. Prohibiting nesting would requiere me to duplicate the code, which I obviously don't want.

    Support for nested transactions is unavoidable. But what are the "most correct" semantics? Consider the example above. The inner stored procedure implements actions 2.1 and 2.2, as a single unit of work. The outer stored procedure implements actions 1, 2.1, 2,2, and 3, also as a single unit of work. And it does so by caling the inner procedure.

    So what should a nested COMMIT do? It can not really commit the changes of actions 2.1 and 2.2. After all, action 3 might still fail, and in that case the whole outer procedure needs to be rolled back, including actions 2.1 and 2.2. Otherwise, the outer procedure would not be atomic. So the only thing the COMMIT can do is to decrease the nesting level counter; actually committing the data has to be postponed until all nesteed transactions have finished.

    Conversely, a ROLLBACK in the nested transaction should roll back ALL open transactions. If it would only rollback the effects of the nested transaction, the outer procedure could continue to perform action 3 and commit. IN that case, actions 1 and 3 are committed, but actions 2.1 and 2.2 are not. The procedure is no longer ACID. Making any rollback, regardless of nesting level, roll back ALL open work prevents that problem.

    This question is actually almost similar to the one two days ago. The only real difference is the use of the extra keywork WORK (that is not required).


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • CirquedeSQLeil (9/15/2010)


    Good question. I think it is worth noting that this behavior is exhibited because nested transactions do not truly exist in SQL Server.

    It is worth mentioning that although nested transaction doesn't exist in SQL Server, you can still rollback parts of transaction and commit the rest of the transaction with the use of save points

    Adi

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Hugo Kornelis (9/16/2010)


    This question is actually almost similar to the one two days ago. The only real difference is the use of the extra keywork WORK (that is not required).

    That is correct, Hugo. In fact there are 2 more on similar lines coming up. I originally encountered the behaviour that was covered in the question 2 days ago. The rest of the questions are derivatives of my research on the original question.

    I hope you like them all 🙂

    Thanks & Regards,
    Nakul Vachhrajani.
    http://nakulvachhrajani.com
    Be courteous. Drive responsibly.

    Follow me on
    Twitter: @sqltwins

  • Great question!

    I didn't know about the ROLLBACK WORK syntax, so I definitely learned something today.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • i strongly disagree with you.. as the rollback/commit statments does not work for alias unless preceeded by TRAN or TRANSACTION so the syntax error.... i chose answer "Error : incorrect syntax".. this is not articulated proper and I lost 2 points here....:(

  • vinod.andani-874416 (9/16/2010)


    i strongly disagree with you.. as the rollback/commit statments does not work for alias unless preceeded by TRAN or TRANSACTION so the syntax error.... i chose answer "Error : incorrect syntax".. this is not articulated proper and I lost 2 points here....:(

    Well, it does work:

    http://msdn.microsoft.com/en-us/library/ms174973.aspx

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • vinod.andani-874416 (9/16/2010)


    i strongly disagree with you.. as the rollback/commit statments does not work for alias unless preceeded by TRAN or TRANSACTION so the syntax error.... i chose answer "Error : incorrect syntax".. this is not articulated proper and I lost 2 points here....:(

    Vinod,

    The "WORK" is not an alias - it's sort-of a keyword. It's not even required - Issuing a simple ROLLBACK (without a user-defined transaction name) is same as issuing a ROLLBACK WORK. You can refer to Hugo's explanation as to how nested transactions work and why ROLLBACK WORK rolls back to the outermost transaction.

    From the MSDN Link referenced, "This statement functions identically to ROLLBACK TRANSACTION except that ROLLBACK TRANSACTION accepts a user-defined transaction name."

    Sorry that you lost 2 points (there are lots more on SSC to win), but I hope that you learnt the use of "ROLLBACK WORK" and how transactions behave.

    Thanks & Regards,
    Nakul Vachhrajani.
    http://nakulvachhrajani.com
    Be courteous. Drive responsibly.

    Follow me on
    Twitter: @sqltwins

  • Nice question and good explanation from Hugo. Thanks 🙂

    Thanks

  • Nakul Vachhrajani (9/16/2010)


    Hugo Kornelis (9/16/2010)


    This question is actually almost similar to the one two days ago. The only real difference is the use of the extra keywork WORK (that is not required).

    That is correct, Hugo. In fact there are 2 more on similar lines coming up. I originally encountered the behaviour that was covered in the question 2 days ago. The rest of the questions are derivatives of my research on the original question.

    I hope you like them all 🙂

    Ah crap. Not more. Have already lost 4 points aint that enough... have mercy 😉

  • Great question, great follow on discussion. Thanks.

  • Thanks Hugo for the explanation.

  • Thanks Nakul for a very good question and to Hugo for the detailed explanation.

    Steve Jimmo
    Sr DBA
    “If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan

Viewing 15 posts - 1 through 15 (of 22 total)

You must be logged in to reply to this topic. Login to reply