Post reply

Fun with Transactions - Part III

  • September 20, 2010 at 10:26 pm

    #226591

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

    Thanks & Regards,
    Nakul Vachhrajani.
    http://nakulvachhrajani.com

    Follow me on
    Twitter: @sqltwins

  • September 20, 2010 at 10:27 pm

    #1224492

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

  • September 20, 2010 at 11:33 pm

    #1224528

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

  • September 21, 2010 at 12:43 am

    #1224556

    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/

  • September 21, 2010 at 12:51 am

    #1224560

    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

    Follow me on
    Twitter: @sqltwins

  • September 21, 2010 at 1:54 am

    #1224592

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

  • September 21, 2010 at 2:04 am

    #1224597

    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/

  • September 21, 2010 at 4:40 am

    #1224663

    Great question!

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

  • September 21, 2010 at 4:42 am

    #1224665

    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.

  • September 21, 2010 at 5:59 am

    #1224710

    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

  • September 21, 2010 at 6:53 am

    #1224753

    Good question, good forum discussion. Thanks.

  • September 21, 2010 at 8:33 am

    #1224879

    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.

  • September 21, 2010 at 9:11 am

    #1224931

    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

  • September 21, 2010 at 9:13 am

    #1224934

    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

  • September 21, 2010 at 9:18 am

    #1224941

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

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

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