TransactionInDoubtException using System.Transactions on SQL Server 2005

Question

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

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.

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.

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

Thanks

Solution 1

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.

Solution 2

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.

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.

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

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):

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 nested connections 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"

And here is the MS official list of what causes escalation: 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.

Comments

[Mark]

1) I gave little snippet of code 2) I could use SqlTransaction but from an OO standpoint System.Transactions is a beautiful and neat abstraction 3) Everything indicates that only one connection is being used. I'm not sure how I could conclusively prove this 4) No third party componenets in this area of the code 5) Its not in a CLR 6) No linked servers

[Randy Levy]

You could have handled the DistributedTransactionStarted event.