CarlJ February 2016

Concurrency exception in Entity Framework when loading and deleting objects

I've got an EF class mapped to a SQL Server table. I have the following very simple Entity Framework code (using the ASP.NET Boilerplate repository wrapper over the EF DBContext):

var objectsToDelete = repository.GetAll().Where(r => r.parentId == param1).ToList();
foreach(var obj in objectsToDelete) {
    repository.Delete(obj); //doesn't actually update the database yet.
}
...
repository.SaveChanges();

Note that the GetAll call will immediately read from the database.

Surprisingly to me, this is a real concurrency headache when multiple web service calls try to run this code simultaneously with the same param1 value (imagine that the user performs the exact same action twice in quick succession). To cut a long story short, a context switch can occur between getting the IDs and calling SaveChanges. So this is what happens (you can change this order around quite a bit without affecting the result):

  1. Thread #1 starts a SQL transaction and starts running the above code.
  2. Thread #2 starts a SQL transaction and starts running the above code (using a different DBContext).
  3. Thread #1 gets the objects to delete.
  4. Context switch.
  5. Thread #2 gets the objects to delete - they will have the same IDs that #1 read.
  6. Thread #2 marks the objects as deleted and calls SaveChanges, which issues the SQL DELETE statements.
  7. Thread #2 commits its transaction and it's done.
  8. Thread #1, using its (now stale) list, marks those same objects as deleted and calls SaveChanges, which issues the SQL DELETE statements.
  9. Exception! Entity throws this:

Store update, insert, or delete statement affected an unexpected number of rows (0).

Now, even if I turn the transaction isolation level all the way up to Serializable, this doesn't help. "Serializable" specifies that statements cannot read data that has been modified but

Answers


Krishna shidnekoppa February 2016

var objectsToDelete = repository.GetAll().Where(r => r.parentId == param1).ToList();
if(objectsToDelete.Count!=0)
{
    repository.RemoveRange(objectsToDelete ); 
}
...
repository.SaveChanges();

Post Status

Asked in February 2016
Viewed 1,887 times
Voted 14
Answered 1 times

Search




Leave an answer