Multiple dbcontexts in parallel threads, EntityException "Rerun your statement when there are fewer active users"

Question

I am using Parallel.ForEach to do work on multiple threads, using a new EF5 DbContext for each iteration, all wrapped within a TransactionScope, as follows:

using (var transaction = new TransactionScope())
{
    int[] supplierIds;

    using (var appContext = new AppContext())
    {
        supplierIds = appContext.Suppliers.Select(s => s.Id).ToArray();
    }

    Parallel.ForEach(
        supplierIds,
        supplierId =>
    {
        using (var appContext = new AppContext())
        {
            Do some work...

            appContext.SaveChanges();                
        }
    });

    transaction.Complete();
}

After running for a few minutes it is throwing an EntityException "The underlying provider failed on Open" with the following inner detail:

"The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users. Ask the database administrator to check the lock and memory configuration for this instance, or to check for long-running transactions."

Does anyone know what's causing this or how it can be prevented? Thanks.

Answer 1

You could also try setting the maximum number of concurrent tasks in the Parallel.ForEach() method using new ParallelOptions { MaxDegreeOfParallelism = 8 } (replace 8 with the whatever you want to limit it to.

See MSDN for more details

Answer 2

You should also find out why your app is taking such huge amounts of locks? You have wrapped a TransactionScope around multiple db connections. This probably causes a distributed transaction which might have to do with it. It certainly causes locks to never be released until the very end. Change that.

You can only turn up locking limits so far. It does not scale to arbitrary amounts of supplier ids. You need to find the cause for the locks, not mitigate the symptoms.