By: Sergey Gigoyan | Comments (2) | Related: > TSQL
Problem
Did you ever have a problem handling SQL Server transactions in nested stored procedures? Did you ever receive an error message “Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.”? However, after reviewing your code you still had a feeling that you handled transactions correctly? In this tip we are going to explain the reason for the above error and suggest solutions on how to correctly manage transactions in case of nesting stored procedures to avoid this error.
Solution
Let’s illustrate a problem many developers face when using transactions in nested stored procedures. First, we will create a test environment:
--script 1 USE master GO CREATE DATABASE TestDB GO USE TestDB GO CREATE TABLE TestTable ( ID INT NOT NULL, Value INT NOT NULL, PRIMARY KEY (ID) ) GO
To facilitate our example we will use two procedures – an outer procedure and an inner procedure.
Stored procedure uspUpdateData updates rows in table TestTable.
--script 2
USE TestDB
GO
CREATE PROCEDURE uspUpdateData
@pID INT,
@pNewValue INT
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION
UPDATE TestTable
SET Value=@pNewValue
WHERE ID=@pID AND Value < 100
IF @@TRANCOUNT > 0
COMMIT
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK
SELECT ERROR_NUMBER() AS ErrorNumber
SELECT ERROR_MESSAGE() AS ErrorMessage
END CATCH
END
Stored procedure uspInsertData inserts data into table TestTable and also calls uspUpdateData to update the value for specific rows.
--script 3
USE TestDB
GO
CREATE PROCEDURE uspInsertData
@pID INT,
@pValue INT
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION
INSERT INTO TestTable(ID, Value)
VALUES (@pID, @pValue)
EXEC uspUpdateData @pID=@pID, @pNewValue=NULL
IF @@TRANCOUNT > 0
COMMIT
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK
SELECT ERROR_NUMBER() AS ErrorNumber
SELECT ERROR_MESSAGE() AS ErrorMessage
END CATCH
END
Now let’s call the outer stored procedure uspInsertData.
--script 4 USE TestDB GO EXEC uspInsertData @pID=1, @pValue=10 GO SELECT * FROM TestTable
As the Value column cannot be NULL in table TestTable, the transaction is rolled back and both the insert and update operations fail, but we also receive another error message related to a mismatching number of BEGIN and COMMIT statements:
As we know, the BEGIN TRANSACTION statement increases the value of @@TRANCOUNT by 1, each COMMIT decreases it by 1 and a ROLLBACK statement sets @@TRANCOUNT to 0 (except ROLLBACK TRANSACTION to savepoint).
Reviewing our code we can see that we always check the value of @@TRANCOUNT to be greater than 1, which means that we are inside a transaction and then do COMMIT or ROLLBACK. So, why are BEGIN and COMMIT statements mismatched? The key point is that we didn’t consider the fact that each stored procedure must end with the same transaction count with which it is executed, otherwise an error will occur. It is not a fatal error, however it should be avoided and there are different approaches. In our example when uspUpdateData is executed the @@TRANCOUNT=1, but when it is finished @@TRANCOUNT=0, because the ROLLBACK occurred in uspUpdateData. It means that ROLLBACK must be called in the procedure that starts the outermost transaction.
To avoid this error, we can do the following: we can open transactions and commit or roll them back only in the outermost stored procedure, but considering the fact that the inner procedure can be called separately or in other procedures, so we will need to include logic in the inner procedure into the transaction. To do this, we can check the inner procedure if there is an open transaction from the caller procedure. If yes, we do not open a new one, if no, we open a transaction in the inner procedure and commit it in case of success. In case of failure, if the transaction is opened in the outer procedure (@@TRANCOUNT>1 when inner procedure starts), we generate an error to allow the outermost procedure to call a ROLLBACK, so in this case the ROLLBACK is never called from the inner procedure. If the transaction is opened in the inner procedure (@@TRANCOUNT=1), we issue a ROLLBACK in the inner procedure. As a result our procedure ends with the same count of transactions as it starts, so we will not receive an error.
To illustrate this approach we will modify the inner procedure in the following way:
--script 5
USE TestDB
GO
ALTER PROCEDURE uspUpdateData
@pID INT,
@pNewValue INT,
@pResCode INT=0 OUTPUT --0-OK, 1-ERROR
AS
BEGIN
DECLARE @trancount BIT = 0
BEGIN TRY
IF @@TRANCOUNT=0
BEGIN
BEGIN TRANSACTION
SET @trancount=1
END
UPDATE TestTable
SET Value=@pNewValue
WHERE ID=@pID AND Value < 100
IF @trancount=1
COMMIT
END TRY
BEGIN CATCH
IF @trancount=1
ROLLBACK
SELECT ERROR_NUMBER() AS ErrorNumber
SELECT ERROR_MESSAGE() AS ErrorMessage
SET @pResCode=1
END CATCH
END
Also, we will modify the outer procedure to raise an error when the inner procedure fails, to be able to go to a CATCH block and do a ROLLBACK.
--script 6
USE TestDB
GO
ALTER PROCEDURE uspInsertData
@pID INT,
@pValue INT,
@pResCode INT=0 OUTPUT --0-OK, 1-ERROR
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION
INSERT INTO TestTable(ID, Value)
VALUES (@pID, @pValue)
EXEC uspUpdateData @pID=@pID, @pNewValue=NULL, @pResCode=@pResCode OUTPUT
IF @pResCode=1
RAISERROR('uspUpdateData failed',16,1)
IF @@TRANCOUNT > 0
COMMIT
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK
SELECT ERROR_NUMBER() AS ErrorNumber
SELECT ERROR_MESSAGE() AS ErrorMessage
SET @pResCode=1
END CATCH
END
As we can see we have added the @pResCode output parameter in both procedures to check if the inner procedure fails, to be able to decide to do a ROLLBACK or not. Now execution of the uspInsertData procedure will not generate an error, but will still have the same result – all changes are rolled back and the TestTable is empty.
We can also use another approach allowing the inner procedures to open transactions without checking if there are open transactions. In this case we must also issue a ROLLBACK only in the procedure where the outermost transaction is opened, but if @@TRANCOUNT>1 in the inner procedure we need to issue a COMMIT even in case of failure to end the procedure with the same value of @@TRANCOUNT as it was when it started. Instead, we return a failure to the outermost procedure to do the ROLLBACK.
--script 7
USE TestDB
GO
ALTER PROCEDURE uspUpdateData
@pID INT,
@pNewValue INT,
@pResCode INT=0 OUTPUT --0-OK, 1-ERROR
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION
UPDATE TestTable
SET Value=@pNewValue
WHERE ID=@pID AND Value < 100
IF @@TRANCOUNT > 0
COMMIT
END TRY
BEGIN CATCH
IF @@TRANCOUNT=1
ROLLBACK
ELSE
IF @@TRANCOUNT > 1
COMMIT
SELECT ERROR_NUMBER() AS ErrorNumber
SELECT ERROR_MESSAGE() AS ErrorMessage
SET @pResCode=1
END CATCH
END
So, in both cases we do not receive the error message “Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.” and our logic is implemented correctly.
Conclusion
All in all, each procedure must end with the same count of transactions as it starts, otherwise there will be a non fatal error “Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.”. There are different approaches of handling transactions in nested stored procedures, but in all of them a ROLLBACK must be issued in the outermost transaction.
Next Steps
Check out this related information:
- https://technet.microsoft.com/en-us/library/ms187844(v=sql.105).aspx
- https://docs.microsoft.com/en-us/sql/t-sql/functions/trancount-transact-sql
- https://docs.microsoft.com/en-us/sql/t-sql/language-elements/transactions-transact-sql
About the author
Sergey Gigoyan is a database professional with more than 10 years of experience, with a focus on database design, development, performance tuning, optimization, high availability, BI and DW design.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
