Grant Hughes Grant Hughes - 1 year ago 86
SQL Question

How To Avoid Locking Database In Entity Framework 4 When Doing Many Updates

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!";

//--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...

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download