Fun with Transactions - Part III

  • Comments posted to this topic are about the item Fun with Transactions - Part III

    Thanks & Regards,
    Nakul Vachhrajani.
    http://nakulvachhrajani.com
    Be courteous. Drive responsibly.

    Follow me on
    Twitter: @sqltwins

  • Thanks for the question, it is good to review the basics of transactions!

  • thanks for this question... i was wondering if you could you provide a question or a query that involves both ROLLBACK WORK & ROLLBACK TRAN 🙂

  • 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/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • 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://nakulvachhrajani.com
    Be courteous. Drive responsibly.

    Follow me on
    Twitter: @sqltwins

  • Thanks for this. I wouldn't have realised that ROLLBACK would roll back a comitted transaction - doesn't this have ACID implications?

  • 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/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Great question!

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • 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.

  • 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

  • Good question, good forum discussion. Thanks.

  • 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.

  • 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, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • 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, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Would COMMIT TRAN OuterTran without the use of COMMIT TRAN InnerTran commit both transactions?

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

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