Entity Framework v1 - "Timeout expired" exceptions during peak hours on a high-traffic website

Question

I am getting these "System.Data.SqlClient.SqlException" exceptions during peak hours on a high-traffic website (ASP.NET).

The full exception:

SomeMethodName
- Ex.Type: System.Data.UpdateException - Ex.Source: System.Data.Entity 
- Ex.Message: An error occurred while updating the entries. See the inner exception for     details. 
- Ex.StackTrace: 
at System.Data.Mapping.Update.Internal.UpdateTranslator.Update(IEntityStateManager     stateManager, IEntityAdapter adapter) 
at System.Data.EntityClient.EntityAdapter.Update(IEntityStateManager entityCache) 
at System.Data.Objects.ObjectContext.SaveChanges(SaveOptions options) 
at SomeMethodName() in c:\SomeFileName.cs:line XXXX 
at SomeMethodName() - 
Ex.Type: System.Data.SqlClient.SqlException - Ex.InnerEx.Source: .Net SqlClient Data   Provider - 
Ex.InnerEx.Message: Timeout expired. The timeout period elapsed prior to completion of    the operation or the server is not responding. The statement has been terminated. -
Ex.InnerEx.StackTrace: at System.Data.SqlClient.SqlConnection.OnError(SqlException    exception, Boolean breakConnection) 
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean  breakConnection) 
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() 
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler,    SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject  stateObj) 
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() 
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior   runBehavior, String resetOptionsString) 
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior,   RunBehavior runBehavior, Boolean returnStream, Boolean async) 
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior,   RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) 
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior,   RunBehavior runBehavior, Boolean returnStream, String method) 
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) 
at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior) 
at System.Data.Mapping.Update.Internal.DynamicUpdateCommand.Execute(UpdateTranslator  translator, EntityConnection connection, Dictionary`2 identifierValues, List`1  generatedValues) 
at System.Data.Mapping.Update.Internal.UpdateTranslator.Update(IEntityStateManager stateManager, IEntityAdapter adapter) - 
Ex.InnerEx.Data[HelpLink.ProdName]: Microsoft SQL Server -   Ex.InnerEx.Data[HelpLink.ProdVer]: 10.00.2531
- Ex.InnerEx.Data[HelpLink.EvtSrc]: MSSQLServer - Ex.InnerEx.Data[HelpLink.EvtID]: -2 -   Ex.InnerEx.Data[HelpLink.BaseHelpUrl

I am using Entity Framework version 1 on a server that has installed .NET Framework 4.0. The database is SQL Server 2008 Std 64-bit. The code that throws the exception looks like this:

using (TransactionScope scope = new TransactionScope())
{
    //Code

    using (DataEntities context = new DataEntities())
    {
        context.AddToListingSet(newListing); //Exception is thrown here
        context.SaveChanges();
    }

    //More code

    scope.Complete();
}

As mentioned earlier, this mostly happen during peak hours. During non-peak hours, no exception is thrown.

Could it be that Entity Framework cannot handle all the data it is getting? Removing the Transaction code is not an option for me.

Answer 1

No, the problem is not that "Entity Framework cannot handle all the data it is getting." You are doing an INSERT, not a select. The problem is that the SQL query is taking longer to run than the timeout setting on the database or connection string, so it is being terminated.

Based on the code you show, it is unlikely that the INSERTstatement is at fault (unless you have triggers or other code you're not showing us). It is more likely that there is some other query running from a concurrent process which is locking the tables in use. But that's a guess on my part. You're going to have to do some SQL Server performance analysis to find the underlying issue. The code, in the exception here appear to be the symptom, not the cause.

Answer 2

I have found out why my code sometimes throws exceptions during peak-hours. Inside of my TransactionScope, I am doing some system IO (saving uploaded images to the file system) right before the call to

scope.Complete();

System IO is a relatively slow process, resulting in the locking of my Listing table for a longer period of time.

By moving the system IO outside of the TransactionScope, I don't get the exceptions anymore (in my case it is OK to have the system IO outside of the TransactionScope).