Fixed: Two Requests Buy the Last Item in EF Core (Overselling)

One unit left, five simultaneous orders, and all five answered 201 Created. The order code was in a transaction, and it checked the stock first. It looked correct, and the first time I ran it, it even behaved. The check and the write were two separate steps, and every order did the check before any of them had written.

What I Was Doing

My .NET C# Web API uses EF Core 10.0.14 and MySQL 9.7.4. Each order line loads the product, checks its stock in C#, and changes it, all inside a transaction:

src/Dewiride.Inventory.Api/Features/Orders/OrderEndpoints.cs (excerpt)

// Read the product, check its stock in C#, then change it.
var product = await db.Products
    .FirstOrDefaultAsync(p => p.Id == line.ProductId && !p.IsArchived, ct);

if (product is null)
{
    return TypedResults.Problem(
        title: "The product doesn't exist.",
        detail: $"There is no product with Id {line.ProductId}.",
        statusCode: StatusCodes.Status400BadRequest);
}

if (product.QuantityOnHand < line.Quantity)
{
    return TypedResults.Problem(
        title: "There isn't enough stock.",
        detail: $"Product {line.ProductId} has {product.QuantityOnHand} on hand, and the order asks for {line.Quantity}.",
        statusCode: StatusCodes.Status409Conflict);
}

product.QuantityOnHand -= line.Quantity;
product.UpdatedAtUtc = now;

Five orders for the last unit arrived at once:

Terminal

seq 5 | xargs -P 5 -I{} curl -s -o /dev/null -w "%{http_code}\n" -X POST http://localhost:5190/api/v1/orders -H "Content-Type: application/json" -d '{"customerName":"Buyer {}","customerEmail":"buyer{}@example.com","lines":[{"productId":13,"quantity":1}]}'

How to Test a Race Condition in a Web API from the Terminal explains that command, and has a PowerShell version.

What I Expected

One 201, and four 409 Conflict.

What I Got

The first round, right after the API started, looked fine:

Output (first round)

201
409
409
409
409

One more unit arrived, and five more orders came in, with the same code:

Output (next round)

201
201
201
201
201

The product's quantity should equal the sum of its stock movements, so I compared them:

mysql

SELECT p.QuantityOnHand,
       (SELECT COALESCE(SUM(m.QuantityChange),0) FROM stock_movements m WHERE m.ProductId=p.Id) AS SumOfMovements,
       (SELECT COUNT(*) FROM order_lines l WHERE l.ProductId=p.Id) AS OrdersPlaced
FROM products p WHERE p.Id=10;

Output

+----------------+----------------+--------------+
| QuantityOnHand | SumOfMovements | OrdersPlaced |
+----------------+----------------+--------------+
|              0 |             -4 |            6 |
+----------------+----------------+--------------+

Five orders for the one unit, and the numbers disagree: QuantityOnHand is 0, while the movements add up to -4. OrdersPlaced is 6: the order from the first round, and these five.

What I Checked

What MySQL received. The general query log shows two of the connections:

Output

+------------+--------------------------------------------------------------+
| connection | statement                                                    |
+------------+--------------------------------------------------------------+
|         51 | SELECT `p`.`Id`, `p`.`ArchivedAtUtc`, `p`.`CategoryId`, `p`. |
|         43 | SELECT `p`.`Id`, `p`.`ArchivedAtUtc`, `p`.`CategoryId`, `p`. |
|         51 | UPDATE `products` SET `QuantityOnHand` = 0, `UpdatedAtUtc` = |
|         43 | UPDATE `products` SET `QuantityOnHand` = 0, `UpdatedAtUtc` = |
|         51 | COMMIT                                                       |
|         43 | COMMIT                                                       |
+------------+--------------------------------------------------------------+

Each connection reads, then writes QuantityOnHand = 0. The second UPDATE waits for the first one's row lock, and then sets the same value. It doesn't subtract: EF Core worked out 0 in C# and sent that.

Why the transaction didn't stop it. A plain SELECT in a transaction takes no row lock, under the default isolation level. So every order reads 1.

Why the first round looked fine. A race depends on timing. In that round, right after the API started, the requests didn't overlap between the read and the write. That's why this kind of bug passes a quick test.

Why It Happens

The check happens in C#, and the write happens later, in a separate statement. Between the two, nothing stops another request from doing the same check on the same value.

The Fix

Do the check and the write in one statement, so MySQL does both while it holds the row:

src/Dewiride.Inventory.Api/Features/Orders/OrderEndpoints.cs (excerpt)

// MySQL locks the row, checks the condition and changes the row in one
// statement. A second buyer waits here, then sees the new quantity.
var taken = await db.Products
    .Where(p => p.Id == line.ProductId && !p.IsArchived && p.QuantityOnHand >= line.Quantity)
    .ExecuteUpdateAsync(
        setters => setters
            .SetProperty(p => p.QuantityOnHand, p => p.QuantityOnHand - line.Quantity)
            .SetProperty(p => p.UpdatedAtUtc, now),
        ct);

if (taken == 0)
{

The second order waits for the row, then sees the new quantity, the WHERE doesn't match, and it gets 0 rows, which becomes a 409. How to Prevent Overselling with a Conditional UPDATE in EF Core goes through it.

After the Fix

Output (a new product with one unit)

201
409
409
409
409

Output (the same comparison)

+----------------+----------------+--------------+
| QuantityOnHand | SumOfMovements | OrdersPlaced |
+----------------+----------------+--------------+
|              0 |              0 |            1 |
+----------------+----------------+--------------+

One order, and the numbers agree. It gave the same result on every round.

Common Mistakes

Mistake	What happens	Do this instead
Testing with one request, or once	The race doesn't show	Send them at once, several times
Thinking a transaction prevents this	A plain read takes no lock	Use a conditional UPDATE, or lock the row
Using a concurrency token for stock	Requests fail with stock left	Use a conditional UPDATE
Relying on a check constraint alone	Buyers get a 500	Keep it as a backstop

Troubleshooting

Symptom	Likely cause	Fix
QuantityOnHand and the movements disagree	An oversell happened	Check the orders, then fix the code
One order, then a long wait for others	They're waiting for the row lock	That's expected, and short
Lock wait timeout exceeded under load	A transaction holds the row too long	The fix

Conclusion

Reading a value, checking it in C#, and writing it later is a race, even inside a transaction. Make the check part of the UPDATE's WHERE, and read the row count.

Additional Resources

• ExecuteUpdate and ExecuteDelete — set-based updates.
• Consistent Nonlocking Reads — why a plain SELECT doesn't lock.

Related

• How to Prevent Overselling with a Conditional UPDATE in EF Core
• How to Lock a Row with SELECT FOR UPDATE in EF Core
• How to Test a Race Condition in a Web API from the Terminal