C# 在 SQL Server 2005 上使用 System.Transactions 的 TransactionInDoubtException

TransactionInDoubtException using System.Transactions on SQL Server 2005

提问by Mark

The underlying question to this post is "Why would a non-promoted LTM Transaction ever be in doubt?"

这篇文章的基本问题是“为什么非推广的 LTM 交易会有疑问？”

I'm getting System.Transactions.TransactionInDoubtException and i can't explain why. Unfortunately i cannot reproduce this issue but according to trace files it does happen. I am using SQL 2005, connecting to one database and using one SQLConnection so i don't expect promotion to take place. The error message indicates a timeout. However, sometimes I get a timeout message but the exception is that the transaction has aborted as opposed to in doubt, which is much easier to handle.

我收到 System.Transactions.TransactionInDoubtException，我无法解释原因。不幸的是，我无法重现这个问题，但根据跟踪文件，它确实发生了。我正在使用 SQL 2005，连接到一个数据库并使用一个 SQLConnection，因此我不希望进行升级。错误消息指示超时。但是，有时我会收到超时消息，但例外情况是事务已中止，而不是有疑问，这更容易处理。

Here is the full stack trace:

这是完整的堆栈跟踪：

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.
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)
   at System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParserStateObject.ReadNetworkPacket()
   at System.Data.SqlClient.TdsParserStateObject.ReadBuffer()
   at System.Data.SqlClient.TdsParserStateObject.ReadByte()
   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.SqlInternalConnectionTds.ExecuteTransaction(TransactionRequest transactionRequest, String name, IsolationLevel iso, SqlInternalTransaction internalTransaction, Boolean isDelegateControlRequest)
   at System.Data.SqlClient.SqlDelegatedTransaction.SinglePhaseCommit(SinglePhaseEnlistment enlistment)
   --- End of inner exception stack trace ---
   at System.Transactions.TransactionStateInDoubt.EndCommit(InternalTransaction tx)
   at System.Transactions.CommittableTransaction.Commit()
   at System.Transactions.TransactionScope.InternalDispose()
   at System.Transactions.TransactionScope.Dispose()

Any ideas? Why am i getting in doubpt and what should i do when i get it?

有任何想法吗？为什么我会怀疑，当我得到它时我该怎么办？

EDIT for more information

编辑以获取更多信息

I actually still don't have the answer for this. What I did realize is that the transaction actually partially commits. One table gets the insert but the other does not get the update. The code is HEAVILY traced and there is not much room for me to be missing something.

对于这个问题，我实际上仍然没有答案。我确实意识到事务实际上是部分提交的。一个表获得插入但另一个没有获得更新。代码被大量跟踪，我没有太多空间可以遗漏一些东西。

Is there a way I can easily find out if the transaction has been promoted. Can we tell from the stack trace if it is? SIngle Phase commit (which is in the strack trace) seems to indicate no promotion to me, but maybe i'm missing something. If its not getting promoted then how can it be in doubt.

有没有一种方法可以轻松查明交易是否已被提升。我们可以从堆栈跟踪中判断是否是这样吗？单阶段提交（在 strack 跟踪中）似乎对我没有任何提升，但也许我错过了一些东西。如果它没有得到提升，那么它怎么可能有疑问。

Another interesting piece to the puzzle is that i create a clone of the current transaction. I do that as a workarround to this issue. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=914869&SiteID=1

另一个有趣的难题是我创建了当前事务的克隆。我这样做是为了解决这个问题。 http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=914869&SiteID=1

Unfortunately, i don't know if this issue has been resolved. Maybe creating the clone is causing a problem. Here is the relevant code

不幸的是，我不知道这个问题是否已经解决。也许创建克隆会导致问题。这是相关的代码

using (TransactionScope ts = new TransactionScope())
{
   transactionCreated = true;
   //part of the workarround for microsoft defect mentioned in the beginning of this class
   Transaction txClone = Transaction.Current.Clone();
   transactions[txClone] = txClone;
   Transaction.Current.TransactionCompleted += new TransactionCompletedEventHandler(TransactionCompleted);
   MyTrace.WriteLine("Transaction clone stored and attached to event");

   m_dataProvider.PersistPackage(ControllerID, package);
   MyTrace.WriteLine("Package persisted");
   m_dataProvider.PersistTransmissionControllerStatus(this);
   MyTrace.WriteLine("Transmission controlled updated");
   ts.Complete();
}

Thanks

谢谢

采纳答案by Mark

The answer is that it can't. What apparently was happening was that promotion was taking place. (We accidentally discovered this) I still don't know how to detect if a promotion attempt is happening. That would have been extreamly useful in detecting this.

答案是不能。显然正在发生的是促销正在进行中。（我们偶然发现了这一点）我仍然不知道如何检测是否正在发生促销尝试。这对于检测这一点非常有用。

回答by Bogdan_Ch

Hard to advice anything without looking into your code, but my first suggestion is that TransactionScope() is an overhead when you have 1 SQL server with 1 connection.

在不查看您的代码的情况下很难提出任何建议，但我的第一个建议是，当您有 1 个具有 1 个连接的 SQL 服务器时， TransactionScope() 是一种开销。

Why not to use System.Data.SqlClient.SqlTransaction() instead?

为什么不改用 System.Data.SqlClient.SqlTransaction() 呢？

Documentation sais that "If a connection to a remote server is opened within a database transaction, the connection to the remote server is enlisted into the distributed transaction and the local transaction is automatically promoted to a distributed transaction." However if you use really only one connection is a very strange error. Are you sure that you are not calling any 3rd party components that can create connections to MS SQL, MS MQ or something else that will require a distibuted transaction to be created?

文档说“如果在数据库事务中打开到远程服务器的连接，则到远程服务器的连接会被登记到分布式事务中，并且本地事务会自动提升为分布式事务。” 但是如果你真的只使用一个连接是一个非常奇怪的错误。您确定您没有调用任何可以创建与 MS SQL、MS MQ 或其他需要创建分布式事务的连接的第三方组件吗？

Also if you use TransactionScope() in SQL Server CLR procedure, it will promote transaction in any case.

此外，如果您在 SQL Server CLR 过程中使用 TransactionScope()，它在任何情况下都会提升事务。

Also if you call a store procedure that access a table from linked SQL server, I suppose this will also require promotion.

此外，如果您调用从链接的 SQL 服务器访问表的存储过程，我想这也需要升级。

The question is quite old, perhaps you already know the answer and could post it here for others. Thanks!

这个问题很老了，也许你已经知道答案了，可以把它贴在这里给其他人。谢谢！

回答by Joshua

Beats the heck out of me.

把我吓坏了。

I'm in the habit of doing ExecuteNonQuery on "BEGIN TRANSACTION" and "COMMIT" or "ROLLBACK" by hand.

我习惯于手动对“BEGIN TRANSACTION”和“COMMIT”或“ROLLBACK”执行 ExecuteNonQuery。

Quite by accident this worked out really well when some code needed to work just the same whether it was in a transaction or not.

很偶然地，当某些代码需要以相同的方式工作时，无论它是否在事务中，这都非常有效。

回答by Michael Mann

I am actually having the same problem and it seems to be related to the specs of the db server. I would have your dba have a look at the CPU utilization of the box while you are executing this code. This happens in our environment because we are attempting an update operation on a large number of rows in our database within a transaction. This is happening on our OLTP database on one of our most used tables which will create lock contention. What I find fascinating about the problem is the time out aspect which I see in your stack trace. No matter what time out values you set whether it be on the command or as an argument to the constructor of the TransactionScope it does not seem to adress the issue. The way I am going to address the issue is to chunk the commits. Hope this helps

我实际上遇到了同样的问题，它似乎与数据库服务器的规格有关。当您执行此代码时，我会让您的 dba 查看该框的 CPU 利用率。这发生在我们的环境中，因为我们正在尝试对事务中数据库中的大量行进行更新操作。这发生在我们最常用的表之一上的 OLTP 数据库上，这将产生锁争用。我觉得这个问题很吸引人的是我在您的堆栈跟踪中看到的超时方面。无论您设置的超时值是在命令上还是作为 TransactionScope 构造函数的参数，它似乎都没有解决问题。我要解决这个问题的方法是将提交分块。希望这可以帮助

回答by BateTech

The current accepted answer is that a non-promoted LTM (non-MSDTC) Transaction can never be in doubt. After much research into a similar issue, I have found that this is incorrect.

当前公认的答案是非促销 LTM（非 MSDTC）交易永远不会有疑问。经过对类似问题的大量研究，我发现这是不正确的。

Due to the way the single phase commit protocol is implemented, there is a small period of time where the transaction is "in doubt", after the Transaction Manager sends the SinglePhaseCommit request to its subordinate, and before the subordinate replies with either a committed/aborted/or prepared (needs to promote/escalate to MSDTC) message. If the connection is lost during this time, then the transaction is "in doubt", b/c the TransactionManager never received a response when it asked the subordinate to perform a SinglePhaseCommit.

由于单阶段提交协议的实现方式，在事务管理器向其下级发送 SinglePhaseCommit 请求之后，在下级回复一个已提交/中止/或准备（需要提升/升级到 MSDTC）消息。如果在此期间连接丢失，则事务“有疑问”，b/c 当 TransactionManager 要求下级执行 SinglePhaseCommit 时，它从未收到响应。

From MSDN Single-Phase Commit, also see "Single phase commit flow" image at the bottom of this answer:

从MSDN Single-Phase Commit，另请参阅此答案底部的“单阶段提交流程”图像：

There is a possible disadvantage to this optimization: if the transaction manager loses contact with the subordinate participant after sending the Single-Phase Commit request but before receiving an outcome notification, it has no reliable mechanism for recovering the actual outcome of the transaction. Consequently, the transaction manager sends an In Doubt outcome to any applications or voters awaiting informational outcome notification

这种优化可能有一个缺点：如果事务管理器在发送单阶段提交请求后但在收到结果通知之前失去了与下级参与者的联系，则它没有可靠的机制来恢复事务的实际结果。因此，事务管理器向任何等待信息结果通知的应用程序或投票者发送不确定结果

Also here are some practical examples of things I've found that cause System.Transaction promotion/escalation to a MSDTC transaction (this is not directly related to the OP, but I have found very useful. Tested in VS 2013, SQL Server 2008 R2, .NET 4.5 except where noted):

这里还有一些我发现导致 System.Transaction 提升/升级到 MSDTC 事务的实际示例（这与 OP 没有直接关系，但我发现它非常有用。在 VS 2013、SQL Server 2008 R2 中测试, .NET 4.5 除非另有说明）：

1. (this one is specific to SQL Server 2005 or if compatibility level < 100)- Calling Connection.Open() more than once at any point within a TransactionScope. This also includes calling .Open(), .Close(), .Open() on the SAME connection instance.
2. Opening nestedconnections within a TransactionScope
3. Using multiple connections that do not use connection pooling, even if they are not nested and connecting to the same database.
4. Queries that involve linked servers
5. SQL CLR procedures that use TransactionScope. See: http://technet.microsoft.com/en-us/library/ms131084.aspx"TransactionScope should be used only when local and remote data sources or external resource managers are being accessed. This is because TransactionScope [within CLR] always causes transactions to promote, even if it is being used only within a context connection"
6. It appears that if using connection pooling, and the same exact physical connection that was used in Connection1 is not available for some reason in Connections "2 to N" then the entire transaction will be promoted (b/c these are treated as 2 separate durable resources, item #2 is the MS official list below). I have not tested/confirmed this particular case, but is my understanding of how it works. It makes sense b/c behind the scenes this is similar to using nested connections or not using connection pooling b/c multiple physical connections are used. http://msdn.microsoft.com/en-us/library/8xx3tyca(v=vs.110).aspx"When a connection is closed and returned to the pool with an enlisted System.Transactions transaction, it is set aside in such a way that the next request for that connection pool with the same System.Transactions transaction will return the same connection if it is available. If such a request is issued, and there are no pooled connections available, a connection is drawn from the non-transacted part of the pool and enlisted"

1. （这是特定于 SQL Server 2005 或如果兼容性级别 < 100）- 在 TransactionScope 内的任何点多次调用 Connection.Open()。这还包括在 SAME 连接实例上调用 .Open()、.Close()、.Open()。
2. 在 TransactionScope 中打开嵌套连接
3. 使用不使用连接池的多个连接，即使它们没有嵌套并连接到同一个数据库。
4. 涉及链接服务器的查询
5. 使用 TransactionScope 的 SQL CLR 过程。请参阅：http: //technet.microsoft.com/en-us/library/ms131084.aspx“仅在访问本地和远程数据源或外部资源管理器时才应使用 TransactionScope。这是因为TransactionScope [在 CLR 中] 始终导致事务促进，即使它仅在上下文连接中使用”
6. 看来，如果使用连接池，并且 Connection1 中使用的相同物理连接由于某种原因在 Connections“2 to N”中不可用，那么整个事务将被提升（b/c 这些被视为 2 个独立的持久资源，第 2 项是下面的 MS 官方列表）。我没有测试/确认这个特殊情况，但我对它是如何工作的理解。b/c 在幕后是有意义的，这类似于使用嵌套连接或不使用连接池 b/c 使用多个物理连接。 http://msdn.microsoft.com/en-us/library/8xx3tyca(v=vs.110).aspx“当连接关闭并返回到具有登记 System.Transactions 事务的池时，它以这样一种方式被搁置，即具有相同 System.Transactions 事务的该连接池的下一个请求将返回相同的连接，如果它是可用。如果发出这样的请求，并且没有可用的池连接，则从池的非事务部分中提取连接并登记“

And here is the MS official list of what causes escalation: http://msdn.microsoft.com/en-us/library/ms229978(v=vs.85).aspx

这是导致升级的原因的 MS 官方列表：http: //msdn.microsoft.com/en-us/library/ms229978(v= vs.85).aspx

1. At least one durable resource that does not support single-phase notifications is enlisted in the transaction.
2. At least two durable resources that support single-phase notifications are enlisted in the transaction. For example, enlisting a single connection with SQL Server 2005 does not cause a transaction to be promoted. However, whenever you open a second connection to a SQL Server 2005 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.
3. A request to "marshal" the transaction to a different application domain or different process is invoked. For example, the serialization of the transaction object across an application domain boundary. The transaction object is marshaled-by-value, meaning that any attempt to pass it across an application domain boundary (even in the same process) results in serialization of the transaction object. You can pass the transaction objects by making a call on a remote method that takes a Transaction as a parameter or you can try to access a remote transactional-serviced component. This serializes the transaction object and results in an escalation, as when a transaction is serialized across an application domain. It is being distributed and the local transaction manager is no longer adequate.

1. 至少一个不支持单阶段通知的持久资源被登记在事务中。
2. 事务中至少征用了两个支持单阶段通知的持久资源。例如，登记与 SQL Server 2005 的单个连接不会导致事务被提升。但是，每当您打开到 SQL Server 2005 数据库的第二个连接导致数据库登记时，System.Transactions 基础结构会检测到它是事务中的第二个持久资源，并将其升级为 MSDTC 事务。
3. 调用将事务“编组”到不同应用程序域或不同进程的请求。例如，跨应用程序域边界的事务对象的序列化。事务对象是按值编组的，这意味着任何跨应用程序域边界（即使在同一进程中）传递它的尝试都会导致事务对象的序列化。您可以通过调用将事务作为参数的远程方法来传递事务对象，也可以尝试访问远程事务服务组件。这会序列化事务对象并导致升级，就像跨应用程序域序列化事务一样。它正在分发，本地事务管理器不再适用。