Learn more about SQL Server tools

mssqltips logo
 

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

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Handling Transactions in Nested SQL Server Stored Procedures


By:   |   Last Updated: 2017-06-08   |   Comments   |   Related Tips: More > T-SQL

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 [email protected]
         WHERE [email protected] 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 @[email protected], @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:

transaction count after execute indicates a mismatching 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 [email protected]
         WHERE [email protected] 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 @[email protected], @pNewValue=NULL, @[email protected] 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.

SQL Server stored procedure failed

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 [email protected]
         WHERE [email protected] 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:



Last Updated: 2017-06-08


get scripts

next tip button



About the author
MSSQLTips author Sergey Gigoyan 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.

View all my tips




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    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools