119

I have an Insert stored procedure which will feed data to Table1 and get the Column1 value from Table1 and call the second stored procedure which will feed the Table2.

But when I call The second stored procedure as:

Exec USPStoredProcName

I get the following error:

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

I have read the answers in other such questions and am unable to find where exactly the commit count is getting messed up.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Vignesh Kumar A
  • 27,863
  • 13
  • 63
  • 115

18 Answers18

138

If you have a TRY/CATCH block then the likely cause is that you are catching a transaction abort exception and continue. In the CATCH block you must always check the XACT_STATE() and handle appropriate aborted and uncommitable (doomed) transactions. If your caller starts a transaction and the calee hits, say, a deadlock (which aborted the transaction), how is the callee going to communicate to the caller that the transaction was aborted and it should not continue with 'business as usual'? The only feasible way is to re-raise an exception, forcing the caller to handle the situation. If you silently swallow an aborted transaction and the caller continues assuming is still in the original transaction, only mayhem can ensure (and the error you get is the way the engine tries to protect itself).

I recommend you go over Exception handling and nested transactions which shows a pattern that can be used with nested transactions and exceptions:

create procedure [usp_my_procedure_name]
as
begin
    set nocount on;
    declare @trancount int;
    set @trancount = @@trancount;
    begin try
        if @trancount = 0
            begin transaction
        else
            save transaction usp_my_procedure_name;

        -- Do the actual work here

lbexit:
        if @trancount = 0
            commit;
    end try
    begin catch
        declare @error int, @message varchar(4000), @xstate int;
        select @error = ERROR_NUMBER(), @message = ERROR_MESSAGE(), @xstate = XACT_STATE();
        if @xstate = -1
            rollback;
        if @xstate = 1 and @trancount = 0
            rollback
        if @xstate = 1 and @trancount > 0
            rollback transaction usp_my_procedure_name;

        raiserror ('usp_my_procedure_name: %d: %s', 16, 1, @error, @message) ;
    end catch
end
go
Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • 3
    Thanks for your help. By using Raiserror I have found the problem.It's about trying to insert NULL value to the NOT NULL field – Vignesh Kumar A Feb 21 '14 at 09:48
  • But a constraint check validation would not abort the transaction. Are you explicitly rolling back in the catch, or do you use `xact_abort on`? – Remus Rusanu Feb 21 '14 at 09:50
  • Im explicitly rolling back – Vignesh Kumar A Feb 21 '14 at 09:52
  • Try the pattern I posted in my response instead. – Remus Rusanu Feb 21 '14 at 09:53
  • 2
    I have tried this pattern but still it does not work - when I have an external transaction this pattern creates a savepoint and in case of a critical error (uncomittable transaction) rollsback the outer transaction - this still causes a @@trancount =1 before entering procedure and @@trancount = 0 when exiting it – sparrow Sep 11 '15 at 14:10
  • 4
    I think this bit in the CATCH is wrong: `if @xstate = -1 rollback;` Looking at this [MSDN example](https://msdn.microsoft.com/en-us/library/ms188378.aspx#Anchor_4), we should **not** rollback the full transaction unless there was **not** an outer transaction (that is, unless we did `begin tran`). I think the procedure should only `rollback` if we started the transaction, which would fix @sparrow's problem. – Nick Feb 19 '16 at 21:41
  • This may be years later, but @Nick is correct, unless there is something we don't see/understand – RoLYroLLs Nov 03 '17 at 21:20
  • An issue for me using this is that depending on which condition was satisfied, the outer rollback after you return from EXEC sp_stored_procedure no longer exists. Using SQL Server 2017 – JoeManiaci Jan 15 '19 at 18:05
83

I had this problem too. For me, the reason was that I was doing

return
commit

instead of

commit
return   

in one stored procedure.

seguso
  • 2,024
  • 2
  • 18
  • 20
  • 6
    @seguso - this was very helpful. Thank you for sharing. Sometimes something so simply gets underneath the dust. Happens to the best of 'em. – Leo Gurdian Mar 10 '17 at 08:45
  • This was the problem for me, but was less obvious because we were wrapping several sproc calls in one big transaction via our data access layer-- so just looking at the sproc you couldn't tell there was a transaction at all. If you have this issue, make sure there isn't something outside of the sproc itself that is creating a transaction. If there is then you may not be able to use return statements within the sproc at all. – EF0 Nov 09 '18 at 22:13
  • This was me, I had a transaction and was returning before my commit transaction in an if/else statement – Kevin Jul 29 '19 at 14:47
23

This normally happens when the transaction is started and either it is not committed or it is not rollback.

In case the error comes in your stored procedure, this can lock the database tables because transaction is not completed due to some runtime errors in the absence of exception handling You can use Exception handling like below. SET XACT_ABORT

SET XACT_ABORT ON
SET NoCount ON
Begin Try 
     BEGIN TRANSACTION 
        //Insert ,update queries    
     COMMIT
End Try 
Begin Catch 
     ROLLBACK
End Catch

Source

Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
Amarnath Balasubramanian
  • 9,300
  • 8
  • 34
  • 62
11

Be aware of that if you use nested transactions, a ROLLBACK operation rolls back all the nested transactions including the outer-most one.

This might, with usage in combination with TRY/CATCH, result in the error you described. See more here.

niklasolsn
  • 303
  • 5
  • 10
11

This can also occur if your stored procedure encounters a compile failure after opening a transaction (e.g. table not found, invalid column name).

I found i had to use 2 stored procedures a "worker" one and a wrapper one with try/catch both with logic similar to that outlined by Remus Rusanu. The worker catch is used to handle the "normal" failures and the wrapper catch to handle compile failure errors.

https://msdn.microsoft.com/en-us/library/ms175976.aspx

Errors Unaffected by a TRY…CATCH Construct

The following types of errors are not handled by a CATCH block when they occur at the same level of execution as the TRY…CATCH construct:

  • Compile errors, such as syntax errors, that prevent a batch from running.
  • Errors that occur during statement-level recompilation, such as object name resolution errors that occur after compilation because of deferred name resolution.

Hopefully this helps someone else save a few hours of debugging...

Justin
  • 1,303
  • 15
  • 30
  • 1
    Thanks Justin. Nice observation. In my case I was doing an aggregate inside an update which does not produce compilation errors during SP save but was indeed invalid syntax - "An aggregate may not appear in the set list of an UPDATE statement" – kuklei Apr 01 '20 at 13:17
7

In my case, the error was being caused by a RETURN inside the BEGIN TRANSACTION. So I had something like this:

Begin Transaction
 If (@something = 'foo')
 Begin
     --- do some stuff
     Return
 End
commit

and it needs to be:

Begin Transaction
 If (@something = 'foo')
 Begin
     --- do some stuff
     Rollback Transaction ----- THIS WAS MISSING
     Return
 End
commit
Casey Crookston
  • 13,016
  • 24
  • 107
  • 193
3

For me after extensive debugging the fix was a simple missing throw; statement in the catch after the rollback. Without it this ugly error message is what you end up with.

begin catch
    if @@trancount > 0 rollback transaction;
    throw; --allows capture of useful info when an exception happens within the transaction
end catch
dimension314
  • 212
  • 3
  • 6
3

I had the same error message, my mistake was that I had a semicolon at the end of COMMIT TRANSACTION line

  • Simple as this. Plus, my case needed a 'ROLLBACK' statement in case where the SP won't be fully executed. Just for closing / ending the transaction. – Jose Cordero Nov 14 '18 at 15:31
3

Avoid using

RETURN

statement when you are using

BEGIN TRY
    ... 
END TRY

BEGIN CATCH
    ...
END CATCH

and

BEGIN, COMMIT & ROLLBACK

statements in SQL stored procedures

Aravin
  • 6,605
  • 5
  • 42
  • 58
1

I encountered this error once after omitting this statement from my transaction.

COMMIT TRANSACTION [MyTransactionName]
Ken Palmer
  • 2,355
  • 5
  • 37
  • 57
1

In my opinion the accepted answer is in most cases an overkill.

The cause of the error is often mismatch of BEGIN and COMMIT as clearly stated by the error. This means using:

Begin
  Begin
    -- your query here
  End
commit

instead of

Begin Transaction
  Begin
    -- your query here
  End
commit

omitting Transaction after Begin causes this error!

TheGameiswar
  • 27,855
  • 8
  • 56
  • 94
omostan
  • 840
  • 8
  • 9
1

Make sure you don't have multiple transactions in the same procedure/query out of which one or more are left uncommited.

In my case, I accidentally had a BEGIN TRAN statement in the query

Sen Alexandru
  • 1,953
  • 3
  • 19
  • 35
1

This can also depend on the way you are invoking the SP from your C# code. If the SP returns some table type value then invoke the SP with ExecuteStoreQuery, and if the SP doesn't returns any value invoke the SP with ExecuteStoreCommand

1

For me, the issue was that I forgot to add the output keyword following some output parameters of a SP call within the transaction.

1

The exact reason for this message is the rule that SQL Server implies: Transaction count should be same at the beginning and the end of execution of a procedure. In other terms, a procedure;

  • shouldn't commit/rollback a transaction that it didn't start. In this case, previous count displayed in the exception message would be greater zero, and current count is zero. Best way to prevent this is capturing transaction count (@@TRANCOUNT) at the very beginning of the execution, and using transaction statements only if it is zero. The sample procedure below is a simplest "safe" structure against this type of mistake. If this procedure is called within an existing transaction, it won't begin a new transaction nor try to commit or rollback the "inherited" one. Instead, it just re-throws the same error to caller context. This is also a good practice to keep the real source procedure of the error.
  • should decide the fate (commit or rollback) of a transaction it started, before it's execution ends. In this case, current count would be greater than previous count.

I would highly recommend reading Erland Sommarskog's Error and Transaction Handling in SQL Server thoroughly


create or alter proc sp_err266
as
begin
    set nocount on
    set xact_abort on

    declare @trancount int = @@trancount

    if @trancount = 0
        begin tran

    begin try

        raiserror('Raise an unexpected error...', 16, 1);
        
        if XACT_STATE() = 1 and @trancount = 0
            commit;

    end try
    begin catch
        if XACT_STATE() <> 0 and @trancount = 0
            rollback;
        else
            throw;
    end catch
end

Mehmet AVŞAR
  • 524
  • 10
  • 16
0

If you are having a code structure of something like:

SELECT 151
RETURN -151

Then use:

SELECT 151
ROLLBACK
RETURN -151
Vidyesh
  • 515
  • 7
  • 14
0

For me two begin transactions and multi rollback transaction causing this issue.

------------------------------------------------------------
BEGIN TRANSACTION
                           -- BEGING TRANSACTION
call of stored procedure   -- ROLLBACK TRANASCTION
                           -- ROLLBACK TRANSACTION
ROLLBACK TRANSACTION
-----------------------------------------------------------

It can rollback only one time, it won't have multi rollback statements, also check the return statements which is causing the issue.

0

In nested procedures ROLLBACK should be used with care, detailed explanation here https://stackoverflow.com/a/74479802/6204480

yikekas
  • 129
  • 1
  • 5