Fun with Transactions - Part II

Question

Post reply

Fun with Transactions - Part II

Nakul Vachhrajani

SSChampion

Points: 10294
More actions
September 15, 2010 at 9:41 pm

#240489

Comments posted to this topic are about the item Fun with Transactions - Part II
Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Follow me on
Twitter: @sqltwins

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

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

UMG Developer SSChampion Points: 13482 More actions · Answer 1

UMG Developer

SSChampion

Points: 13482

September 15, 2010 at 10:01 pm

#1221869

Another good question, thanks!

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

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

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 3

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/

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 4

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/

Nakul Vachhrajani SSChampion Points: 10294 More actions · Answer 5

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

Follow me on
Twitter: @sqltwins

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 6

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

vinod.andani-874416 Mr or Mrs. 500 Points: 557 More actions · Answer 7

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....:(

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 8

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

Nakul Vachhrajani SSChampion Points: 10294 More actions · Answer 9

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

Follow me on
Twitter: @sqltwins

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 10

Nice question and good explanation from Hugo. Thanks 🙂

Thanks

tommyh SSCertifiable Points: 6302 More actions · Answer 11

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 😉