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 ««12

Fun with Transactions - Part III Expand / Collapse
Author
Message
Posted Tuesday, September 21, 2010 6:53 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 7:35 AM
Points: 2,819, Visits: 2,563
Good question, good forum discussion. Thanks.
Post #990208
Posted Tuesday, September 21, 2010 8:33 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, August 29, 2014 11:33 AM
Points: 554, Visits: 1,196
Great question to brush up on Transactions. One thing that I tend to forget about, that I brushed up on before answering, is the behavior of transaction_name parameter of BEGIN TRANSACTION, COMMIT TRANSACTION, and ROLLBACK TRANSACTION.
Post #990355
Posted Tuesday, September 21, 2010 9:11 AM


SSC-Insane

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

Group: General Forum Members
Last Login: Today @ 2:50 PM
Points: 21,744, Visits: 15,435
murray-906152 (9/21/2010)
In regards to SSChasing Mays concerns about committed transactions being rolled out, 'Committing inner transactions is ignored by the SQL Server Database Engine' (http://msdn.microsoft.com/en-us/library/ms189336.aspx).

Real committed transactions can't be rolled out ('A transaction cannot be rolled back after a COMMIT TRANSACTION statement is executed.' (http://msdn.microsoft.com/en-us/library/ms181299.aspx)).

Perhaps the related QotD answer explanation element ('... (including those inner transactions that have been committed)') could have been better worded.



Nice explanation and it also works well with the Myth a Day series explaining nested transactions by Paul Randal.




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 #990411
Posted Tuesday, September 21, 2010 9:13 AM


SSC-Insane

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

Group: General Forum Members
Last Login: Today @ 2:50 PM
Points: 21,744, Visits: 15,435
I think this is a nice question to continue the series. People should look up the article by Paul Randal on this topic http://www.sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-(2630)-nested-transactions-are-real.aspx



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 #990414
Posted Tuesday, September 21, 2010 9:18 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 7, 2010 8:13 AM
Points: 436, Visits: 322
Would
COMMIT TRAN OuterTran

without the use of
COMMIT TRAN InnerTran

commit both transactions?
Post #990421
Posted Tuesday, September 21, 2010 9:29 AM


SSC-Insane

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

Group: General Forum Members
Last Login: Today @ 2:50 PM
Points: 21,744, Visits: 15,435
JF1081 (9/21/2010)
Would
COMMIT TRAN OuterTran

without the use of
COMMIT TRAN InnerTran

commit both transactions?



Yes it does.




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 #990439
Posted Wednesday, September 22, 2010 8:33 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 7, 2010 8:13 AM
Points: 436, Visits: 322
CirquedeSQLeil (9/21/2010)
JF1081 (9/21/2010)
Would
COMMIT TRAN OuterTran

without the use of
COMMIT TRAN InnerTran

commit both transactions?



Yes it does.

Thanks, and I hope I did not just ruin Part IV.
Post #991234
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse