Prevent overselling in EF Core: concurrency-safe stock updates

Stock tracking looks like the easiest part of an online store. Every product has a quantity, and each order subtracts from it. Then a sale goes live, two people buy the last item in the same second, and you've sold something you don't have.

Here's why the obvious code fails, and the small change that fixes it on SQL Server, PostgreSQL, MySQL and SQLite alike. Stock tracking is a core part of eCommerceMVCCore, my ASP.NET Core e-commerce platform, so it's a problem I've had to think through. The code is simplified.

The obvious code

var product = await db.Products.FindAsync(productId);
if (product.StockQuantity < quantity)
    return Fail("Not enough stock.");

product.StockQuantity -= quantity;
await db.SaveChangesAsync();

Run it for two requests at once with one item in stock:

1. Request A reads StockQuantity = 1. The check passes.
2. Request B reads StockQuantity = 1. The check passes.
3. A saves 0.
4. B saves 0.

Two orders, one item, and the stock says 0 as if nothing went wrong. It's the classic lost update, and nothing in that code will warn you.

Fix 1: a conditional update

Let the database check and subtract in one statement:

var updated = await db.Products
    .Where(p => p.Id == productId && p.StockQuantity >= quantity)
    .ExecuteUpdateAsync(s => s.SetProperty(p => p.StockQuantity, p => p.StockQuantity - quantity));

if (updated == 0)
    return Fail("Sorry, that item just sold out.");

That becomes a single UPDATE ... SET StockQuantity = StockQuantity - @qty WHERE Id = @id AND StockQuantity >= @qty. The database locks the row while it runs, so only one of the two requests can match the WHERE clause. The other updates zero rows and gets a friendly message instead of an order.

This is my default. It's short, fast, needs no schema change, and behaves the same on every provider.

Fix 2: optimistic concurrency

If you'd rather keep the load-modify-save style, add a concurrency token. EF Core then includes the original value in the UPDATE and throws if the row changed in the meantime:

modelBuilder.Entity<Product>()
    .Property(p => p.StockQuantity)
    .IsConcurrencyToken();

try
{
    product.StockQuantity -= quantity;
    await db.SaveChangesAsync();
}
catch (DbUpdateConcurrencyException)
{
    // someone else changed the stock: reload and check again, or tell the shopper
}

On SQL Server you could use a rowversion column instead, but that's SQL Server-specific. Marking the stock column itself as the token works on all four databases.

Multiple items in one order

A cart with three products needs all three decrements to succeed, or none. Wrap them, and saving the order, in one transaction:

await using var tx = await db.Database.BeginTransactionAsync();

foreach (var line in cart.Lines)
{
    var ok = await db.Products
        .Where(p => p.Id == line.ProductId && p.StockQuantity >= line.Quantity)
        .ExecuteUpdateAsync(s => s.SetProperty(p => p.StockQuantity, p => p.StockQuantity - line.Quantity));

    if (ok == 0)
    {
        await tx.RollbackAsync();
        return Fail($"{line.ProductName} doesn't have enough stock left.");
    }
}

db.Orders.Add(order);
await db.SaveChangesAsync();
await tx.CommitAsync();

If anything fails, the rollback puts the earlier lines' stock back.

Building a store? eCommerceMVCCore gives you stock tracking, a low-stock filter and a server-checked checkout in ASP.NET Core 10.

Where payments fit in

With online payments there's a second race: the stock can run out between "Pay" and the payment being captured. My order is: re-check stock, capture the payment, then save the order and decrement stock in one transaction. If that final step fails, refund automatically. I covered that flow in my article on taking PayPal payments safely.

Show low stock before it's zero

The other half of stock is the admin side. A "low stock" filter on the product list, for example StockQuantity <= 5, tells the store owner what to reorder before customers see "sold out".

FAQ

What is a lost update?

Two transactions read the same value, both change it, and the second save overwrites the first. Neither fails, so the bug stays silent until the numbers don't add up.

Should I use ExecuteUpdateAsync or a concurrency token?

ExecuteUpdateAsync (EF Core 7 and later) checks and changes stock in one statement with no retry logic. A concurrency token keeps the load-modify-save style but needs you to catch DbUpdateConcurrencyException and retry. Both work on every major provider.

Should I reserve stock when an item is added to the cart?

Usually not for a small store: reserving at checkout is simpler. Cart reservations need expiry times and clean-up, or abandoned carts lock up stock. eCommerceMVCCore shows stock on every product page and lets admins filter for low stock.

Get the source code

Stock tracking with a low-stock filter in the admin dashboard is part of eCommerceMVCCore. Get the ASP.NET Core 10 e-commerce source code.

Have you been bitten by a lost update in production? Tell me how you found it.