CODEWITHSUNDEEP
c#sql-serverentity-frameworkparallel-processingparallel.foreach
Matt Sharpe
Matt Sharpe

Reputation: 3783

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

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.

Upvotes: 9

Views: 6307

Answers (3)

bmdixon
bmdixon

Reputation: 352

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

Upvotes: 6

usr
usr

Reputation: 171178

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.

Upvotes: 1

Matt Randle
Matt Randle

Reputation: 1893

You are running into the maximum number of locks allowed by sql server - which by default is set automatically and governed by available memory.

You can

  1. Set it manually - I forget exactly how but google is your friend.
  2. Add more memory to your sql server
  3. Commit your transactions more frequently.

Upvotes: 0

Related Questions