Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements

Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.

Recently, one of my colleague was very upset with this error as he was not using any @@TRANCOUNT but this error was coming. As this error message suggest a wrong transaction count after execute he was poking his nose into finding the line where he is counting transaction. There were no transaction count in his stored procedure.

 

After a lot troubleshooting, he find the simple mistake that was the actual culprit. In fact he has used XACT_STATE() and not @@Trancount. His procedure inside code was as (just for sample)-

BEGIN TRY
    BEGIN TRANSACTION;
        DELETE FROM Production.Product
        WHERE ProductID = @ProductID;
    COMMIT TRANSACTION;
END TRY

BEGIN CATCH
    IF (XACT_STATE()) = -1
    BEGIN
        ROLLBACK TRANSACTION;
    END;

    IF (XACT_STATE()) = 1
    BEGIN
        COMMIT TRANSACTION;
    END;
END CATCH;
GO

And in above code you can see there is no transaction count. hmmm....

The error was due to XACT_STATE() usage without setting XACT_ABORT to ON. 
This error just disappeared after putting this line of code on the top.
SET XACT_ABORT ON;
BEGIN TRY
    BEGIN TRANSACTION;
        DELETE FROM Production.Product
        WHERE ProductID = @ProductID;
    COMMIT TRANSACTION;
END TRY

BEGIN CATCH
    IF (XACT_STATE()) = -1
    BEGIN
        ROLLBACK TRANSACTION;
    END;

    IF (XACT_STATE()) = 1
    BEGIN
        COMMIT TRANSACTION;
    END;
END CATCH;
GO
In short: Always set XACT_ABORT to ON on the top if you are using XACT_STATE()