SQL Try/Catch logic with nested transactions

Question

The following sproc is implemented in accord with the template in this article: Exception handling and nested transactions. This sproc is supposed to handle deadlocks and it is called by another sproc that already creates a transaction. Some magic with BEGIN/COMMIT of the inner transaction is not matching, because I get this exception: Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0. As far as I understand, catch is executed, @xstate = -1 is true and the whole outer transaction is rolled back.

Any ideas where the mismatch happens?

CREATE PROCEDURE [dbo].[mysproc]
AS
BEGIN
    SET NOCOUNT ON;
    SET DEADLOCK_PRIORITY LOW;
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;  

    BEGIN TRY        
        DECLARE @trancount int;
        SET @trancount = @@TRANCOUNT;        
        IF (@trancount = 0)
            BEGIN TRANSACTION;
        ELSE
            SAVE TRANSACTION InnerTran;   
        --              
        -- do some work that can potentially cause a deadlock
        --
   END TRY
   BEGIN CATCH
        DECLARE @xstate int
        SELECT @xstate = XACT_STATE()

        IF (@xstate = - 1)
            ROLLBACK;
        IF (@xstate = 1 and @trancount = 0)
            ROLLBACK;
        IF (@xstate = 1 and @trancount > 0)
            ROLLBACK TRANSACTION InnerTran;
   END CATCH  
END
GO

you have different rollback testing logic compared to the article you linked to. Why? — Mitch Wheat, Dec 10 '11 at 02:04
i.e. Remus's code template explicitly checks the TRANCOUNT.... — Mitch Wheat, Dec 10 '11 at 02:21
Remus' code rolls back a transaction that the callee creates, but I want only the nested transaction to be rolled back. — kateroh, Dec 10 '11 at 02:44

score 5 · Accepted Answer · answered Dec 14 '11 at 20:53

The difference is that you do not raise an exception. In case XACT_STATE() is -1 in the catch block (ie. uncommittable transaction, like a deadlock would cause) In such a case your procedure would rollback (it must, it has no choice in -1 case) but return w/o raising an exception. Hence, the mismatch. You must raise an exception and catch it in the caller.

See Uncommittable Transactions and XACT_STATE:

If an error generated in a TRY block causes the state of the current transaction to be invalidated, the transaction is classified as an uncommittable transaction. An error that ordinarily ends a transaction outside a TRY block causes a transaction to enter an uncommittable state when the error occurs inside a TRY block. An uncommittable transaction can only perform read operations or a ROLLBACK TRANSACTION. The transaction cannot execute any Transact-SQL statements that would generate a write operation or a COMMIT TRANSACTION. The XACT_STATE function returns a value of -1 if a transaction has been classified as an uncommittable transaction.

Deadlocks will always result in uncomittable transactions. In fact, in the case of deadlock, by the time you catch the deadlock exception, the transaction has already rolled back as the deadlock victim.

SQL Try/Catch logic with nested transactions

1 Answers1