Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0
SqlSql Server-2012SqlexceptionSql Problem Overview
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.
Sql Solutions
Solution 1 - Sql
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
Solution 2 - Sql
I had this problem too. For me, the reason was that I was doing
return
commit
instead of
commit
return
in one stored procedure.
Solution 3 - Sql
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
Solution 4 - Sql
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.
Solution 5 - Sql
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...
Solution 6 - Sql
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
Solution 7 - Sql
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
Solution 8 - Sql
I had the same error message, my mistake was that I had a semicolon at the end of COMMIT TRANSACTION line
Solution 9 - Sql
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
Solution 10 - Sql
I encountered this error once after omitting this statement from my transaction.
COMMIT TRANSACTION [MyTransactionName]
Solution 11 - Sql
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!
Solution 12 - Sql
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
Solution 13 - Sql
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
Solution 14 - Sql
For me, the issue was that I forgot to add the output
keyword following some output parameters of a SP call within the transaction.
Solution 15 - Sql
If you are having a code structure of something like:
SELECT 151
RETURN -151
Then use:
SELECT 151
ROLLBACK
RETURN -151