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?

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.