Rollback done - yet this error : Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements

0
votes

I am getting this error in stored procedure.

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

I read through some of the answers and found that if I return without a commit, I get the error. My stored procedure is something like this:

BEGIN TRY
BEGIN
    if @id is null
        BEGIN
        set @id= (SELECT last_sequence_value FROM table_name WHERE sequence_name = 'id') + 1
            BEGIN               
                BEGIN TRANSACTION 

                    -- update SQL statement here 

                    IF @@ROWCOUNT = 0
                    BEGIN                       
                        ROLLBACK TRANSACTION
                        RAISERROR('There was an error getting unique id in the table.',10,1)                    
                        RETURN
                    END
                    IF (@@ERROR <> 0)
                    BEGIN
                        ROLLBACK TRANSACTION
                        RAISERROR('There was an error updating record to the table',10,1)
                        RETURN
                    END
                COMMIT TRANSACTION
            END
        END 
    else 
        BEGIN
            -- some sql select statements
        END
  END
END TRY
BEGIN CATCH
   -- Raise an error with the details of the exception
      RAISERROR(@ErrMsg, @ErrSeverity, 1) WITH SETERROR
END CATCH

From the above code, I am doing a rollback and returning from the stored procedure. Yet when I run this in the perf testing environment, I get the error mentioned earlier.

Kindly help regarding this.

4
sqlsql-servertsqlstored-procedures
wht you want with above ? - Yogesh Sharma
Yes - you shouldn't use rollback transaction when nesting transactions - this rolls the outermost transaction back hence the error about @@trancount leaving as 0. You might need to use Save Points here and here - StuartLC
@StuartLC - yeah you are right but since I have rolled back the transaction and mentioned return statement, there is no way it should go into commit transaction. So I am still not sure why that error comes up. If I did a return without rollback then its very risky since it keeps the transaction open. - GuruC
Not quite - if your proc is called from another proc (or any sql code / ado etc) which has itself already started a transaction, your PROC is NOT ALLOWED to ROLLBACK TRANSACTION; since this attempts to undo the caller's transaction. Instead, you must detect whether there is an outer transaction or not, and if there is one, to start a savepoint and rollback to your savepoint instead. It gets messy very quickly. The other option is not to start an inner transaction if there's already an outer transaction, and then just THROW if the proc fails, leaving it to the outer transaction to rollback. - StuartLC

4 Answers

3
votes

I have fixed it by adding 

IF @@TRANCOUNT > 0
   ROLLBACK TRANSACTION

in the catch block before I raise an error.

Thanks everyone for the wonderful inputs.

0
votes

I had a user report this same error in code I inherited, but it was caused by a combination of using a transaction and try/catch inside of the sproc along with calling the sproc inside of a VB.Net System.Transactions.TransactionScope block as well. If an error cropped up inside the sproc, the sproc rolled back everything there correctly, but it apparently rolled back the VB.Net transaction as well and created the mismatch. My fix was to remove the transaction code and the the try/catch blocks from the sproc since the VB.Net transaction will rollback all the inserts/updates inside the sproc if the sproc failed or if the other processing done inside the VB.Net transaction block failed as well. Hopefully this will save someone else a day of frustration.

-1
votes

Here you should use try catch blog inside begin transaction. like this

BEGIN
    if @id is null
        BEGIN
        set @id= (SELECT last_sequence_value FROM table_name WHERE sequence_name = 'id') + 1
            BEGIN               
                BEGIN TRANSACTION 
                    BEGIN TRY
                    -- update SQL statement here 

                    IF @@ROWCOUNT = 0
                    BEGIN                       
                        ROLLBACK TRANSACTION
                        RAISERROR('There was an error getting unique id in the table.',10,1)                    
                        RETURN
                    END
                    IF (@@ERROR <> 0)
                    BEGIN
                        ROLLBACK TRANSACTION
                        RAISERROR('There was an error updating record to the table',10,1)
                        RETURN
                    END

                   END TRY 
                   BEGIN CATCH
                         -- Raise an error with the details of the exception
                       RAISERROR(@ErrMsg, @ErrSeverity, 1) WITH SETERROR
                  END CATCH
                COMMIT TRANSACTION
            END
        END 
    else 
        BEGIN
            -- some sql select statements
        END
  END
-1
votes

First, compare

DECLARE @ErrorVar INT

RAISERROR(N'Message', 16, 1);
IF @@ROWCOUNT <> 0
    PRINT 'Rows <> 0'
IF @@ERROR <> 0
    PRINT N'Error = ' + CAST(@@ERROR AS NVARCHAR(8));
GO

And this

DECLARE @ErrorVar INT,
    @Error INT,
    @Cnt INT

RAISERROR(N'Message', 16, 1);
SELECT @ERROR = @@ERROR, @Cnt = @@ROWCOUNT
IF @Cnt <> 0
    PRINT 'Rows 0'
IF @ERROR <> 0
    PRINT N'Error = ' + CAST(@@ERROR AS NVARCHAR(8));
GO

https://docs.microsoft.com/en-us/sql/t-sql/functions/error-transact-sql Because @@ERROR is cleared and reset on each statement executed, check it immediately following the statement being verified, or save it to a local variable that can be checked later. +