SQL SQLException - 事务开始/提交不匹配
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3728869/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQLException - Transaction BEGIN / COMMIT mismatch
提问by Chris Ballance
Have you encountered this exception for a stored procedure which does indeed have a balanced transaction block?
对于确实具有平衡事务块的存储过程，您是否遇到过这种异常？
I double-checked the stored procedure and it has exactly one TRANSACTION BEGINand cooresponding TRANSACTION END
我仔细检查了存储过程，它正好有一个TRANSACTION BEGIN和相应的TRANSACTION END
Error logged
错误记录
SqlException - Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0. The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION. - Delete failed - stack: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.S ... [Rest of stack trace truncated by logging system]`
Additional Info
附加信息
The stored procedure does contain EXECcalls to another stored procedure. Would a mismatched transaction pair here cause the error to be surfaced in this way?
存储过程确实包含EXEC对另一个存储过程的调用。这里不匹配的交易对会导致错误以这种方式浮出水面吗？
UpdateIt turns out that there was a violation of a foreign key constraint within the nested stored procedure. The outer transaction did not include a Try/Catch block and had SET XACT_ABORT ONspecified, which did not properly handle either a commit or rollback. Also added a check for @@TransactionCount > 0 before attempting a rollback
更新事实证明，嵌套存储过程中存在违反外键约束的情况。外部事务不包括 Try/Catch 块并且已SET XACT_ABORT ON指定，它没有正确处理提交或回滚。在尝试回滚之前还添加了对 @@TransactionCount > 0 的检查
回答by Remus Rusanu
Yes it would. Each BEGIN increments @@trancount, each commit decrements it. Only when the count gets to 0 is the transaction really committed. Your procedure, as a caller, cannot control this. It is the job of the called procedures to behave properly and balance the BEGIN and COMMIT count, if any of the called procedures has a imbalance, you'll see this error.
是的。每个 BEGIN 递增@@trancount，每个提交递减它。只有当计数变为 0 时才真正提交事务。作为调用者，您的过程无法控制这一点。被调用过程的工作是正确运行并平衡 BEGIN 和 COMMIT 计数，如果任何被调用过程不平衡，您将看到此错误。
回答by Conrad Frix
Are you sure you don't have path that produces this
你确定你没有产生这个的路径吗
BEGIN TRAN
ROLLBACK TRAN
COMMIT TRAN
回答by Mike Forman
Yes, you're going down the right path. If a nested procedure call creates transactions, they affect the calling procedure.
是的，你走在正确的道路上。如果嵌套过程调用创建事务，它们会影响调用过程。
Check that other procedure
检查其他程序
回答by seguso
Make sure you don't have inadvertently written
确保你没有无意中写
return
commit
in place of
代替
commit
return
For me, that was the problem.
对我来说，这就是问题所在。
回答by yonsk
Add this on top of PROCEDURE creation text
将此添加到 PROCEDURE 创建文本之上
SET XACT_ABORT ON;
设置 XACT_ABORT 开启；
It will ensure that if nothing got executed, the transaction is aborted entirely.
它将确保如果没有执行任何操作，事务将完全中止。
MSDN Doc: http://technet.microsoft.com/en-us/library/ms188792(v=sql.105).aspx
MSDN 文档：http: //technet.microsoft.com/en-us/library/ms188792(v=sql.105).aspx
