原因System.Transactions.TransactionInDoubtException [英] Reason for System.Transactions.TransactionInDoubtException

问题描述

我有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)

我GOOGLE了一下它，并发现了一些有关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/库/ 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事务。
  

• At least two durable resources that support single-phase notifications are enlisted in the transaction. For example, enlisting a single connection with does not cause a transaction to be promoted. However, whenever you open a second connection to a database causing the database to enlist, the System.Transactions infrastructure detects that it is the second durable resource in the transaction, and escalates it to an MSDTC transaction.

注：我看过一些文章，指出这仅适用于SQL 2005，并且SQL 2008+是更聪明的MSDTC推广。该SQL 2008，这些国家只提升到MSDTC当多个连接打开的同时。参见：<一href="http://stackoverflow.com/questions/1690892/transactionscope-automatically-escalating-to-msdtc-on-some-machines">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?

另外，你的内部异常是超时（System.Data.SqlClient.SqlException：在超时时间已到），而不是一个死锁。尽管这两个都涉及到阻塞，他们是不一样的东西。阻塞的原因，当应用程序停止等待由另一个连接阻塞，从而使目前的语句可以获取该资源上的锁的资源的暂停出现。当两个不同的连接正在争夺相同的资源A 僵局时，他们阻止的方式，他们将永远无法完成，除非被终止其中一个连接（本为什么死锁错误消息说，交易......已被选作死锁牺牲品）。由于您的错误是超时，这解释了为什么僵局查询返回一个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（<一href="http://msdn.microsoft.com/en-us/library/system.transactions.transactionindoubtexception%28v=vs.110%29.aspx" rel="nofollow">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.

此异常也被抛出时，试图为   提交事务和交易变得的不确定。

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.

原因：可能有一些不同的原因，但很难确定具体原因没有源$ C ​​$ C被张贴。

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+，并且您有多个连接打开的同时（即嵌套连接或并行运行多个异步连接），那么该交易将被提升到一个MSDTC事务。
3. 如果您有的try / catch {重试，如果超时/死锁}，也就是在您的code运行的逻辑，那么这可能会导致问题时，交易是在 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的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！