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

c# entity-framework sql

Question

This inquiry concerns the finest method for employing Microsoft Entity Framework to handle many inserts or changes. The issue is that we created a time-consuming application that reads thousands of records from the database and then individually modifies each record's single field. We discovered, to to our surprise, that each of these changed records remained locked for as long as the ObjectContext was not discarded. The following is some pseudocode that serves just as an example.

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 issue is that we must do several updates without taking into account transactions. When we realized that calling context, we were shocked. The lock on the records is really created by SaveChanges(), and it is not released until the ObjectContext is destroyed. As this is a system with significant traffic and the ability to operate for hours, we particularly don't want to lock the database's records.

What is the best approach to do several changes in Microsoft Entity Framework 4 WITHOUT putting them all in a single lengthy transaction that locks the database? We're hoping the solution doesn't include creating a fresh ObjectContext for each and every change.

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

Accepted Answer

By default, read-committed transaction isolation level is used by Entity Framework on top of SQL Server, and transactions are committed at theSaveChanges If you notice any additional behavior, it must be consistent with the rest of your code (are you utilizingTransactionScope ( — you failed to include it in your code) or there must be a glitch.

Moreover, your strategy is flawed. You should load each record independently if you wish to store each one separately. EF is unquestionably a poor option for these purposes. even with only a singleSaveChange It will still just make one roundtrip to the database to update each of your records.

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

Popular Answer

That kind of lock is not produced by Entity Framework. EF does not allow pessimistic locking; it only supports optimistic concurrency.

I believe that your SQL Server settings is to blame for the locking you encounter. The locks following each query could occur if your server's Transaction Isolation Level is configured to REPEATABLE READ. But I'm not entirely clear which setup option would be the issue. here are further details.

Edit:

Zzz-23-Zzz is another insightful paper about transactions and transaction isolation in EF. It is extremely advised to establish the isolation level explicitly at all times. Adapted from the text:

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