SQL Server Transaction Count with Mismatching Number BEGIN and COMMIT

Problem

There are situations where you might receive this error message “Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.”. Even when using a TRY CATCH block in the stored procedure and a ROLLBACK in the catch block, this error still occurs. One reason this is not captured is because fatal errors are not caught and the transaction remains open. In this tip we will look at how you can handle this issue.

Solution

We will look at an example to create this issue and how this can be resolved. This example is probably something you wouldn’t normally do, but this allowed me to show the issue with a real working example.

We create two different global temporary tables in different query windows:

--On Query Window 1
CREATE TABLE ##tmpStagingTest1
(
    ID INT IDENTITY(1,1),
    Spid SMALLINT,
    Value INT 
)
--On Query Window 2
CREATE TABLE ##tmpStagingTest2
(
    ID INT IDENTITY(1,1),
    Spid SMALLINT,
    Value INT UNIQUE
)

We then create the following stored procedure which works with these tables:

CREATE PROCEDURE uspInsertStageingDataTest
@pValue INT
AS
BEGIN
    SET NOCOUNT ON
    BEGIN TRY
        BEGIN TRANSACTION
        INSERT INTO ##tmpStagingTest1(Spid, Value)
        VALUES (@@SPID, @pValue)
        INSERT INTO ##tmpStagingTest2(Spid, Value)
        VALUES (@@SPID, @pValue)
        COMMIT
    END TRY
    BEGIN CATCH
        SELECT ERROR_MESSAGE()
        IF @@TRANCOUNT>0
            ROLLBACK
    END CATCH
END

Without closing “Query Window 1” and “Query Window 2” (to make sure the global temporary tables exist) we open a new “Query Window 3” and execute the procedure:

EXEC uspInsertStageingDataTest @pValue=1

There are no errors and the SP runs successfully.

We can then check the data in the tables and the transaction count as follows running the below in “Query Window 3”:

SELECT * FROM ##tmpStagingTest1
SELECT * FROM ##tmpStagingTest2
SELECT @@TRANCOUNT AS Trancount

The result is the following: one row was inserted into each table and the transaction has committed:

SET_XACT_ABORT_ON

Now let’s close “Query Window 2”, so temporary table ##tmpStagingTest2 is deleted and run the uspInsertStageingDataTest stored procedure again:

EXEC uspInsertStageingDataTest @pValue=2

We then get the following error message:

uspInsertStageingDataTest

If we execute these statements again the results are as follows:

SELECT * FROM ##tmpStagingTest1
SELECT * FROM ##tmpStagingTest2
SELECT @@TRANCOUNT AS Trancount

The first statement returns two rows.

result_of_these_queries

The second statement returns an error.

transaction_count

And the third statement returns a Trancount of 1:

SELECT_@@TRANCOUNT

As we can see, one row was inserted in the first table, then the procedure failed and the transaction has not been committed, because an error has not been caught. This is a problem, because we want to implement the logic which inserts data into both tables or insert nothing. We can manually execute the rollback command in “Query Window 3”, after which the transaction will be rolled back and only the first row will remain in the ##tmpStagingTest1 table. If we try to close “Query Window 3” without doing a ROLLBACK we will receive the following message:

Query_Window

Solving The Issue

Let’s assume that we manually rolled back the transaction and there is only one row in ##tmpStagingTest1 table. The reason why the transaction remains open is that ##tmpStagingTest2 table is missing and it causes the fatal error. The catch block can’t catch fatal errors, so the transaction remains open.

To avoid similar situations and implement our logic without problems we can set XACT_ABORT ON in our procedure. When SET XACT_ABORT is ON and T-SQL statement raises a run-time error, SQL Server automatically rolls back the current transaction. By default XACT_ABORT is OFF. Let’s use this in our stored procedure as follows:

ALTER PROCEDURE uspInsertStageingDataTest
@pValue INT
AS
BEGIN
    SET NOCOUNT ON
    SET XACT_ABORT ON
    BEGIN TRY
        BEGIN TRANSACTION
        INSERT INTO ##tmpStagingTest1(Spid, Value)
        VALUES (@@SPID, @pValue)
        INSERT INTO ##tmpStagingTest2(Spid, Value)
        VALUES (@@SPID, @pValue)
        COMMIT
    END TRY
    BEGIN CATCH
        SELECT ERROR_MESSAGE()
        IF @@TRANCOUNT>0
            ROLLBACK
    END CATCH
END

Then we run it again:

EXEC uspInsertStageingDataTest @pValue=3

We will receive the following message:

SELECT_ERROR_MESSAGE()

But in this case transaction is rolled back and the trancount is 0:

SELECT @@TRANCOUNT AS Trancount
SELECT_@@TRANCOUN

Now if we create the ##tmpStagingTest2 table again and run this command twice:

EXEC uspInsertStageingDataTest @pValue=3

The first time it completes successfully, because there is unique constraint on “Value” column in ##tmpStagingTest2 table, the second execution of the stored procedure returns the following message:

##tmpStagingTest2

This means that this error isn’t a fatal error and is handled in the catch block. We return ERROR_MESSAGE() which is selected in the catch block and the transaction is rolled back in the catch block. If we check the trancount we can see this value is 0.

SELECT @@TRANCOUNT AS Trancount
TRY_CATCH_blocks

When SET XACT_ABORT ON is included in a stored procedure without using TRY/CATCH blocks, transactions will roll back in case of errors. However, usually it’s necessary to handle errors in a specific way, for example returning the corresponding message or assign a corresponding response code to output variable when errors occur (not fatal errors). This kind of error handling can be developed in the CATCH block, so using SET XACT_ABORT ON with TRY/CATCH gives us a more flexible error handling solution.

Conclusion

Using SET XACT_ABORT ON in stored procedures can be very helpful in situations when there are transactions and the possibility of fatal errors. It helps developers avoid uncommitted transactions. Using SET XACT_ABORT ON in procedures with TRY/CATCH blocks, we should take into account the fact that all errors that are caught in the catch block are handled there. Only in cases when the CATCH block is unable to handle the error, thanks to the fact that XACT_ ABORT is ON, SQL Server rolls back the transaction.

Next Steps

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *