ROLLBACK to a SAVEPOINT

  • sergey.gigoyan

    Ten Centuries

    Points: 1175

    Comments posted to this topic are about the item ROLLBACK to a SAVEPOINT

  • Stewart "Arturius" Campbell

    SSC Guru

    Points: 71483

    Interesting question, thanks Sergey

    back-to-basics question on a subject that i had forgotten all about (never use save points if i can help it)

    ____________________________________________
    Space, the final frontier? not any more...
    All limits henceforth are self-imposed.
    “libera tute vulgaris ex”

  • Sean Lange

    SSC Guru

    Points: 286481

    Interesting question. I have never had a need for save points. It seems like if save points are needed perhaps a little more time in the design phase is appropriate. The part of this that is scary is that it seems to perpetuate the myth that nested transactions are real.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • webrunner

    One Orange Chip

    Points: 29982

    Thanks, this question taught me something today!

    - webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and says Can I join you?
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • Carlo Romagnano

    SSC-Insane

    Points: 21830

    Sean Lange - Tuesday, October 30, 2018 7:18 AM

    The part of this that is scary is that it seems to perpetuate the myth that nested transactions are real.

    Great!
    In this script the main bug is to open a nested transaction and rollback to the saved point. I think you can choose or no nested transaction and rollback to the saved point or start a named nested transaction and rollback to it instead of the saved point.

  • Ken Hiatt

    SSC-Addicted

    Points: 435

    I’m confused... what are the 2 transactions that are being counted? And is the end of the initial transaction implied?

  • Shayn Thomas

    SSCertifiable

    Points: 5429

    nice question
    ta

    ---------------------------------------------------------------------------------------
    The more you know, the more you know that you dont know

  • Carlo Romagnano

    SSC-Insane

    Points: 21830

    Ken Hiatt - Wednesday, October 31, 2018 5:05 AM

    I’m confused... what are the 2 transactions that are being counted? And is the end of the initial transaction implied?

    There're two begin transaction, so that's right. The rollback to saved_point is not the same as rollback of a transaction, you can see it as "restart from the saved_point".

  • Solomon Rutzky

    SSCoach

    Points: 16173

    Carlo Romagnano - Wednesday, October 31, 2018 7:12 AM

    Ken Hiatt - Wednesday, October 31, 2018 5:05 AM

    I’m confused... what are the 2 transactions that are being counted? And is the end of the initial transaction implied?

    There're two begin transaction, so that's right. The rollback to saved_point is not the same as rollback of a transaction, you can see it as "restart from the saved_point".

    Agreed. And to answer the second question: yes, the end of the script (including both BEGIN TRAN statements) is implied.

    Also, for more details on transactions and save points, I did some research several years ago and posted my results in the following DBA.StackExchange answer:

    How to rollback when 3 stored procedures are started from one stored procedure

    Take care, Solomon..

    SQL#https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
    Sql Quantum Lifthttps://SqlQuantumLift.com/ ( company )
    Sql Quantum Leaphttps://SqlQuantumLeap.com/ ( blog )
    Info sitesCollations     •     Module Signing     •     SQLCLR

  • Sue_H

    SSC Guru

    Points: 90287

    Solomon Rutzky - Tuesday, November 6, 2018 11:58 AM

    Also, for more details on transactions and save points, I did some research several years ago and posted my results in the following DBA.StackExchange answer:

    How to rollback when 3 stored procedures are started from one stored procedure

    Take care, Solomon..

    Wow....That's an awesome post on transactions!! Thanks for writing that and posting the link -

    Sue

  • Solomon Rutzky

    SSCoach

    Points: 16173

    Sue_H - Tuesday, November 6, 2018 4:55 PM

    Solomon Rutzky - Tuesday, November 6, 2018 11:58 AM

    Also, for more details on transactions and save points, I did some research several years ago and posted my results in the following DBA.StackExchange answer:

    How to rollback when 3 stored procedures are started from one stored procedure

    Take care, Solomon..

    Wow....That's an awesome post on transactions!! Thanks for writing that and posting the link -

    Sue

    Thanks, and you are quite welcome :). And that reminds me:

    I was going to mention to Sergey that the question, in its current form, is actually a little ambiguous since "0" is a possible answer, under a certain condition. If the session level setting of XACT_ABORT is ON, then @@TRANCOUNT will immediately go to 0 (including a rollback of the transaction itself). This question assumes that XACT_ABORT is OFF (which is typically the case), but it would be best to explicitly state that in the question, just to remove any possible confusion. You can see the effect below. It is the same code as in the question, but I set XACT_ABORT ON at the top, which then causes an error in the CATCH block since the only thing you can do in a CATCH block when XACT_ABORT is ON and an error happens is execute ROLLBACK. I also added the "GO" so that the error doesn't prevent the SELECT @@TRANCOUNT from showing that it is indeed 0. And of course the conditional ROLLBACK at the bottom to make testing easier when you change XACT_ABORT to OFF which then leaves the @@TRANCOUNT at 2.

    SET XACT_ABORT ON;

    BEGIN TRANSACTION

        IF OBJECT_ID('tempdb..#tmpTable') IS NOT NULL
            DROP TABLE #tmpTable

        CREATE TABLE #tmpTable(ID INT NOT NULL)

        SAVE TRANSACTION T1

            BEGIN TRY
                BEGIN TRANSACTION

                    INSERT INTO #tmpTable(ID)
                    VALUES (NULL)

                COMMIT
            END TRY
            BEGIN CATCH
                ROLLBACK TRANSACTION T1
            END CATCH
    GO -- added to allow SELECT to work when XACT_ABORT is ON and causes error in CATCH block
            SELECT @@TRANCOUNT AS 'TRANCOUNT'

    IF (@@TRANCOUNT > 0)
    BEGIN
        PRINT 'Rolling back transaction...';
        ROLLBACK;
    END;

    Take care, Solomon...

    SQL#https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
    Sql Quantum Lifthttps://SqlQuantumLift.com/ ( company )
    Sql Quantum Leaphttps://SqlQuantumLeap.com/ ( blog )
    Info sitesCollations     •     Module Signing     •     SQLCLR

Viewing 11 posts - 1 through 11 (of 11 total)

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