Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statem


By:   |   Read Comments (7)   |   Related Tips: More > Error Handling

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


Last Update:






About the author
MSSQLTips author Sergey Gigoyan Sergey Gigoyan is a database professional with more than 6 years of experience, with a focus on database design, development, performance tuning, optimization, high availability, BI and DW design.

View all my tips





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Friday, May 19, 2017 - 3:26:55 PM - Sergey Gigoyan Back To Top

Dear Hammad Dar,

Thanks for reading. The error message you mentioned can occur, for example when transactions are incorrectly handled in nested stored procedures. I am writing an article about this. You will be abele read it on mssqltips.com soon.

Thanks


Saturday, April 22, 2017 - 1:37:14 PM - Hammad Dar Back To Top

Nice article.

What do you advise about the other way around:

 

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

 

 


Wednesday, January 04, 2017 - 10:43:37 PM - Thana Back To Top

 Dear Sergey Gigoyan,

Kindly thank you for your cool tip i will try this

Regards,

Thana

 


Wednesday, September 14, 2016 - 8:28:25 PM - mcastro Back To Top

Thanks, It is exactly what happened to me. 


Tuesday, September 22, 2015 - 1:09:17 PM - Sergey Gigoyan Back To Top

Hi Steven,

 

Thank you for reading. As you can see, in the article we have a "Unique constraint violation error" and it's handled by CATCH block even if XACT_ABORT is ON. You can add "SELECT ERROR_SEVERITY() AS ErrorSeverity" code in the CATCH block and see that in both cases (XACT_ABORT ON and OFF) it returns the same severity - Error Severity=14. So, setting XACT_ABORT ON does not make constraint violations become fatal error. But when we remove ROLLBACK from CATCH block (it's only done for testing) and SET XACT_ABORT to ON, the transaction will be rolled back and we will receive the message "Uncommittable transaction is detected at the end of the batch. The transaction is rolled back." and @@TRANCOUNT=0 after stored procedure call. When we SET XACT_ABORT to OFF, the transaction remains open - @@TRANCOUNT=1 after stored procedure call. It means that XACT ABORT ON rolled back uncommittable transactions even if we didn't do it in the right way in the CATCH block. In other words when XACT_ABORT is ON,  transactions will be rolled back regardless of the type of error.


Monday, September 21, 2015 - 6:38:11 AM - Steven Back To Top

Hi. Useful tip but I am not sure if your tip makes the following clear: 

'When XACT_ABORT is ON, errors associated with a data modification statement cause the entire transaction to abort. Recall that constraint violations are normally non-fatal errors. With XACT_ABORT on, they become fatal to the transaction and therefore to the entire set of stored procedures, triggers, or functions involved. '  - From http://www.codemag.com/article/0305111


Tuesday, September 15, 2015 - 12:58:52 AM - Mshesh Back To Top

well explained , i had face similiar issue years ago and solved using XACT_ABORT .

 


Learn more about SQL Server tools