locked
System.Transactions throws TransactionInDoubt exception RRS feed

  • Question

  • Hi,

    We've migrated to System.Transactions not so long ago, and in some processes, heavy multithreaded ones we get a lot of TransactionInDoubt exception containing Timeout exception inside (see quoted exception below). the timeout part is extremely weird since we have a very long timeout defined both for the command and the transaction.

    we open several connections (not too many) and perform several queries throughout the transaction, but it's all under the same transaction.

    I've seen some posts regarding the exact same problem (like http://social.msdn.microsoft.com/Forums/en-US/d1c6b247-5460-4316-b4e6-0fafd7a46385/transactionindoubtexception-using-systemtransactions-on-sql-server-2005)

    but none was answered... 

    what IS IT??? how can I stop this from happening??

    this is the 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()
       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.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.SqlDelegatedTransaction.SinglePhaseCommit(SinglePhaseEnlistment enlistment)


    • Moved by CoolDadTx Monday, January 27, 2014 4:02 PM ADO.NET related
    Monday, January 27, 2014 9:30 AM

All replies

  • Hello EyalEEE,

    Since we do not know the exact program, we are not clear why it throws such an error. Could you please provide a simple demo which could reproduce it?

    And for this error message "Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding".

    If you are connecting a mirrored database, then this KB article will be help:

    http://support.microsoft.com/kb/2555235

    If not, please check if you have any scenario of below:

    1. There's a deadlock somewhere

    2. The database's statistics and/or query plan cache are incorrect

    3. The query is too complex and needs to be tuned  

    For fix it, please check link below for details:

    http://stackoverflow.com/questions/8602395/timeout-expired-the-timeout-period-elapsed-prior-to-completion-of-the-operation

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, January 28, 2014 6:05 AM
  • You wrote multithreading.

    You know that the transaction locks the part (and that is a broad part) where the transaction is active. In that time the server is not responding for that part. A timeout is then normal and is hard to tune because it can be in endless situations different.

     Be aware a transaction is often misused. It makes for instance seldom sense to do a Select with a transaction or it should be in very special cases. But then you should also not use multithreading.


    Success
    Cor




    Tuesday, January 28, 2014 9:39 AM
  • Hi there Fred,

    first of all I can't really provide a simple demo for reproducing since I'm not sure as to how to reproduce. we have a very elaborated system, it's not you simple transactional app. also the heavy multithreading seem to be a factor. we have similar jobs which are single threaded and it does not appear there.

    regarding the mirrored DB, I'm not sure as to what that is exactly, but for what I've read in the KB you provided, it seems like a timeout in the connect (according to the stack trace) and in our stack trance the start of it is SinglePhaseCommit

    also for the rest of the questions:

    1. no deadlocks were spotted (we have traces on the DB), and deadlocks usually a deadlock exception.

    2. there's a chance that the statistics are incorrect, not very likely since they are updated regulary.

    3. could be the query is too heavy, but which query??????? again, there are two things, timeout and transactionInDoubt. how are the two connected???

     

    Tuesday, January 28, 2014 3:29 PM
  • Hi Cor I would happily deal with a timeout, the question is not the timeout, but what is the TransactionInDoubt exception? and why is it saying something about a timeout? timeout where? on what statement?? the root of the call according to the stack trace is the "singlePhaseCommit" indicating we are in the commit process, probably from the scope.Complete() call. where is the timeout? what if we had 20 queries inside that System.Transaction?? which is the one timed out??? why am I only hearing of it in the commit and in the form of TransactionInDoubt???
    Tuesday, January 28, 2014 3:32 PM
  • Yes that is why I would avoid the multi threading with an SQL server and datasets (or any other kind of ORM), what do you think to win?

    The server does not run faster and can only handle one reader (which is used by the dataadapter) at a time. 


    Success
    Cor



    • Edited by Cor Ligthert Thursday, January 30, 2014 12:23 PM
    Thursday, January 30, 2014 12:22 PM
  • I don't know if this helps or not, but do any of your Transactions get elevated to Distributed Transactions? If so, then your problem may have something to do with DTC not being configured properly ... although, I would think that you'd get an error that indicates an issue with DTC.

    I don't know, but maybe it's food for thought ...


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Friday, January 31, 2014 2:13 AM
  • Hi there Bonnie. Yes, the DTC is a candidate and I too suspect the scenario of elevation to DTC. however - I have no CLUE as to what DTC setting is misconfigured, if this is the case... this might be a case of too many connection requests against low number of available connections in the DTC pool or something... Maybe I've just invented something I don't know...

    Anyway this bug still exists and it's killing me. more suggestions are welcome :)

    Wednesday, February 12, 2014 11:34 AM
  • Hi Cor, both our servers and software infrastructure (N-Tier, one Backend Server and several FrontEnd Servers) and the SQL Server both greatly support MultiThreading and the handling of multiple requests simultaneously so I don't really understand your statement.

    High end systems are excpected to handle multiple requests. it's been so for the past decade or so.

    Wednesday, February 12, 2014 11:35 AM
  • It seems to me that you have created a program without knowing if the bunch what you did make really all work.

    Why not create a small program and test first all what you did step by step. 

    I think that nobody can help you. Your attitude in this is that you want it to go like you think it must go.


    Success
    Cor

    Friday, February 14, 2014 12:40 PM