SQL EXECUTE 之后的事务计数表示 BEGIN 和 COMMIT 语句的数量不匹配。先前计数 = 1，当前计数 = 0

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

提问by Vignesh Kumar A

I have an Insertstored procedure which will feed data to Table1and get the Column1value from Table1and call the second stored procedure which will feed the Table2.

我有一个Insert存储过程，它将提供数据Table1并从中获取Column1值Table1并调用将提供 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.

EXECUTE 之后的事务计数表示 BEGIN 和 COMMIT 语句的数量不匹配。先前计数 = 1，当前计数 = 0。

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

我已经阅读了其他此类问题中的答案，但无法找到提交计数到底在哪里搞砸了。

回答by Remus Rusanu

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).

如果您有一个 TRY/CATCH 块，那么可能的原因是您正在捕获事务中止异常并继续。在 CATCH 块中，您必须始终检查XACT_STATE()并处理适当的中止和不可提交（注定）的事务。如果您的调用者开始一个事务并且被调用者遇到了死锁（它中止了事务），那么被调用者将如何与调用者通信事务已中止并且不应继续“照常营业”？唯一可行的方法是重新引发异常，迫使调用者处理这种情况。如果你默默地吞下一个中止的事务并且调用者继续假设仍然在原始事务中，那么只有混乱才能确保（并且你得到的错误是引擎试图保护自己的方式）。

I recommend you go over Exception handling and nested transactionswhich 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

回答by seguso

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

我也有这个问题。对我来说，原因是我在做

return
commit

instead of

代替

commit
return   

in one stored procedure.

在一个存储过程中。

回答by Amarnath Balasubramanian

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

如果错误出现在您的存储过程中，这可以锁定数据库表，因为在没有异常处理的情况下由于某些运行时错误而导致事务未完成您可以使用如下异常处理。 设置 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

来源

回答by niklasolsn

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

请注意，如果您使用嵌套事务，则 ROLLBACK 操作会回滚所有嵌套事务，包括最外面的事务。

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

这可能与 TRY/CATCH 结合使用，导致您描述的错误。在此处查看更多信息。

回答by Justin

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.

我发现我必须使用 2 个存储过程，一个是“worker”，一个是带有 try/catch 的包装器，两者的逻辑类似于 Remus Rusanu 概述的逻辑。工作捕获用于处理“正常”失败，包装捕获用于处理编译失败错误。

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

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

Errors Unaffected by a TRY…CATCH Construct

不受 TRY…CATCH 结构影响的错误

The following types of errors are not handled by a CATCH block when they occur at the same level of executionas 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.

当以下类型的错误发生在与TRY…CATCH 构造相同的执行级别时，它们不会被 CATCH 块处理：

• 阻止批处理运行的编译错误，例如语法错误。
• 语句级重编译过程中出现的错误，例如编译后由于名称解析延迟导致的对象名称解析错误。

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

希望这可以帮助其他人节省几个小时的调试时间......

回答by Zsombor Zsuffa

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

我有同样的错误消息，我的错误是在 COMMIT TRANSACTION 行的末尾有一个分号

回答by Casey Crookston

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

在我的情况下，正在由导致错误RETURN内BEGIN TRANSACTION。所以我有这样的事情：

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

回答by Nitin Patwekari

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

SQL 存储过程中的语句

回答by Sen Alexandru

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

就我而言，我不小心在查询中有一个 BEGIN TRAN 语句

回答by Rajan Tikare

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

这还取决于您从 C# 代码调用 SP 的方式。如果 SP 返回某个表类型值，则使用 E​​xecuteStoreQuery 调用 SP，如果 SP 不返回任何值，则使用 E​​xecuteStoreCommand 调用 SP