Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Fun with Transactions - Part II Expand / Collapse
Author
Message
Posted Wednesday, September 15, 2010 9:41 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 12:36 AM
Points: 1,379, Visits: 1,772
Comments posted to this topic are about the item Fun with Transactions - Part II

Thanks & Regards,
Nakul Vachhrajani.
http://beyondrelational.com/modules/2/blogs/77/nakuls-blog.aspx
Be courteous. Drive responsibly.

Follow me on
Twitter: @nakulv_sql
Google Plus: +Nakul
Post #986881
Posted Wednesday, September 15, 2010 10:01 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, March 27, 2014 8:50 AM
Points: 2,163, Visits: 2,184
Another good question, thanks!
Post #986884
Posted Wednesday, September 15, 2010 10:33 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:28 PM
Points: 20,453, Visits: 14,063
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #986899
Posted Thursday, September 16, 2010 1:30 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:54 PM
Points: 5,787, Visits: 8,001
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 MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #986986
Posted Thursday, September 16, 2010 1:35 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 4:48 AM
Points: 2,104, Visits: 5,376
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/
Post #986989
Posted Thursday, September 16, 2010 1:35 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 12:36 AM
Points: 1,379, Visits: 1,772
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://beyondrelational.com/modules/2/blogs/77/nakuls-blog.aspx
Be courteous. Drive responsibly.

Follow me on
Twitter: @nakulv_sql
Google Plus: +Nakul
Post #986990
Posted Thursday, September 16, 2010 2:04 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:53 AM
Points: 12,172, Visits: 9,118
Great question!
I didn't know about the ROLLBACK WORK syntax, so I definitely learned something today.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #987005
Posted Thursday, September 16, 2010 4:28 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, September 07, 2012 5:25 AM
Points: 231, Visits: 61
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....
Post #987071
Posted Thursday, September 16, 2010 4:44 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:53 AM
Points: 12,172, Visits: 9,118
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




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #987084
Posted Thursday, September 16, 2010 5:03 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 12:36 AM
Points: 1,379, Visits: 1,772
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://beyondrelational.com/modules/2/blogs/77/nakuls-blog.aspx
Be courteous. Drive responsibly.

Follow me on
Twitter: @nakulv_sql
Google Plus: +Nakul
Post #987102
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse