CODEWITHSUNDEEP
sql-servert-sqltransactions
Jodrell
Jodrell

Reputation: 35716

"Nested" transactions, why does this happen, how do I avoid it?

As demonstrated here,

If I have an inner stored procedure, that always rolls back without an error.

CREATE PROCEDURE [inner] AS BEGIN
    SET XACT_ABORT, NOCOUNT ON;
    
    BEGIN TRY
        BEGIN TRANSACTION;
        
        IF (1 + 1 = 2) BEGIN
            ROLLBACK TRANSACTION;
        END ELSE BEGIN
            COMMIT TRANSACTION;
        END
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 BEGIN
            ROLLBACK TRANSACTION;
        END
    END CATCH
END

and an outer SP that calls the inner,

CREATE PROCEDURE [outer] AS BEGIN
    SET XACT_ABORT, NOCOUNT ON;
    
    BEGIN TRY
        BEGIN TRANSACTION;
        
        EXEC [inner];
    
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 BEGIN
            ROLLBACK TRANSACTION;
        END
        
        ;THROW;
    END CATCH
END

and, then I call the outer SP,

EXEC [outer];

I get this error

Msg 266 Level 16 State 2 Line 0
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements.
Previous count = 1, current count = 0.

Now, initially this is not what I was expecting.

What I think is happening,

axioms:

so, step by step,

  1. [outer] -> BEGIN TRANSACTION sets @@TRANCOUNT to 1.
  2. [outer] -> calls [inner] with @@TRANCOUNT 1.
  3. [inner] -> ROLLBACK TRANSACTION resets @@TRANCOUNT to 0.
  4. [inner] -> returns to [outer] with @@TRANCOUNT 0.
  5. SQL Server detects a mismatch in @@TRANCOUNT before and after the call to [inner] and, therefore raises the error above.

So, my questions are,

Is my understanding correct and where is the canonical documentation of this?

What is the best way to write an Stored Procedure that may want to rollback its own changes, so that it can be called directly in its own batch or from within another transaction?

Upvotes: 2

Views: 1226

Answers (1)

Charlieface
Charlieface

Reputation: 71579

Yes, your understanding is correct.

The documentation is here:

In stored procedures, ROLLBACK TRANSACTION statements without a savepoint_name or transaction_name roll back all statements to the outermost BEGIN TRANSACTION. A ROLLBACK TRANSACTION statement in a stored procedure that causes @@TRANCOUNT to have a different value when the stored procedure completes than the @@TRANCOUNT value when the stored procedure was called produces an informational message. This message does not affect subsequent processing.

I'm not sure why it says "does not affect subsequent processing" as that is demonstrably false: instead it throws an error with severity 16, which can be caught with BEGIN CATCH.

If you do want to use nested transactions such as this, and be able to roll them back only partially, it becomes significantly more complicated.

You have to conditionally begin a transaction if there is none. Then you have to SAVE a savepoint. And then each rollback must conditionally roll back either the whole transaction or only the savepoint

CREATE PROCEDURE [inner] AS BEGIN
    SET XACT_ABORT, NOCOUNT ON;
    
    BEGIN TRY
        DECLARE @tranCount int = @@TRANCOUNT;
        IF @tranCount = 0
            BEGIN TRANSACTION;

        SAVE TRANSACTION innerSave;

        IF (1 + 1 = 2) BEGIN
            IF @tranCount = 0
                ROLLBACK;
            ELSE
                ROLLBACK TRANSACTION innerSave;
        END ELSE BEGIN
            COMMIT TRANSACTION;
        END
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 BEGIN
            IF @tranCount = 0
                ROLLBACK;
            ELSE
                ROLLBACK TRANSACTION innerSave;
        END
    END CATCH
END

db<>fiddle

Upvotes: 4

Related Questions