Fixed: False "DbUpdateConcurrencyException" on Stock with a Concurrency Token in EF Core
I had a Version concurrency token on products, so I tried it against overselling. It worked, and it also turned away customers: with 100 in stock and 5 simultaneous orders, 3 got 409 Conflict. They weren't conflicting over stock at all. They'd just happened to read the same version.
What I Was Doing
My .NET C# Web API uses EF Core 10.0.14. Products have an int Version marked as a concurrency token, and selling one looked like this:
g.MapPost("/sell-version/{id:int}", async (int id, InventoryDbContext db, CancellationToken cancellationToken) =>
{
var product = await db.Products.FirstAsync(p => p.Id == id, cancellationToken);
product.QuantityOnHand -= 1;
product.Version += 1;
try
{
await db.SaveChangesAsync(cancellationToken);
}
catch (DbUpdateConcurrencyException)
{
return Results.Problem(title: "Someone else changed this product.", statusCode: 409);
}
return Results.Ok(new { product.QuantityOnHand });
});
g.MapPost("/sell-version-raw/{id:int}", async (int id, InventoryDbContext db, CancellationToken cancellationToken) =>
{
var product = await db.Products.FirstAsync(p => p.Id == id, cancellationToken);
product.QuantityOnHand -= 1;
product.Version += 1;
await db.SaveChangesAsync(cancellationToken);
return Results.Ok(new { product.QuantityOnHand });
});
The product had 100 on hand, and 5 sales came in at once:
+----------------+---------+
| QuantityOnHand | Version |
+----------------+---------+
| 100 | 0 |
+----------------+---------+
What I Expected
5 sales, and 95 left. The token is there to catch people editing the same product.
What I Got
2 200
3 409
+----------------+---------+
| QuantityOnHand | Version |
+----------------+---------+
| 98 | 2 |
+----------------+---------+
The 409 is my catch. Without it, the same requests answer 500, and the log has the exception's own message:
Microsoft.EntityFrameworkCore.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. See https://go.microsoft.com/fwlink/?LinkId=527962 for information on understanding and handling optimist
3 sales refused, with 98 in stock.
What I Checked
What a conflict means here. The EF Core page explains that with a token, EF Core adds it to the UPDATE's WHERE. If the row's value has changed since it was read, no row matches, and it throws. So any two requests that read the same version conflict, whatever they meant to do.
Why I saw 2 sales and not 1. The token only conflicts for requests whose reads overlap, so the count depends on timing.
Why It Happens
A concurrency token asks "has anything changed since I read this?" Selling needs a narrower question: "is there enough stock?" For a person editing a product's name, the first question is the right one. For 5 buyers on a product with 100 in stock, the answer to the first is yes, and the answer to the second is also yes.
The Fix
Leave the token where it belongs, on edits. Change stock with a conditional UPDATE, which doesn't touch the token:
g.MapPost("/sell-conditional/{id:int}", async (int id, InventoryDbContext db, CancellationToken cancellationToken) =>
{
var taken = await db.Products
.Where(p => p.Id == id && p.QuantityOnHand >= 1)
.ExecuteUpdateAsync(s => s.SetProperty(p => p.QuantityOnHand, p => p.QuantityOnHand - 1), cancellationToken);
return taken == 1
? Results.Ok()
: Results.Problem(title: "There isn't enough stock.", statusCode: 409);
});
How to Prevent Overselling with a Conditional UPDATE in EF Core has the whole pattern.
After the Fix
5 200
+----------------+---------+
| QuantityOnHand | Version |
+----------------+---------+
| 93 | 2 |
+----------------+---------+
5 sales, from 98 to 93, and Version hasn't moved, so nobody editing the product gets a false conflict either.
Common Mistakes
| Mistake | What happens | Do this instead |
|---|---|---|
Retrying on DbUpdateConcurrencyException in a loop | It works, slowly, and busy products suffer most | Use a conditional UPDATE |
| Dropping the token altogether | Two edits overwrite each other | Keep it for edits |
Bumping Version on every stock change | Editors get a conflict on every sale | Leave it out of stock changes |
Troubleshooting
| Symptom | Likely cause | Fix |
|---|---|---|
409 on a popular product only | More overlapping reads | This fix |
A 500 for this exception on a real edit | Nothing catches it | Catch DbUpdateConcurrencyException, and answer 409 |
Stock goes negative with a conditional UPDATE | The WHERE is missing the check | Add QuantityOnHand >= quantity |
Conclusion
A concurrency token treats any overlap as a conflict, so it refuses sales that were fine. Use it for edits by people, and use a conditional UPDATE for quantities.
Additional Resources
- Handling Concurrency Conflicts — tokens in EF Core.
- ExecuteUpdate and ExecuteDelete — set-based updates.
