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

Meatball

No the database is not mirrored. Nor does this only happen in one or two environments but in douzens of them – Meatball

Have you tried asking on serverfault? Or even putting a surport request in with Microsoft. Please post the answer when you find it... – Intervene

Answer 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.

Meatball

You could have handled the DistributedTransactionStarted event. – Corking

Answer 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.

Bandmaster

Answer 3

Beats the heck out of me.

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

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.

Torpid

Answer 4

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.

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

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?

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

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

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

Teacart

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 – Meatball

Answer 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

Howse