By: Sergey Gigoyan | Comments | Related: > Transactions
Problem
It is essential to manage a transaction inside nested SQL Server stored procedures to avoid the error “Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.”
We have already discussed this theme in the tip “Handling Transactions in Nested SQL Server Stored Procedures” where two solutions are provided to this problem. Also, in the tip “Understanding SQL Server Transaction Savepoints”, we explained savepoints and their features. Therefore, in this tip, we are going to use savepoints to provide a third solution to the problem of mismatching the number of Transaction Control Language (TCL) commands in nested stored procedures.
Solution
Before starting to read the third solution in this article, it is strongly recommended that one read these two tips:
- Handling Transactions in Nested SQL Server Stored Procedures
- Understanding SQL Server Transaction Savepoints
In the tip “Handling Transactions in Nested SQL Server Stored Procedures", we described the cause of the problem of mismatching BEGIN and COMMIT statements. The idea is that the stored procedure must end with the same number of open transactions as it is entered. Hence, if there is an open transaction in the outer procedure and a ROLLBACK command is executed in the inner procedure, it causes the value of the @@TRANCOUNT variable to be set to 0. However, the value of the @@TRANCOUNT was greater than 0, when the inner procedure called. This means that open transaction counts are mismatching in the beginning and in the end of the inner procedure, thus creating this error. Now let’s setup the test environment and outer and inner procedures and briefly illustrate the error, as it has already been illustrated in the “Handling Transactions in Nested SQL Server Stored Procedures” article.
The code below creates the TestDB database, inner and outer procedures:
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
USE TestDB
GO
--Inner procedure
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
GO
USE TestDB
GO
--Outer procedure
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
GO
In case of calling the outer procedure, in addition to the error related to the insertion of NULL, we also receive an error message about mismatching BEGIN and COMMIT commands, because the transaction is rolled back in inner procedure:
USE TestDB GO EXEC uspInsertData @pID=1, @pValue=10 GO SELECT * FROM TestTable
In the previous tip related to this problem, we solved this issue by checking in the inner procedure. If there is an opened transaction outside of the current procedure and only in the case of no opened transactions from the outside procedure, we open the transaction in the inner procedure. Otherwise, we do not open and COMMIT or ROLLBACK the transaction inside the inner procedure. Also, we have suggested another approach that opened the transaction in the inner procedure without checking the existence of transactions from the outside procedure and we issue the ROLLBACK statement only in the procedure where the first transaction is opened. In this solution, if we have opened the transaction from outside, we issue the COMMIT command in the inner procedure, even in case of failure to have the same number of open transactions as before starting the inner procedure.
In this tip we will tackle the same problem using savepoints. From the article “Transaction savepoints”, we know that rolling back to the savepoint does not change the value of the @@TRANCOUNT variable. Bearing this fact in mind, we can use savepoints in the inner procedures to roll back nested transaction to that point. In other words, we can roll back only internal changes without changing the value of the @@TRANCOUNT. So, the following code changes the inner procedure according to this logic:
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
ELSE
SAVE TRANSACTION uspUpdateDataCHANGES
UPDATE TestTable
SET Value=@pNewValue
WHERE ID=@pID AND Value < 100
IF @trancount=1
COMMIT
END TRY
BEGIN CATCH
IF @trancount=1
ROLLBACK
ELSE
ROLLBACK TRANSACTION uspUpdateDataCHANGES
SELECT ERROR_NUMBER() AS ErrorNumber
SELECT ERROR_MESSAGE() AS ErrorMessage
SET @pResCode=1
END CATCH
END
What have we changed? First of all, we are checking the existence of outer transactions and if there are no opened transactions from outside, we open the transaction in the inner procedure. In case of an existing outer transaction, we just set the savepoint before doing any changes in the inner procedure. Secondly, in case of success, if there is an internal transaction opened, we commit it in the inner procedure. If there is no opened transaction in the internal procedure, logically we do not need to issue a COMMIT command there. Finally, in case of failure, if we are starting the transaction only in the internal procedure, we definitely will roll it back in the inner procedure. If there was an opened transaction before the inner procedure was started, we are only rolling back the transaction to the savepoint, which is set before internal changes. Thus, using this approach, in case of the existing outer transaction, we can only roll back the internal procedure changes without touching the value of the @@TRANCOUNT variable, so that the result of the inner procedure will end with the same number of opened transactions as it is started. Now, we will change the outer procedure to create an error in case of an internal procedure failure:
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
We especially pass NULL value to the @pNewValue variable to make the inner procedure fail, which will allow us to illustrate the solution. Now, when we are calling the outer procedure, we do not have the TCL commands mismatching problem anymore:
USE TestDB GO EXEC uspInsertData @pID=1, @pValue=10 GO SELECT * FROM TestTable
We will only receive an error message about the impossibility of inserting the NULL value into the “Value” column:
If we change @pNewValue=NULL to @pNewValue=NULL in the call of internal procedure in the outer procedure, we will see that changes will be committed successfully:
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=50, @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
After the execution of the outer procedure, the data will be filled into the table:
USE TestDB GO EXEC uspInsertData @pID=1, @pValue=10 GO SELECT * FROM TestTable
Conclusion
In conclusion, savepoints can be useful in coping with the problem of transactions in nested stored procedures. Rolling back the transaction to the savepoint in the inner procedure will roll back only local changes without changing the value of the @@TRANCOUT. Therefore, we will avoid the mismatching BEGIN and COMMIT statements error.
Next Steps
The following information will be useful to this tip:
- Handling Transactions in Nested SQL Server Stored Procedures
- SQL Server transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements
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
