sql-server - 使用单个事务范围时获取 TransactionInDoubtException

Question

我正在以下面的方式使用事务范围，

var option1 = new TransactionOptions();
        option1.IsolationLevel = IsolationLevel.ReadCommitted;
        option1.Timeout = TimeSpan.FromSeconds(Convert.ToInt32(ConfigurationManager.AppSettings["Tide:TransactionTimeout"]))
using (var transactionScope = new TransactionScope(TransactionScopeOption.RequiresNew, option1))
                {
                        //delete data from database
                        //multiple BulkInsertData in different tables of database
                        //update data
                        transactionScope.Complete();
                }

没有内部交易开始。我得到 TransactionInDoubtException，我不明白为什么会发生。之前代码运行的很顺利，但是从昨天开始，突然抛出这个异常

System.Transactions.TransactionInDoubtException: The transaction is in doubt. ---> System.Data.SqlClient.SqlException: The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.TdsExecuteTransactionManagerRequest(Byte[] buffer, TransactionManagerRequestType request, String transactionName, TransactionManagerIsolationLevel isoLevel, Int32 timeout, SqlInternalTransaction transaction, TdsParserStateObject stateObj, Boolean isDelegateControlRequest) at System.Data.SqlClient.SqlInternalConnectionTds.ExecuteTransactionYukon(TransactionRequest transactionRequest, String transactionName, IsolationLevel iso, SqlInternalTransaction internalTransaction, Boolean isDelegateControlRequest) at System.Data.SqlClient.SqlDelegatedTransaction.SinglePhaseCommit(SinglePhaseEnlistment enlistment) --- End of inner exception stack trace

Answer 1

来自异常消息

The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION

您似乎正在运行一些未正确处理错误的 SQL。在运行的 SQL 中有这样的东西吗？

BEGIN TRAN

-- Some SQL here that generates an error

COMMIT TRAN

在这种情况下，BEGIN TRAN... 子句中的任何错误都将导致交易失败。当 COMMIT TRAN 被调用时，事务状态无法确定，因此出现 TransactionInDoubt 异常。您需要确保在程序开始时调用 SET XACT_ABORT ON 并正确处理错误:

SET XACT_ABORT, NOCOUNT ON
BEGIN TRY
    BEGIN TRAN
   -- Some SQL here that generates an error
    COMMIT TRAN
END TRY
BEGIN CATCH
   IF @@trancount > 0 ROLLBACK TRANSACTION
   DECLARE @msg nvarchar(2048) = error_message()
   RAISERROR (@msg, 16, 1)
   RETURN 55555
END CATCH

参见 this article on error handling in SQL Server