CODEWITHSUNDEEP
sqlsql-servert-sqlstored-procedures
Gurucharan Balakuntla Maheshku
Gurucharan Balakuntla Maheshku

Reputation: 2499

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

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.

Upvotes: 0

Views: 12165

Answers (4)

Jeff Joy
Jeff Joy

Reputation: 31

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.

Upvotes: 0

Gurucharan Balakuntla Maheshku
Gurucharan Balakuntla Maheshku

Reputation: 2499

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.

Upvotes: 4

Stanislav Kundii
Stanislav Kundii

Reputation: 2894

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://learn.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. +

Upvotes: -1

Newaz Sharif
Newaz Sharif

Reputation: 424

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

Upvotes: -1

Related Questions