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?
- First and second transaction will be created
- BOTH First and second transaction will be rolled back
- 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.
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.
There are two ways I find most likely to be useful:
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.
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.
- 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.
- 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!