DEV Community

Jakub Rumpel
Jakub Rumpel

Posted on

Be careful using ROLLBACK on nested transaction in SQL Server!

This is something I've learned today, and was pretty irritated to find out.

Up to this day I was sure that ROLLBACK TRANSACTION in nested transactions will only roll back highest, most recent transaction. To my surprise, something else happens. Consider following example:

BEGIN TRAN
    BEGIN TRAN
    ROLLBACK TRAN
COMMIT TRAN
Enter fullscreen mode Exit fullscreen mode

Do you know what will happen?

  1. First and second transaction will be created
  2. BOTH First and second transaction will be rolled back
  3. A "The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION." error will be thrown.

This is a warning: Be careful when rolling back nested transactions. It doesn't matter if transactions are named or how many are there - ROLLBACK will roll back all of them.

When do you use nested transactions?

The easiest example I can think of is when you call a procedure from a different procedure, and you have transactions in both those procedures:

--this is a very simplified example
CREATE PROCEDURE Proc1 AS
BEGIN
    BEGIN TRAN
        EXEC Proc2
    COMMIT TRAN
END

CREATE PROCEDURE Proc2 AS
BEGIN
    BEGIN TRAN
    ROLLBACK TRAN
END
Enter fullscreen mode Exit fullscreen mode

As you can see, there are two nested transactions here, and both will be rolled back because of ROLLBACK TRAN statement in Proc2, leading to an error being thrown when we get to COMMIT TRAN statement.

How can you avoid the error message?

There are two ways I find most likely to be useful:

  1. Check @@trancount before committing transaction

    This is simple enough: When you get to committing all the changes you've made in the transaction, check if there are still transactions left to be committed. Check the following code:

    --this is a very simplified example
    CREATE PROCEDURE Proc1 AS
    BEGIN
        BEGIN TRAN
            EXEC Proc2
        IF(@@trancount > 0)
        BEGIN
            COMMIT TRAN
        END
    END
    
    CREATE PROCEDURE Proc2 AS
    BEGIN
        BEGIN TRAN
        ROLLBACK TRAN
    END
    

    With this newly added IF in Proc1, the COMMIT statement won't be called, because there will be no transactions left to commit detected by our condition.

  2. Use only one rollback in CATCH statement

    Sometimes you can avoid such problem by removing transaction from nested procedure, and instead using RAISERRROR and BEGIN TRY/BEGIN CATCH statements. Check the following code:

    CREATE PROCEDURE Proc1 AS
    BEGIN
        BEGIN TRY
            BEGIN TRAN
                EXEC Proc2
            COMMIT TRAN
        END TRY
        BEGIN CATCH
            ROLLBACK TRAN
        END CATCH
    END
    
    CREATE PROCEDURE Proc2 AS
    BEGIN
        RAISERROR('Sample error', 16, 1) --numbers are examples for severity and state
    END
    

    In this example, rollback will only happen when there will be and error in Proc2 - leading to a situation when we have one master transaction, which can be rolled by only by throwing an error. Of course, we can also roll it back earlier and handle such a case however we see fit. This is just an example of how you can avoid problems with rolling back a nested transaction.

Sidenotes:

  • I'm aware that in new applications THROW is preferred to RAISERROR, but sadly I haven't used it yet, so I decided to use old statement for now
  • This post completely ignores the existence of XACT_ABORT and XACT_STATE - you can learn more about those under provided links. I encourage you to do so: They can make managing your transactions much easier.

Summary

  • Be careful when rolling back nested transactions
  • ROLLBACK statement always rolls back every transaction you have in your current session.
  • You can deal with lack of transactions by checking for @@trancount or removing nested transactions and using RAISERROR or THROW

Transactions in SQL Server are still a difficult subject for me, so if I made any mistake, please, let me know!

Top comments (0)