<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss content posted by Nakul Vachhrajani
»
Fun with Transactions - Part II
22 posts, Page 1 of 3
1
2
3
»
»»
Fun with Transactions - Part II
Rate Topic
Display Mode
Topic Options
Author
Message
Nakul Vachhrajani
Nakul Vachhrajani
Posted Wednesday, September 15, 2010 9:41 PM
Ten Centuries
Group: General Forum Members
Last Login: 2 days ago @ 1:02 AM
Points: 1,277,
Visits: 1,609
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
UMG Developer
UMG Developer
Posted Wednesday, September 15, 2010 10:01 PM
SSCrazy
Group: General Forum Members
Last Login: Monday, May 13, 2013 11:21 AM
Points: 2,163,
Visits: 2,148
Another good question, thanks!
Post #986884
SQLRNNR
SQLRNNR
Posted Wednesday, September 15, 2010 10:33 PM
SSCoach
Group: General Forum Members
Last Login: Yesterday @ 10:25 AM
Points: 18,754,
Visits: 12,337
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 2008
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
Hugo Kornelis
Hugo Kornelis
Posted Thursday, September 16, 2010 1:30 AM
SSCertifiable
Group: General Forum Members
Last Login: Today @ 3:49 PM
Points: 5,244,
Visits: 7,063
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
Adi Cohn-120898
Adi Cohn-120898
Posted Thursday, September 16, 2010 1:35 AM
SSCrazy
Group: General Forum Members
Last Login: Wednesday, May 22, 2013 9:50 AM
Points: 2,019,
Visits: 4,923
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
Nakul Vachhrajani
Nakul Vachhrajani
Posted Thursday, September 16, 2010 1:35 AM
Ten Centuries
Group: General Forum Members
Last Login: 2 days ago @ 1:02 AM
Points: 1,277,
Visits: 1,609
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
Koen Verbeeck
Koen Verbeeck
Posted Thursday, September 16, 2010 2:04 AM
SSCrazy Eights
Group: General Forum Members
Last Login: Yesterday @ 2:11 AM
Points: 9,378,
Visits: 6,473
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
vinod.andani-874416
vinod.andani-874416
Posted Thursday, September 16, 2010 4:28 AM
SSC 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
Koen Verbeeck
Koen Verbeeck
Posted Thursday, September 16, 2010 4:44 AM
SSCrazy Eights
Group: General Forum Members
Last Login: Yesterday @ 2:11 AM
Points: 9,378,
Visits: 6,473
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
Nakul Vachhrajani
Nakul Vachhrajani
Posted Thursday, September 16, 2010 5:03 AM
Ten Centuries
Group: General Forum Members
Last Login: 2 days ago @ 1:02 AM
Points: 1,277,
Visits: 1,609
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 »
22 posts, Page 1 of 3
1
2
3
»
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
