Database timeout when perfoming first update operation in ADO.NET Entity Framework

Question

We are creating an application using WCF services that access database using Entity Framework. We are using SQL Server 2005, .Net 4.0, Entity Framework 4.0 and C#. When the WCF services are deployed, and the application starts, first database operations are always SELECT to read some data and then, upon user interaction with the application some database updates could occur. The problem observed is with the first database UPDATE operation performed after the services are deployed, that normally fails with the error:

An error occurred while updating the entries.
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

This error raises in the SaveChanges() method and normally this method is enclosed in a transaction like:

// TransactionScopeOption.RequiresNew, IsolationLevel.ReadCommitted, Timeout = TimeSpan.FromMinutes(1)
using (TransactionScope scope = TransactionScopeUtil.CreateTransactionScope())
{
  using (var dbcontext = new DBCtx("connectionstring"))
  {
    dbcontext.Connection.Open();

    // Read and update database.

    dbcontext.SaveChanges();

    dbcontext.Connection.Close();
  }
}
scope.Complete();

This very first database update operation normally fails and then never fail again until services are re-deployed.

Could be a problem with the database connection pool in the cold start?

We are not comfortable with the solution/workaround of increasing the connection/commmand timeout as could mask performance problems in the future and database updates are normally not "big".

Is it very first update or very first database access? Also it looks like your error is not from transaction but from call to service. — Ladislav Mrnka, Nov 24 '11 at 11:06
Is the very first database update, as the client application that call services when starts, retrieves some data to populate lists, combos... and the the user can update information in the database, raising this error the first time. — David Oliván, Nov 25 '11 at 11:00

score 0 · Accepted Answer · edited May 23 '17 at 12:03

Try to use TransactionManager.MaximumTimeout may be you have already done this. so if it is not the issue then the following stuff:

EF desinger connection exists as 'Default Command Timeout' so set the command timeout with some value by using dbcontext. it will set the underlying command timeout for the context commands.

context.CommandTimeout = 180;

check this stack overflow question for more details

you should use scope.Complete(); in the using statement - MSDN - Transaction Scope implementation

using(TransactionScope scope = new TransactionScope())
     {
          /* Perform transactional work here */
          scope.Complete();
     }

As this is not easy to reproduce, I changed Context command timeout to 1 minute and let transaction timeout to 1 minute as currently and will try. Hope will work:) — David Oliván, Dec 14 '11 at 22:23

Database timeout when perfoming first update operation in ADO.NET Entity Framework

1 Answers1