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

Answer 1

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!

Answer 2

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.

Answer 3

Have you tried asking on serverfault? Or even putting a surport request in with Microsoft.

Please post the answer when you find it...

Answer 4

Наve you tried to raise Connection Timeout parameter in connection string? I had the same problem with long-running transaction on high-load and it helped for me. I set Connection Timeout to 900 seconds. By default it is 15 seconds.

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

Answer 6

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.