When doing many updates in Entity Framework 4, how do you avoid locking the database?

c# entity-framework sql

Question

This question is in regards to the best practice for handling many inserts or updates using Microsoft Entity Framework. The problem is that we wrote a long-running program which pulls back thousands of records from the database, and then updates a single field on each of those records, one-by-one. Much to our dismay, we realized that each of these records that were updated were locked for the duration of the time in which the ObjectContext was not disposed. Below is some pseudocode (doesn't actually run) to illustrate:

using(ObjectContext context = new ObjectContext())
{

    var myRecords = context.CreateObjectSet<MyType>().AsQueryable();

    foreach(var record in myRecords)
    {
       record.MyField = "updated!";
       context.SaveChanges();

       //--do something really slow like call an external web service
   }
}

The problem is that we need to do many updates without any regard for transactions. We were surprised to realize that calling context.SaveChanges() actually creates the lock on the records and does not release it until the ObjectContext is disposed. We especially do NOT want to lock the records in the database as this is a high-traffic system and the program could potentially run for hours.

So the question is: what is the optimal way to do many updates in Microsoft Entity Framework 4 WITHOUT doing them all on one long transaction that locks the DB? We are hoping that the answer is not to create a new ObjectContext for every single update...

1
15
3/16/2011 7:44:09 PM

Accepted Answer

Entity framework on top of SQL server by default uses read committed transaction isolation level and transaction is committed at the end of SaveChanges. If you suspect other behavior it must be by the rest of your code (are you using TransactionScope? - you didn't show it in your code) or it must be some bug.

Also your approach is wrong. If you want to save each record separately you should also load each record separately. EF is definitely bad choice for this type of applications. Even if you use only single SaveChange for updating all your records it will still make single roundtrip to database for each update.

7
3/16/2011 8:34:10 PM

Popular Answer

Those locks are not created by Entity Framework. EF only supports optimistic concurrency, pessimistic locking is not supported with EF.

I think the locking you experience is a result of your SQL Server configuration. Perhaps if your Transaction Isolation Level on the server is set to REPEATABLE READ this might cause the locks after each query. But I am not sure which configuration setting could be exactly the problem. More details are here.

Edit:

Another helpful article about transactions and transaction isolation in EF is here. It strongly recommends to always set the isolation level explicitely. Quote from the article:

If you don't take control of [the isolation level], you have no idea in which transaction isolation level your queries will be running. After all, you don't know where the connection that you got from the pool has been [...] You simply inherit the last used isolation level on the connection, so you have no idea which type of locks are taken (or worse: ignored) by your queries and for how long these locks will be held. On a busy database, this will definitely lead to random errors, time-outs and deadlocks.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow