問(wèn)題描述

我有 2 個(gè)在 Sql Server 數(shù)據(jù)庫(kù)中讀取和生成數(shù)據(jù)的作業(yè).每隔一段時(shí)間，作業(yè)會(huì)因 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)

我在谷歌上搜索了一下，發(fā)現(xiàn)了一些關(guān)于 MSDTC 的信息，但我認(rèn)為這不是問(wèn)題，因?yàn)槭聞?wù)應(yīng)該是本地的，因?yàn)樽鳂I(yè)只能在單個(gè)數(shù)據(jù)庫(kù)上工作.以下查詢:

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'

表明數(shù)據(jù)庫(kù)沒(méi)有發(fā)生死鎖，所以死鎖不可能是原因.我在互聯(lián)網(wǎng)上找不到任何其他資源來(lái)提供有關(guān)異常原因的確切信息.那么有人知道可能是什么原因或如何找到此錯(cuò)誤的根源嗎?

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?

推薦答案

即使事務(wù)是本地的，如果在同一個(gè)事務(wù)范圍內(nèi)打開(kāi)多個(gè)連接，事務(wù)仍然會(huì)升級(jí)到 MSDTC，根據(jù)這篇文章: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

導(dǎo)致 System.Transactions 基礎(chǔ)結(jié)構(gòu)的升級(jí)將交易的所有權(quán)轉(zhuǎn)移到 MSDTC 發(fā)生在:...

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

• 事務(wù)中至少有兩個(gè)支持單階段通知的持久資源.例如，招募單個(gè)連接不會(huì)導(dǎo)致事務(wù)被提升.但是，每當(dāng)您打開(kāi)與數(shù)據(jù)庫(kù)的第二個(gè)連接時(shí)要登記的數(shù)據(jù)庫(kù)，System.Transactions 基礎(chǔ)結(jié)構(gòu)檢測(cè)它是事務(wù)中的第二個(gè)持久資源，并且將其升級(jí)為 MSDTC 事務(wù).

注意:我讀過(guò)一些文章，指出這僅適用于 SQL 2005，并且 SQL 2008+ 在 MSDTC 提升方面更聰明.這些說(shuō)明 SQL 2008 僅在同時(shí)打開(kāi)多個(gè)連接時(shí)才會(huì)升級(jí)為 MSDTC.請(qǐng)參閱:TransactionScope 在某些機(jī)器上自動(dòng)升級(jí)為 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?

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

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 來(lái)自 MSDN (http://msdn.microsoft.com/en-us/library/system.transactions.transactionindoubtexception(v=vs.110).aspx) 狀態(tài):

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

嘗試對(duì)事務(wù)執(zhí)行操作時(shí)拋出此異常這是有疑問(wèn)的.當(dāng)交易的狀態(tài)存在疑問(wèn)時(shí)交易無(wú)法確定.具體來(lái)說(shuō)，最終結(jié)果事務(wù)，無(wú)論是提交還是中止，都不會(huì)因此而為人所知交易.

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.

嘗試執(zhí)行以下操作時(shí)也會(huì)拋出此異常提交事務(wù)，事務(wù)變?yōu)?InDoubt.

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

原因:在 TransactionScope 期間發(fā)生了一些事情，導(dǎo)致它在事務(wù)結(jié)束時(shí)的狀態(tài)未知.

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

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

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.

檢查事項(xiàng):

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

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.

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