问题描述

我有 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 的原因的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！