Error handling - stored procedure nested in another stored procedure

Question

I've got a stored procedure that call another stored procedure. And i sometime get an error : System.Data.SqlClient.SqlError: The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

At the advise of my teammate I plan to implement an error handling in the Try Catch that deals with this problem for both the stored procedure. Here is the Catch:

 BEGIN CATCH
 IF @@TRANCOUNT = 1 -- if this is the last tranasction - roll it back and close the transaction 
 ROLLBACK;
 IF @@TRANCOUNT > 1 -- if this is not the last tranasction - commit it and return to the previous transaction 
 COMMIT;
 THROW; -- in any case, throw the original error
 END CATCH 

i understand it deals with transaction that isn't closed if it's a nested procedure that way it can rollback and commit. and i try to understand what happens in each case:

1. Main procedure running failed before running the nested procedure -
2. Main procedure running failed after running the nested procedure that succeeded -
3. Main procedure running and there is a failure in the nested procedure -

here what happens :

1. catch on

 IF @@TRANCOUNT = 1 -- if this is the last tranasction - roll it back and close the transaction 
 ROLLBACK;

and rollback 2. what happens for this case? is it this case IF @@@@TRANCOUNT > 1 and if so what happens for the already nested transaction that was commited? 3. what happens for this case? it this case IF @@@@TRANCOUNT > 1 and all the transactions are rollbacked.

is my logic for 2 and 3 correct? and is this the right error handling for nested transactions in stored procedure?

• Chris Rolliston: Are you using SET XACT_ABORT ON? Also, what exactly is starting the transaction(s) - the .NET side, the parent procedure, the child procedure, or a combination of more than one...?
• dexon: 1. i am not using SET XACT_ABORT ON as i understand it address a timeout or error for application side and the error from the database. not sure how it would help? 2. the Transaction begin in the parent stored procedure with an explicit BEGIN TRANSACTION and also within the child stored procedure with an explicit BEGIN TRANSACION.

Answer 1

If you issue a BEGIN TRANSACTION when a transaction is already open, all that happens (in effect) is that @@TRANCOUNT is incremented. This means the next COMMIT will just decrement @@TRANCOUNT instead of actually committing anything. Only when @@TRANCOUNT falls to zero will COMMIT actually commit.

Now, this final COMMIT can be anywhere - e.g. it's formally valid to do a BEGIN TRAN in a sub-procedure but only the matching COMMIT in the parent procedure. Similarly, when you issue a ROLLBACK, it doesn't matter which procedure (child or parent) you do it in. However, in contrast to COMMIT, ROLLBACK will always roll back regardless of whether @@TRANCOUNT is currently 1 or not.

You've confirmed in the comments you aren't using SET XACT_ABORT ON. Because of this, many SQL errors will not cause execution to stop - instead SQL Server will happily trundle on to your next line of T-SQL code. Rather than trying to manage rollbacks explicitly, I'd recommend using SET XACT_ABORT ON. When you do, an error in a sub-procedure will cause an automatic rollback and have execution in both the child procedure and its parent cease. The problem of mismatched BEGIN TRAN/COMMIT calls should therefore disappear.

Generally speaking, TRY/CATCH is far less useful than (say) try/catch in C# because you've got far less control - in particular, if SQL Server is going to rollback your transaction, it's going to rollback your transaction. If there's a genuine reason for handling a T-SQL exception (e.g. to retry following a timeout error), better do it in the application layer by catching SqlException looking for particular error numbers.

• Charlieface: "Rather than trying to manage rollbacks explicitly, I'd recommend using SET XACT_ABORT ON" should be triple bolded in screaming caps. Lost count of the amount of times I've seen terrible error handling such as this. There is almost never a need for BEGIN CATCH in normal OLTP code.
• dexon: Thanks for the informative reply. will try to use the SET XACT_ABORT ON