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

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.

我实际上仍然没有这个答案。我所意识到的是,交易实际上是部分提交的。一个表获取插入但另一个表未获得更新。代码是HEAVILY跟踪,没有太多空间让我错过一些东西。

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 trace中)似乎表明没有升级给我,但也许我错过了一些东西。如果它没有得到提升那么它怎么会有疑问。

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

5 个解决方案

#1

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单阶段提交,还可以在此答案的底部看到“单阶段提交流”图像:

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

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

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.

(这个特定于SQL Server 2005或兼容级别<100) - 在TransactionScope中的任何位置多次调用Connection.Open()。这还包括在SAME连接实例上调用.Open(),. Close(),. Open()。

2. Opening nested connections within a TransactionScope

在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"

使用TransactionScope的SQL CLR过程。请参阅:http://technet.microsoft.com/en-us/library/ms131084.aspx“只有在访问本地和远程数据源或外部资源管理器时才应使用TransactionScope。这是因为TransactionScope [在CLR内]始终导致事务促进,即使它仅在上下文连接中使用“

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"

似乎如果使用连接池,并且Connection1中使用的相同的物理连接由于某种原因在Connections“2到N”中不可用,则整个事务将被提升(b / c这些被视为2个单独持久的资源,项目#2是下面的MS官方列表)。我没有测试/确认这个特例,但是我对它是如何工作的理解。这在幕后是有意义的,这类似于使用嵌套连接或不使用连接池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.

在事务中登记至少两个支持单阶段通知的持久资源。例如,与SQL Server 2005建立单个连接不会导致事务被提升。但是,每当您打开与SQL Server 2005数据库的第二个连接导致数据库登记时,System.Transactions基础结构都会检测到它是事务中的第二个持久资源,并将其升级为MSDTC事务。

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.

调用将事务“编组”到不同应用程序域或不同进程的请求。例如,跨应用程序域边界的事务对象的序列化。事务对象是按值封送的,这意味着任何尝试将其传递到应用程序域边界(即使在同一进程中)都会导致事务对象的序列化。您可以通过对以事务作为参数的远程方法进行调用来传递事务对象,也可以尝试访问远程事务服务组件。这会序列化事务对象并导致升级,就像跨应用程序域序列化事务一样。它正在分发,本地事务管理器已不再适用。

#2

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.

答案是它不能。显然正在发生的是促销活动正在进行中。 (我们意外地发现了这一点)我仍然不知道如何检测促销尝试是否正在发生。在检测到这一点时,这本来就非常有用。

#3

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个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?

文档sais“如果在数据库事务中打开与远程服务器的连接,则与远程服务器的连接将登记到分布式事务中,并且本地事务将自动提升为分布式事务。”但是,如果你真的只使用一个连接是一个非常奇怪的错误。您确定没有调用任何可以创建与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!

问题已经很久了,也许你已经知道了答案,可以在这里发布给其他人。谢谢!

#4

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.

很明显,当一些代码需要工作时,无论是否处于事务中,这都非常有效。

#5

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的构造函数的参数,它似乎都不会解决问题。我要解决这个问题的方法是对提交进行分块。希望这可以帮助