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:
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:
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.
The second statement returns an error.
And the third statement returns a Trancount of 1:
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:
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:
.jpg)
But in this case transaction is rolled back and the trancount is 0:
SELECT @@TRANCOUNT AS Trancount
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:
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
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
- Try to test XACT_ABORT ON in stored procedures, manually generating fatal errors, to test and better understand how it works.
- Check out these additional resources:
