EF6 executing a long running stored procedure gets timeout errors

Question

I recently upgraded my project from EF5 to EF6. In this project I have an Azure Worker Role that runs periodically, and kicks off a stored procedure on SQL Azure that updates a bunch of database information, and takes on average 1.5 hours to execute. When it is done, the worker role performs additional tasks with the return result of the stored procedure.

This used to work flawlessly in EF5, but in EF6 it fails every time with one of these errors:

Error A transport-level error has occurred when receiving results from the server. (provider: Session Provider, error: 19 - Physical connection is not usable)

Error The session has been terminated because it has acquired too many locks. Try reading or modifying fewer rows in a single transaction. A severe error occurred on the current command. The results, if any, should be discarded.

I have tried the following things to fix the error:

Verified that all stored procedure reads have the WITH (NOLOCK) modifier
Increased the timeout on the entity framework context to 5 hours
Removed the new SqlAzureExecutionStrategy that was put in place and brought it back down to use DefaultExecutionStrategy
Removed any transactions that were occurring in the stored procedure
Assure that this step in the worker role is running in it's own context

Example of the code:

using (var dbContext = new EFEntityContext())
{
    // set the timeout to 5 hours
    var objectContext = (dbContext as IObjectContextAdapter).ObjectContext;
    objectContext.CommandTimeout = 18000; // 5 hours

    // update all active curriculums
    var result = dbContext.usp_MyLongRunningProd();

    // log the results of the operation
    Trace.TraceInformation(result);
}

Also, the stored procedures reads a large table into a cursor, loops through it, and perform as an analysis and data change based on each items. I don't need the cursor to be in a transaction of any kind, and am not using one that I know of, unless EF is making one and that is the issue.

CodeGrue CodeGrue · Accepted Answer · 2013-11-25T15:50:54

I believe this article may have the answer but I won't know until my job runs tonight. I will update this answer if it succeeds:

http://entityframework.codeplex.com/discussions/454994

Which says:

As part of the Connection Resiliency work we changed the default behavior of certain APIs that can produce side effects to start using transactions. We also introduced a way of opting out from this new behavior for the specific cases in which transactions are not supported. You can for instance, in new overloads of Database.ExecuteSqlCommand pass a new enum parameter that disables transactions.

EF6 executing a long running stored procedure gets timeout errors

1 Answers