locked
TransactionInDoubtException using System.Transactions on SQL Server 2005 RRS feed

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

    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. It seems to have been but it doens't say in what version of the .NET framework the issue has been resolved (i.e. is there a hotfix, Service pack or is it just fixed in .NET 3.3) 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
    • Edited by markoueis Wednesday, September 16, 2009 3:02 PM More formatting
    Wednesday, September 16, 2009 2:41 PM

Answers

All replies

  • Can anyone help me with this? Is there a better place where I can post this question?

    Thursday, September 17, 2009 7:08 PM
  • Couple of questions.

    Is it possible to tell us, after how long do you get the exception, counting from the moment you start the transaction? Can you give us some more information about your application topology?  Is this work being done cross machine?  What are the OS Platform of machine(s) involved?  Is the SQL Server 2005 database the only resource involved in the Transaction? 

    Did you make sure that the transaction is not being promoted to MSDTC? A code snippet for the issue you are seeing would be useful for us to help as well. Can you also check, when you are closing the connection that is used for this.

    And regarding the issue mentioned , it has been fixed in .NET 4.0 Beta1. I confirmed this in .Net 4.0 Beta1 build. But cloning the transaction should not cause this.

    Thanks,
    Srini

    Monday, September 21, 2009 5:12 AM
    Moderator
  • Hello,

    I am receiving the exact same error message, however, in my situation I am making many calls to the one database (and opening a new connection for each call).

    (On some environments I experience a timeout error opening the first connection after the transaction has committed as per the entry here http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/5a5550b0-88d9-4e7f-a137-7328b44c8262 )

    In the instance where I receive the TransactionInDoubt exception if I open up the MSDTC log it appears that the transaction has actually committed, albeit after 20 seconds:
    pid=1008       ;tid=1052       ;time=10/21/2009-02:17:37.121   ;seq=16772      ;eventid=RECEIVED_COMMIT_REQUEST_FROM_BEGINNER    ;tx_guid=09bcbd34-c22e-4638-99ab-2f8e0673e0da     ;"received request to commit the transaction from beginner"
    pid=1008       ;tid=1776       ;time=10/21/2009-02:17:57.360   ;seq=16773      ;eventid=TRANSACTION_COMMITTED                    ;tx_guid=09bcbd34-c22e-4638-99ab-2f8e0673e0da     ;"transaction has got committed"

    My transaction lasts for about 16 minutes in the instance where this error occurs. It is a batch process that performs actions on ~25,000 records (I know it is slow).

    There are no entries in the App or DB event viewers to give me any information on the problem.

    In this instance I have a Win 2003 Server 2003 R2 Standard Edition SP2 OS with SQL Server 2005 SP3 installed locally as the default instance.

    Can anyone provide any insight into what I might be doing wrong?

    Thanks,
    Sean.
    Wednesday, October 21, 2009 12:46 AM
  • I have noticed that some of the data is actually committed as well... How can this possibly be?
    If an exception occurs committing a transaction then shouldn't the transaction roll back?
    This sounds like a defect in the .NET framework to me...

    Any thoughts? Solutions?
    Wednesday, October 21, 2009 9:23 PM
  • Has this been placed in the too hard basket?
    Wednesday, October 28, 2009 10:08 PM
  • Sorry, for the delayed response.

    Is it possible to post full transaction trace. Can you follow the link below to enable System.Transactions diagnostic tracing. Then we can see, who promotes the transaction and why.

    http://msdn.microsoft.com/en-us/library/ms229979.aspx

    Thanks,

    Srini

    Friday, March 26, 2010 1:34 AM
    Moderator
  • Marking as answered due to lack of activity.  If the issue is still active please reactivate with more data.  Thanks.

    JVE

    Friday, April 2, 2010 2:54 PM
    Moderator
  •  

    The timeout period elapsed prior to completion of the operation or the server is not responding.

     

    Tuesday, January 24, 2012 5:01 PM
  • The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled the distributed transaction.
    Tuesday, January 24, 2012 5:02 PM