Be careful using ROLLBACK on nested transaction in SQL Server!
Tags: sql
Published: 2021-05-10 13:52:09

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:

```sql
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:

```sql
--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:

    ```sql
    --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:

    ```sql
    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](https://docs.microsoft.com/en-us/sql/t-sql/statements/set-xact-abort-transact-sql?view=sql-server-ver15) and [XACT_STATE](https://docs.microsoft.com/en-us/sql/t-sql/functions/xact-state-transact-sql?view=sql-server-ver15) - 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!