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

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]asbegin    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 herelbexit:        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 catchendgo

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

returncommit

instead of

commitreturn   

in one stored procedure.

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 ONSET NoCount ONBegin Try      BEGIN TRANSACTION         //Insert ,update queries         COMMITEnd Try Begin Catch      ROLLBACKEnd Catch

Source