EXECUTE 之后的事务计数表示 BEGIN 和 COMMIT 语句的数量不匹配.先前计数 = 1，当前计数 = 0 [英] Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0

问题描述

我有一个 Insert 存储过程，它会将数据提供给 Table1 并从 Table1 获取 Column1 值并调用将提供 Table2 的第二个存储过程.

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

我收到以下错误:

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

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.

推荐答案

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

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

这篇关于EXECUTE 之后的事务计数表示 BEGIN 和 COMMIT 语句的数量不匹配.先前计数 = 1，当前计数 = 0的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！