How to Change a Status Only Once with a Conditional Update in EF Core
· 5 min read
Two people cancel the same order at once. Each one reads Placed, and each one puts the stock back, so you've restocked twice. Put the status in the UPDATE's WHERE, and only one of them can change it. The other one finds out and stops.
Prerequisites
- A .NET C# project using EF Core. I used EF Core 10.0.14 and MySQL 9.7.4.
- A transaction with retries. How to Use a Transaction with a Retrying Execution Strategy in EF Core.
Step 1: Change the Status in One Statement
When the status is all that changes, you don't need a transaction:
src/Dewiride.Inventory.Api/Features/Orders/OrderEndpoints.cs (excerpt)
// Only a placed order can change, so two requests can't both win.
var changed = await db.Orders
.Where(o => o.Id == id && o.Status == OrderStatus.Placed)
.ExecuteUpdateAsync(
setters => setters
.SetProperty(o => o.Status, OrderStatus.Fulfilled)
.SetProperty(o => o.FulfilledAtUtc, now),
cancellationToken);
if (changed > 0)
{
return TypedResults.NoContent();
}
changed is the row count. If it's 0, the order wasn't Placed, or it doesn't exist.
Step 2: Do More When It Works
Cancelling also puts the stock back, so the status change comes first, inside a transaction, and decides everything else:
src/Dewiride.Inventory.Api/Features/Orders/OrderEndpoints.cs (excerpt)
// The first request changes the status and keeps the row's lock until it commits.
// A second request waits, then finds the order is no longer placed.
var changed = await db.Orders
.Where(o => o.Id == id && o.Status == OrderStatus.Placed)
.ExecuteUpdateAsync(
setters => setters
.SetProperty(o => o.Status, OrderStatus.Cancelled)
.SetProperty(o => o.CancelledAtUtc, now),
ct);
if (changed == 0)
{
return await ExplainStatusAsync(db, id, "cancelled", ct);
}
var lines = await db.OrderLines
.Where(l => l.OrderId == id)
.OrderBy(l => l.ProductId)
.Select(l => new { l.ProductId, l.Quantity })
.ToListAsync(ct);
foreach (var line in lines)
{
await db.Products
.Where(p => p.Id == line.ProductId)
.ExecuteUpdateAsync(
setters => setters
.SetProperty(p => p.QuantityOnHand, p => p.QuantityOnHand + line.Quantity)
.SetProperty(p => p.UpdatedAtUtc, now),
ct);
db.StockMovements.Add(new StockMovement
{
ProductId = line.ProductId,
OrderId = id,
Type = StockMovementType.SaleCancelled,
QuantityChange = line.Quantity,
OccurredAtUtc = now,
});
}
The row lock from that first UPDATE lasts until the commit, so a second request waits there, and then finds that the order is no longer Placed.
Step 3: Explain the Refusal
0 rows doesn't say why, so look it up:
src/Dewiride.Inventory.Api/Features/Orders/OrderEndpoints.cs (excerpt)
private static async Task<Results<NoContent, NotFound, ProblemHttpResult>> ExplainStatusAsync(
InventoryDbContext db,
int id,
string wanted,
CancellationToken cancellationToken)
{
var status = await db.Orders
.Where(o => o.Id == id)
.Select(o => (OrderStatus?)o.Status)
.FirstOrDefaultAsync(cancellationToken);
return status is null
? TypedResults.NotFound()
: TypedResults.Problem(
title: $"The order can't be {wanted}.",
detail: $"Only a placed order can change. This one is {status}.",
statusCode: StatusCodes.Status409Conflict);
}
Response (cancelling it again)
HTTP/1.1 409 Conflict
Content-Type: application/problem+json
{
"type": "https://tools.ietf.org/html/rfc9110#section-15.5.10",
"title": "The order can't be cancelled.",
"status": 409,
"detail": "Only a placed order can change. This one is Cancelled.",
"traceId": "00-58a0af16f25fb94ea62a61d10f54cdc8-b54a760100de83bd-00"
}
Step 4: Race It
An order for 1 unit, with 9 left, and six cancels at once:
Output (status codes, and how many of each)
1 204
5 409
Output (the quantity, and the sum of the movements)
+----------------+----------------+
| QuantityOnHand | SumOfMovements |
+----------------+----------------+
| 10 | 10 |
+----------------+----------------+
One 204, and the stock is back to 10, once.
Step 5: See What MySQL Got
Output (MySQL's log)
+------------------------------------------------------------------------------------------+
| statement |
+------------------------------------------------------------------------------------------+
| SET TRANSACTION ISOLATION LEVEL REPEATABLE READ |
| BEGIN |
| UPDATE `orders` AS `o` SET `Status` = 'Cancelled', `CancelledAtUtc` = timestamp('202 |
| SELECT `o`.`ProductId`, `o`.`Quantity` FROM `order_lines` AS `o` WHERE `o`.`OrderId` = 4 |
| UPDATE `products` AS `p` SET `QuantityOnHand` = `p`.`QuantityOnHand` + 1, `UpdatedAt |
| SAVEPOINT `__EFSavePoint` |
| INSERT INTO `stock_movements` (`IdempotencyKey`, `OccurredAtUtc`, `OrderId`, `ProductId` |
| SELECT `Id` FROM `stock_movements` WHERE ROW_COUNT() = 1 AND `Id` = LAST_INSERT_ID() |
| RELEASE SAVEPOINT `__EFSavePoint` |
| COMMIT |
+------------------------------------------------------------------------------------------+
Common Mistakes
| Mistake | What happens | Do this instead |
|---|---|---|
| Reading the status, checking it in C#, then writing | A race | Put it in the Where |
| Restocking before changing the status | Two requests can both restock | Change the status first |
Answering 200 for a repeat | The client thinks it did something | Answer 409, or make it explicit |
| Looping over lines in client order | Deadlocks | Sort by product ID |
Troubleshooting
| Symptom | Likely cause | Fix |
|---|---|---|
A valid order gets a 409 | Its status isn't what you expected | Read the response's detail |
| Stock goes back twice | The status isn't in the Where | Add it |
Lock wait timeout exceeded | A long transaction on the same order | Keep it short |
Conclusion
Put the current status in the Where of ExecuteUpdateAsync, read the row count, and do everything else only if you changed the row. The row lock does the rest.
Additional Resources
- ExecuteUpdate and ExecuteDelete — set-based updates.
