SAVE TRANSACTION vs BEGIN TRANSACTION (SQL Server) how to nest transactions nicely

Question

I have a stored procedure that needs to set a save point so that it can, under certain circumstances, undo everything it did and return an error code to the caller, or accept/commit it and return success to the caller. But I need it to work whether the caller has already started a transaction or not. The doc is extremely confusing on this subject. Here is what I think will work, but I'm not certain of all the ramifications.

The thing is - this Stored Procedure (SP) is called by others. So I don't know if they've started a transaction or not... Even if I require users to start a transaction to use my SP, I still have questions about the proper use of Save Points ...

My SP will test if a transaction is in progress, and if not, start one with BEGIN TRANSACTION. If a transaction is already in progress, it will instead create a save point with SAVE TRANSACTION MySavePointName, and save the fact this is what I did.

Then if I have to roll back my changes, if I did a BEGIN TRANSACTION earlier, then I will ROLLBACK TRANSACTION. If I did the save point, then I will ROLLBACK TRANSACTION MySavePointName. This scenario seems to work great.

Here is where I get a little confused - if I want to keep the work I've done, if I started a transaction I will execute COMMIT TRANSACTION. But if I created a save point? I tried COMMIT TRANSACTION MySavePointName, but then the caller tries to commit its transaction and gets an error:

The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

So I'm wondering then - a save point can be rolled back (that works: ROLLBACK TRANSACTION MySavePointName will NOT roll back the caller's transaction). But perhaps one never needs to "commit" it? It just stays there, in case you need to roll back to it, but goes away once the original transaction is committed (or rolled back)?

If there is a "better" way to "nest" a transaction, please shed some light as well. I haven't figured out how to nest with BEGIN TRANSACTION but only rollback or commit my internal transaction. Seems ROLLBACK will always roll back to the top transaction, while COMMIT simply decrements @@trancount.

Oligoclase

Your find might be worth posting as an answer. – Polyhydric

@Andriy Ok, that's what I did - removed my edit and used it as an answer instead. Thanks. – Oligoclase

Answer 1

I believe I've figured this all out now, so I will answer my own question...

I've even blogged my findings if you want more details at http://geekswithblogs.net/bbiales/archive/2012/03/15/how-to-nest-transactions-nicely---quotbegin-transactionquot-vs-quotsave.aspx

So my SP starts with something like this, to start a new transaction if there is none, but use a Save Point if one is already in progress:

DECLARE @startingTranCount int
SET @startingTranCount = @@TRANCOUNT

IF @startingTranCount > 0
    SAVE TRANSACTION mySavePointName
ELSE
    BEGIN TRANSACTION
-- …

Then, when ready to commit the changes, you only need to commit if we started the transaction ourselves:

IF @startingTranCount = 0
    COMMIT TRANSACTION

And finally, to roll back just your changes so far:

-- Roll back changes...
IF @startingTranCount > 0
    ROLLBACK TRANSACTION MySavePointName
ELSE
    ROLLBACK TRANSACTION

Oligoclase

any reason to not starting a new nested transaction after save transaction? – Especial

Below is a link to an article about nesting transactions. If a nested transaction is committed, it lowers the nested transaction count but does NOT commit anything. So if the outer transaction is committed, everything is, if it is rolled back, everything is rolled back, including your "committed" one. If the nested transaction does a rollback, it rolls back BOTH itself AND the outer transaction(s). If you ONLY want to rollback your own transaction without affecting any transactions you are already in, use the technique described here. – Oligoclase

Here is that article about nested transactions: technet.microsoft.com/en-us/library/… – Oligoclase

How to do a partial commit ? commit few operations in the procedure (for example import raw data) and also raise error to entity framework when processing these data. But i have problem that Entity framework allways rollback everything (including imported raw data). – Cinerary

Answer 2

Extending Brian B's answer.

This ensures the save point name is unique and uses the new TRY/CATCH/THROW features of SQL Server 2012.

DECLARE @mark CHAR(32) = replace(newid(), '-', '');
DECLARE @trans INT = @@TRANCOUNT;

IF @trans = 0
    BEGIN TRANSACTION @mark;
ELSE
    SAVE TRANSACTION @mark;

BEGIN TRY
    -- do work here

    IF @trans = 0
        COMMIT TRANSACTION @mark;
END TRY
BEGIN CATCH
    IF xact_state() = 1 OR (@trans = 0 AND xact_state() <> 0) ROLLBACK TRANSACTION @mark;
    THROW;
END CATCH

Joyejoyful

I hadn't been back to this post in a while, but love the template that you can just reuse verbatim in the next query. Nice enhancement. – Oligoclase

I've seen on MSDN suggestions to put begin transaction statement inside try block. Would doing that in this pattern cause some issues or it's totally safe to do it? – Derbyshire

I am confused about the if statement in the catch block. Why rolling back the transaction if xact state = -1. Shoulden't it be <>-1? – Lunkhead

I had a problem that has now been fixed. in above template my code was COMMIT TRANSACTION mark regardless of trans variable. It worked most of the time but not always. Just wondering based on your template should we commit a transaction if it is not nested and not commit it if it is nested?? – Byzantium

Answer 3

I have used this type of transaction manager in my Stored Procedures :

    CREATE PROCEDURE Ardi_Sample_Test  
        @InputCandidateID INT  
    AS  
        DECLARE @TranCounter INT;  
        SET @TranCounter = @@TRANCOUNT;  
        IF @TranCounter > 0  
            SAVE TRANSACTION ProcedureSave;  
        ELSE  
            BEGIN TRANSACTION;  
        BEGIN TRY  

            /*
            <Your Code>
            */

            IF @TranCounter = 0  
                COMMIT TRANSACTION;  
        END TRY  
        BEGIN CATCH  
            IF @TranCounter = 0  
                ROLLBACK TRANSACTION;  
            ELSE  
                IF XACT_STATE() <> -1  
                    ROLLBACK TRANSACTION ProcedureSave;  

            DECLARE @ErrorMessage NVARCHAR(4000);  
            DECLARE @ErrorSeverity INT;  
            DECLARE @ErrorState INT;  
            SELECT @ErrorMessage = ERROR_MESSAGE();  
            SELECT @ErrorSeverity = ERROR_SEVERITY();  
            SELECT @ErrorState = ERROR_STATE();  

            RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);  
        END CATCH  
    GO  

Gaylordgaylussac

Microsoft agrees this is the recommended approach. Reference – Gramercy