SQL Server transactions in nested stored procedures using Savepoints

By: Sergey Gigoyan | Updated: 2018-06-27 | 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:

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:

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

Article Last Updated: 2018-06-27

Handling SQL Server transactions in nested stored procedures using Savepoints

Problem

Solution

Conclusion

Next Steps

About the author

Comments For This Article