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

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

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!