Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 2

Question

Error:
System.Data.Entity.Core.EntityCommandExecutionException: An error occurred while executing the command definition. See the inner exception for details. ---> System.Data.SqlClient.SqlException:
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 2.
Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.

Hi Folks,

I am trying to re-produce this error to see the possibility for a code change in one of my Sproc which basically Insert with Try catch
The sproc is long so just pasting the summary of code

--======================================
Alter procedure Dbo.AddEvent
-- Some params

As

SET XACT_ABORT ON

Begin Try

-- Some variable setup

   Insert Dbo.Event
   Values(variables set 1)

   Insert Dbo.Event
   Values(variables set 2)

  Update Dbo.Event
  Set columns with variables 

  COMMIT TRANSACTION

  RETURN 0

End Try

Begin Catch

    DECLARE @errNumber INT

    SELECT
        @errNumber = ERROR_NUMBER()

    IF @@tranCount = 1                        
        ROLLBACK TRANSACTION

    SET @Eventid = -1

    RETURN @errNumber

End Catch
SET XACT_ABORT OFF

Now, I ran SQL trace nearly for a week but didnt get a single exception. Seems like it happens very rare
I am trying to re-produce the error and make required change to the code

Any help must be appreciated

Answer 1

Now I see the bug! I did not look carefully enough last night. This is plain wrong:

 IF @@tranCount = 1                        
     ROLLBACK TRANSACTION

That should be

 IF @@tranCount > 0                        
     ROLLBACK TRANSACTION

Look at the error message you got:

Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 2.

At the time of the error, the procedure was called inside a transaction, so @@trancount was 1. The procedure itself started a nested transaction which bumped @@trancount to 2. But the CATCH block did not roll back the transaction in this case. You should be able to reproduce this situation this way:

BEGIN TRANSACTION

EXEC dbo.AddEvent <with a parameter that leads to an error>
go
SELECT  @@trancount
IF @@trancount > 0 ROLLBACK TRANSACTION

A second flaw is that the procedure does not re-raise the error. Yes, I see that you return an error code, and I guess your client code looks at the return value. But if you nest stored procedures, looking at return codes is tedious. It's better to re-raise the error, so that outer CATCH handlers - be that in calling procedures or client code are activated.

I have a series of articles about error and transaction handling on my web site. Part One gives you a head start by simply describing good ways to implement TRY-CATCH. Part Two and Part Three are much longer and discusses a lot of nasty details with error handling in SQL Server. Start here: https://www.sommarskog.se/error_handling/Part1.html.

Answer 2

Hi,

You don't want to re-set SET XACT_ABORT OFF at the end and this statement

IF @@tranCount > 0 -- slight difference here
ROLLBACK TRANSACTION

should be the first one in the CATCH block.

But yes, we also sometimes get that same issue (one procedure calls another one and yet another one each having try/catch and not re-throwing the error back...

Answer 3

Thanks for your reply @Anonymous

To make the change in the code to avoid the problem is second thing

First is, somehow I need to produce the error with above procedure to show business that we need change in the code. -- They just want to see why the error comes at first place even with the XACT_Abort().

Answer 4

Try opening a transaction (not committing yet) and modify any of the tables used in that procedure and call that procedure at the same time. See if you would be able to get that error.

Answer 5

It's quite impossible to review code you only see bits and piece of. One possibility is that someone was fiddling around when this SP was running and a table was missing. This error is not caught by TRY CATCH, and leaves the transaction open. But only if you have XACT_ABORT OFF, and you have been a good boy and issue SET XACT_ABORT ON on top.

Then again, are you sure that this is the code that actually ran when you got the error?

Try to figure out the root cause for an error you cannot reproduce is very difficult.