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

Question

这篇文章的基本问题是:“为什么未经提倡的LTM交易会受到质疑？”

我收到System.Transactions.TransactionInDoubtException，我无法解释原因。不幸的是，我无法重现此问题，但根据跟踪文件确实会发生。我正在使用SQL 2005，连接到一个数据库并使用一个SQLConnection，所以我不希望升级。错误消息指示超时。但是，有时我会收到超时消息，但异常(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.   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.

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

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();
}

谢谢

Answer 1

当前公认的答案是，毫无疑问，LMT(非MSDTC)交易不会引起任何疑问。经过对类似问题的大量研究，我发现这是不正确的。

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

从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

另外，这里还有一些我发现导致System.Transaction提升/升级为MSDTC事务的事物的实际示例(这与OP没有直接关系，但我发现它非常有用。在VS 2013中进行了测试，SQL Server 2008 R2 ，.NET 4.5(除非另有说明):

(此命令特定于SQL Server 2005或兼容性级别<100)-在TransactionScope中的任意点多次调用Connection.Open()。这还包括在SAME连接实例上调用.Open()、. Close()、. Open()。

在TransactionScope

中打开 嵌套的连接

使用的多个连接不使用连接池，即使它们没有嵌套并连接到同一数据库。

涉及链接服务器的查询

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

看来，如果使用连接池，并且由于某种原因在连接“2到N”中由于某种原因而无法使用Connection1中使用的相同物理连接，则将提升整个事务处理(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事务的该连接池的下一个请求将返回相同的连接(如果它如果发出了这样的请求，并且没有可用的池化连接，则从池的非事务部分中提取一个连接，并征用“

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

事务中至少征集了一种不支持单阶段通知的持久资源。

事务中至少征募了两个支持单阶段通知的持久资源。例如，注册与SQL Server 2005的单个连接不会导致事务升级。但是，每当您打开与SQL Server 2005数据库的第二个连接而导致该数据库加入时，System.Transactions基础结构都会检测到它是事务中的第二个持久资源，并将其升级为MSDTC事务。

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