Transaction timeout expired while using Linq2Sql DataContext.SubmitChanges()

Question

Hi guys, please help me resolve this problem:

There is an ambient MSMQ transaction. I'm trying to use new transaction for logging, but get next error while attempt to submit changes - "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding." Here is code:

public static void SaveTransaction(InfoToLog info)
 {
  using (TransactionScope scope =
   new TransactionScope(TransactionScopeOption.RequiresNew))
  {
   using (TransactionLogDataContext transactionDC =
      new TransactionLogDataContext())
   {
    transactionDC.MyInfo.InsertOnSubmit(info);

    transactionDC.SubmitChanges();
   }

   scope.Complete();
  }
 }

Please help me. Thx.

Answer 1

You could consider increasing the timeout or eliminating it all together.

Something like:

using(TransactionLogDataContext transactionDC = new TransactionLogDataContext())
{
    transactionDC.CommandTimeout = 0;  // No timeout.
}

Be careful

Answer 2

You said:

thank you. but this solution makes new question - if transaction scope was changed why submit operation becomes so time consuming? Database and application are on the same machine

That is because you are creating new DataContext right there:

TransactionLogDataContext transactionDC = new TransactionLogDataContext()) 

With new data context ADO.NET opens up new connection (even if connection strings are the same, unless you do some clever connection pooling). Within transaction context when you try to work with more than 1 connection instances (which you just did) ADO.NET automatically promotes transaction to a distributed transaction and will try to enlist it into MSDTC. Enlisting very first transaction per connection into MSDTC will take time (for me it takes 30+ seconds), consecutive transactions will be fast, however (in my case 60ms). Take a look at this http://support.microsoft.com/Default.aspx?id=922430

What you can do is reuse transaction and connection string (if possible) when you create new DataContext.

 TransactionLogDataContext tempDataContext = 
      new TransactionLogDataContext(ExistingDataContext.Transaction.Connection);
 tempDataContext.Transaction = ExistingDataContext.Transaction;

Where ExistingDataContext is the one which started ambient transaction.

Or attemp to speed up your MS DTC.

Also do use SQL Profiler suggested by billb and look for SessionId between different commands (save and savelog in your case). If SessionId changes, you are in fact using 2 different connections and in that case will have to reuse transaction (if you don't want it to be promoted to MS DTC).