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 Monday, September 20, 2010 10:26 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 12:27 AM
Points: 1,405, Visits: 1,815
Comments posted to this topic are about the item Fun with Transactions - Part III

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

Follow me on
Twitter: @sqltwins
Google Plus: +Nakul
Post #989902
Posted Monday, September 20, 2010 10:27 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, June 16, 2014 9:38 AM
Points: 2,163, Visits: 2,189
Thanks for the question, it is good to review the basics of transactions!
Post #989903
Posted Monday, September 20, 2010 11:33 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, June 30, 2014 2:51 AM
Points: 1,976, Visits: 369
thanks for this question... i was wondering if you could you provide a question or a query that involves both
  ROLLBACK WORK & ROLLBACK TRAN 

Post #989946
Posted Tuesday, September 21, 2010 12:43 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:35 PM
Points: 5,930, Visits: 8,179
ziangij (9/20/2010)
thanks for this question... i was wondering if you could you provide a question or a query that involves both
  ROLLBACK WORK & ROLLBACK TRAN 


ROLLBACK WORK (or simply ROLLBACK, as the WORK keyword is optional) is completely equivalent to ROLLBACK TRAN without transaction name.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #989985
Posted Tuesday, September 21, 2010 12:51 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 12:27 AM
Points: 1,405, Visits: 1,815
ziangij (9/20/2010)
thanks for this question... i was wondering if you could you provide a question or a query that involves both
  ROLLBACK WORK & ROLLBACK TRAN 



When you simply issue a ROLLBACK, it is equivalent to issuing a ROLLBACK WORK.
ROLLBACK TRAN is a special case, as it can only work with user-defined transactions.


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

Follow me on
Twitter: @sqltwins
Google Plus: +Nakul
Post #989989
Posted Tuesday, September 21, 2010 1:54 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, July 24, 2014 4:28 AM
Points: 1,100, Visits: 4,898
Thanks for this. I wouldn't have realised that ROLLBACK would roll back a comitted transaction - doesn't this have ACID implications?
Post #990024
Posted Tuesday, September 21, 2010 2:04 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:35 PM
Points: 5,930, Visits: 8,179
jts_2003 (9/21/2010)
Thanks for this. I wouldn't have realised that ROLLBACK would roll back a comitted transaction - doesn't this have ACID implications?

It would be far worse if the inner transaction were not rolled back. The inner transaction is included in the outer transaction, so a ROLLBACK of the outer transaction should roll back everything that has been done by that transaction - including the nested transactions.

See also the forum discussions that followed the previous two questions in this series.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #990029
Posted Tuesday, September 21, 2010 4:40 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:25 AM
Points: 13,350, Visits: 10,211
Great question!



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 #990110
Posted Tuesday, September 21, 2010 4:42 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, June 19, 2014 6:02 AM
Points: 1,976, Visits: 239
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.

Post #990112
Posted Tuesday, September 21, 2010 5:59 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 14, 2014 9:47 AM
Points: 2,919, Visits: 2,525
Good question - and a great way to review the basics.

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
Post #990161
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse