問題描述

我有 2 個在 Sql Server 數據庫中讀取和生成數據的作業.每隔一段時間，作業會因 System.Transactions.TransactionInDoubtException 而崩潰.確切的堆棧跟蹤是:

I have 2 Jobs that read and produce data in a Sql Server Database. Every once in a while the jobs crash with a System.Transactions.TransactionInDoubtException. The exact stack trace is:

 Unhandled Exception: System.Transactions.TransactionInDoubtException: The transaction is in doubt. ---> System.Data.SqlClient.SqlException: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception: The wait operation timed out. Exitcode: -532462766
    --- End of inner exception stack trace ---
    at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
    at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)
    at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
    at System.Data.SqlClient.TdsParserStateObject.TryReadNetworkPacket()
    at System.Data.SqlClient.TdsParserStateObject.TryPrepareBuffer()
    at System.Data.SqlClient.TdsParserStateObject.ReadSniSyncOverAsync()
    at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
    at System.Data.SqlClient.TdsParserStateObject.TryReadByte(Byte& value)
    at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

我在谷歌上搜索了一下，發現了一些關于 MSDTC 的信息，但我認為這不是問題，因為事務應該是本地的，因為作業只能在單個數據庫上工作.以下查詢:

I googled a bit about it and found something about MSDTC, but I think this can't be the problem because the Transaction should be local since the jobs only work on a single database. The following query:

SELECT cntr_value AS NumOfDeadLocks
  FROM sys.dm_os_performance_counters
 WHERE object_name = 'SQLServer:Locks'
   AND counter_name = 'Number of Deadlocks/sec'
   AND instance_name = '_Total'

表明數據庫沒有發生死鎖，所以死鎖不可能是原因.我在互聯網上找不到任何其他資源來提供有關異常原因的確切信息.那么有人知道可能是什么原因或如何找到此錯誤的根源嗎?

shows that there have been no deadlocks on the database, so deadlocks can't be the reason. I couldn't find any other resource on the internet which gives exact information about the reason of the exception. So has anybody a idea what the reason could be or how to find the root of this error?

推薦答案

即使事務是本地的，如果在同一個事務范圍內打開多個連接，事務仍然會升級到 MSDTC，根據這篇文章:http://msdn.microsoft.com/en-us/library/ms229978(v=vs.110).aspx

Even if the transaction is local, transaction will still escalated to the MSDTC if you open multiple connections within the same transaction scope, according to this article: http://msdn.microsoft.com/en-us/library/ms229978(v=vs.110).aspx

導致 System.Transactions 基礎結構的升級將交易的所有權轉移到 MSDTC 發生在:...

An escalation that results in the System.Transactions infrastructure transferring the ownership of the transaction to MSDTC happens when: ...

• 事務中至少有兩個支持單階段通知的持久資源.例如，招募單個連接不會導致事務被提升.但是，每當您打開與數據庫的第二個連接時要登記的數據庫，System.Transactions 基礎結構檢測它是事務中的第二個持久資源，并且將其升級為 MSDTC 事務.

注意:我讀過一些文章，指出這僅適用于 SQL 2005，并且 SQL 2008+ 在 MSDTC 提升方面更聰明.這些說明 SQL 2008 僅在同時打開多個連接時才會升級為 MSDTC.請參閱:TransactionScope 在某些機器上自動升級為 MSDTC?

NOTE: I have read some articles that state that this only applies to SQL 2005, and that SQL 2008+ is smarter about the MSDTC promotion. These state that SQL 2008 will only promote to MSDTC when multiple connections are open at the same time. See: TransactionScope automatically escalating to MSDTC on some machines?

此外，您的內部異常是 Timeout (System.Data.SqlClient.SqlException: Timeout expired)，而不是 Deadlock.雖然兩者都與阻塞有關，但它們不是一回事.timeout 在阻塞導致應用程序停止等待被另一個連接阻塞的資源時發生，以便當前語句可以獲得該資源的鎖.deadlock 當兩個不同的連接競爭相同的資源時發生，并且它們以某種方式阻塞，除非其中一個連接終止，否則它們將永遠無法完成(這就是為什么死鎖錯誤消息說交易......已被選為僵局受害者").由于您的錯誤是超時，這解釋了為什么死鎖查詢返回 0 計數.

Also, your inner exception is a Timeout (System.Data.SqlClient.SqlException: Timeout expired), not a Deadlock. While both are related to blocking, they are not the same thing. A timeout occurs when blocking causes the application to stop waiting on a resource that is blocked by another connection, so that the current statement can obtain locks on that resource. A deadlock occurs when two different connections are competing for the same resources, and they are blocking in a way they will never be able to complete unless one of the connections is terminated (this why the deadlock error messages say "transaction... has been chosen as the deadlock victim"). Since your error was a Timeout, this explains why you deadlock query returned a 0 count.

System.Transactions.TransactionInDoubtException 來自 MSDN (http://msdn.microsoft.com/en-us/library/system.transactions.transactionindoubtexception(v=vs.110).aspx) 狀態:

System.Transactions.TransactionInDoubtException from MSDN (http://msdn.microsoft.com/en-us/library/system.transactions.transactionindoubtexception(v=vs.110).aspx) states:

嘗試對事務執行操作時拋出此異常這是有疑問的.當交易的狀態存在疑問時交易無法確定.具體來說，最終結果事務，無論是提交還是中止，都不會因此而為人所知交易.

This exception is thrown when an action is attempted on a transaction that is in doubt. A transaction is in doubt when the state of the transaction cannot be determined. Specifically, the final outcome of the transaction, whether it commits or aborts, is never known for this transaction.

嘗試執行以下操作時也會拋出此異常提交事務，事務變為 InDoubt.

This exception is also thrown when an attempt is made to commit the transaction and the transaction becomes InDoubt.

原因:在 TransactionScope 期間發生了一些事情，導致它在事務結束時的狀態未知.

The reason: something occurred during the TransactionScope that caused it's state to be unknown at the end of the transaction.

原因:可能有許多不同的原因，但如果不發布源代碼，很難確定您的具體原因.

The cause: There could be a number of different causes, but it is tough to identify your specific cause without the source code being posted.

檢查事項:

1. 如果您使用的是 SQL 2005，并且打開了多個連接，您的事務將被提升為 MSDTC 事務.
2. 如果您使用的是 SQL 2008+，并且您同時打開了多個連接(即嵌套連接或并行運行的多個 ASYNC 連接)，那么該事務將被提升為 MSDTC 事務.
3. 如果您的代碼中運行了try/catch{retry if timeout/deadlock}"邏輯，那么當事務在 System.Transactions.TransactionScope 中時，這可能會導致問題，因為 SQL Server 在發生超時或死鎖時自動回滾事務的方式.

1. If you are using SQL 2005, and more than one connection is opened, your transaction will be promoted to a MSDTC transaction.
2. If you are using SQL 2008+, AND you have multiple connection open at the same time (i.e. nested connections or multiple ASYNC connections running in parallel), then the transaction will be promoted to a MSDTC transaction.
3. If you have "try/catch{retry if timeout/deadlock}" logic that is running within your code, then this can cause issues when the transaction is within a System.Transactions.TransactionScope, because of the way that SQL Server automatically rolls back transaction when a timeout or deadlock occurs.

這篇關于System.Transactions.TransactionInDoubtException 的原因的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！