I had the following code that is deleting items in a table that were created more than 30 days ago (using an Entity Framework Core 6 DbContext):
var expireBefore = DateTime.Now.AddDays(-30);
db.MyTable.RemoveRange(db.MyTable.Where(t => t.CreatedDate <= expireBefore));
await db.SaveChangesAsync();
This code was executing in multiple places at the same time, and it would occasionally throw a DbUpdateConcurrencyException:
The database operation was expected to affect 1 row(s), but actually affected 0 row(s); data may have been modified or deleted since entities were loaded
I believe this happened due to a race condition where two places both find the old entities, but only one is able to delete them.
To try and fix the issue I put the code into a transaction, I thought this would prevent the race condition, however the same exception is still occasionally thrown. Why is a transaction here not preventing this issue?
var expireBefore = DateTime.Now.AddDays(-30);
var executionStrategy = db.Database.CreateExecutionStrategy();
await executionStrategy.ExecuteAsync(async () =>
{
await using var transaction = await db.Database.BeginTransactionAsync(IsolationLevel.Serializable);
db.MyTable.RemoveRange(db.MyTable.Where(t => t.CreatedDate <= expireBefore));
await db.SaveChangesAsync();
await transaction.CommitAsync();
});
I had the following code that is deleting items in a table that were created more than 30 days ago (using an Entity Framework Core 6 DbContext):
var expireBefore = DateTime.Now.AddDays(-30);
db.MyTable.RemoveRange(db.MyTable.Where(t => t.CreatedDate <= expireBefore));
await db.SaveChangesAsync();
This code was executing in multiple places at the same time, and it would occasionally throw a DbUpdateConcurrencyException:
The database operation was expected to affect 1 row(s), but actually affected 0 row(s); data may have been modified or deleted since entities were loaded
I believe this happened due to a race condition where two places both find the old entities, but only one is able to delete them.
To try and fix the issue I put the code into a transaction, I thought this would prevent the race condition, however the same exception is still occasionally thrown. Why is a transaction here not preventing this issue?
var expireBefore = DateTime.Now.AddDays(-30);
var executionStrategy = db.Database.CreateExecutionStrategy();
await executionStrategy.ExecuteAsync(async () =>
{
await using var transaction = await db.Database.BeginTransactionAsync(IsolationLevel.Serializable);
db.MyTable.RemoveRange(db.MyTable.Where(t => t.CreatedDate <= expireBefore));
await db.SaveChangesAsync();
await transaction.CommitAsync();
});
The problem seems to be that you are using .Where to actually read the entities in, then mark them as removed and save the context. This is both inefficient and does not provide proper locking.
In the new versions of EF Core, you can just use an ExecuteDeleteAsync. This executes directly on the database without loading the data into the context, and should do the locking correctly.
var expireBefore = DateTime.Now.AddDays(-30);
await db.MyTable
.Where(t => t.CreatedDate <= expireBefore));
.ExecuteDeleteAsync();
If you really, really want to load the entities into the context, then firstly you need a WITH (UPDLOCK) or FOR UPDATE clause to get the correct locking (otherwise the shared lock won't stop someone else reading the data). And secondly you want to do this async.
You also need a transaction, but you don't need an execution strategy.
var expireBefore = DateTime.Now.AddDays(-30);
var query = db.MyTable
.FromSql("SELECT * FROM dbo.MyTable WITH (UPDLOCK)")
.Where(t => t.CreatedDate <= expireBefore);
await using var transaction = await db.Database.BeginTransactionAsync(IsolationLevel.Serializable);
db.MyTable.RemoveRange(await query);
await db.SaveChangesAsync();
await transaction.CommitAsync();