Your Stock Table Looks Fine. You Still Sold the Last Unit Twice.

Here's the oversell bug that bothers me most. A customer support ticket says two people were promised the last unit of the same product. You open the database and look at the stock row: OnHand = 1, Reserved = 1, Available = 0. Perfectly consistent. No negative number, no constraint violation, no exception in the logs. Everything looks right, and you still sold one unit twice.

In this post I'll focus on that one part of the problem: why the stock row looks healthy after an oversell, which number you should check instead, and how to make the race happen on every test run instead of once a month in production. The fixes come at the end, in outline. Everything here comes from a small EF Core 10 proof project I call StockGuard, running on SQLite with .NET SDK 10.0.401. It builds with zero warnings and its 17 xUnit tests pass.

If you need a complete multi-warehouse app rather than a proof project, the Inventory Management System (.NET 9) has it, and Tech Skill Builder has complete, tested .NET projects with full source code.

Tech Skill Builder is $5/month for a very limited time only (normally $10). Grab it now before it goes back to $10.

How the row ends up lying

The model is simple. There's one StockLevel row per warehouse and product, with OnHand, Reserved and a computed Available => OnHand - Reserved. Each order also writes a Reservation row with the order ID, warehouse and quantity.

The code most of us write first does this:

await using var db = createContext();
var levels = await db.StockLevels
    .Where(s => s.ProductId == productId)
    .OrderBy(s => s.Warehouse.Priority)
    .ToListAsync(ct);
var plan = AllocationPlanner.Plan(
    levels.Select(l => new StockSnapshot(l.Id, l.WarehouseId, l.Available)), quantity);
if (plan is null) return AllocationResult.OutOfStock(1);

foreach (var line in plan)
{
    levels.Single(l => l.Id == line.StockLevelId).Reserved += line.Quantity;
    db.Reservations.Add(new Reservation { /* order, product, warehouse, quantity */ });
}
await db.SaveChangesAsync(ct);

Reserved += 1 looks relative in C#, but EF Core sends an absolute value. This is the SQL I captured from the run:

UPDATE "StockLevels" SET "Reserved" = @p0, "Version" = @p1
WHERE "Id" = @p2
RETURNING 1;

(This version of the context has no concurrency token, so Version is written but never checked.) Two requests both read Reserved = 0 and both write Reserved = 1. The second write replaces the first. That's a lost update.

Now look at what each table says afterwards:

Source	Value
StockLevels.OnHand	1
StockLevels.Reserved	1
Sum of Reservations.Quantity	2

The stock row is internally consistent. Only the Reservations table shows what was actually promised. That's also why a check constraint like Reserved <= OnHand never fires here: 1 <= 1 is true. I keep the constraint anyway (more on that below), but it can't catch this bug.

The number to check: promised vs on hand

So the invariant I test isn't "Reserved never goes above OnHand." It's "what we promised never goes above what's on the shelf." In the test project, that's one small record:

// OnHand and Reserved come from StockLevels; Promised is the sum of Reservations.
public sealed record StockState(int OnHand, int Reserved, int Promised)
{
    public bool Oversold => Promised > OnHand;
}

Promised comes from the reservation rows, not from the counter. If your inventory code keeps a counter and a ledger, check the counter against the ledger. That comparison is where lost updates show up.

Make the race happen every time

Firing two Task.Run calls and hoping they overlap gives you a flaky test, and a flaky test teaches the team to ignore it. I added one seam to the allocators instead, called after the read and before any write:

public interface IAllocationProbe
{
    void AfterRead(int attempt);
}

In production it's null. In tests, a probe backed by System.Threading.Barrier holds every request at that point until all of them have read:

public sealed class BarrierProbe(int participants) : IAllocationProbe
{
    private readonly Barrier _barrier = new(participants);
    public void AfterRead(int attempt)
    {
        if (attempt == 1 && !_barrier.SignalAndWait(TimeSpan.FromSeconds(10)))
            throw new TimeoutException("Not every request reached the barrier.");
    }
}

Only the first attempt waits, so the retries in the fixed versions can continue. Each request gets its own DbContext and its own connection to a temporary SQLite file in WAL mode (not :memory:), so it behaves like two app instances sharing a database. The test then looks like this:

[Fact]
public async Task Naive_read_check_write_oversells_when_two_requests_interleave()
{
    var seeded = _db.Seed(1); // one unit, one warehouse
    var allocator = new NaiveStockAllocator(_db.CreateNaiveContext, new BarrierProbe(2));
    var results = await Race.RunAsync(2, i => allocator.AllocateAsync($"order-{i}", seeded.ProductId, 1));

    var state = _db.State(seeded.ProductId);
    Assert.True(state.Oversold);     // Promised 2, OnHand 1
    Assert.Equal(1, state.Reserved); // the row still looks healthy
    // remaining asserts (both results succeeded, exact counts) in the complete project
}

This test passes, which means the bug is real and reproducible. The companion test runs the same allocator twice in a row and gets OutOfStock on the second call, which is exactly why the bug survives manual testing.

Three questions for every stock write

Once the race shows up on every run, the fixes are easy to judge. I ask three questions about any code that changes stock:

1. Does the write depend on a value read earlier? If yes, a stale read can overwrite someone else's change. The strongest fix moves the check into the same statement as the write, using a guarded relative update:

var rows = await db.StockLevels
    .Where(s => s.Id == id && s.OnHand - s.Reserved >= qty)
    .ExecuteUpdateAsync(u => u
        .SetProperty(s => s.Reserved, s => s.Reserved + qty)
        .SetProperty(s => s.Version, s => s.Version + 1), ct);
if (rows == 0) { /* roll back, re-read, re-plan */ }
// transaction, Reservation inserts and the retry loop: full version in the complete project

ExecuteUpdate skips the change tracker, so it bumps Version itself. Otherwise the tracked code paths wouldn't notice the change.

2. What happens to the loser? If you keep the change tracker, add a concurrency token (IsConcurrencyToken() on Version). The losing request then gets DbUpdateConcurrencyException instead of silently winning. Then re-plan from fresh data, don't just retry the same write. With several warehouses, that matters: with 1 unit in WH-A and 5 in WH-B, the barrier race ends with both orders filled, because the loser's second attempt takes its unit from WH-B.

3. What stops a script that skips all of this? A check constraint such as "Reserved" >= 0 AND "Reserved" <= "OnHand". It won't catch the lost update above, but it does reject an unguarded increment past what's on hand. I saw SQLite fail with CHECK constraint failed: CK_StockLevels_Reserved_Range. You need it, just not on its own.

What I ran, and what I didn't

The 17 tests cover the naive race, optimistic concurrency with re-planning, the guarded ExecuteUpdate (including 40 concurrent buyers for 10 units, which sold exactly 10), and the check constraints. I ran the suite six more times on the final code with no failures.

What I didn't verify: all of it ran on SQLite, which allows one writer at a time. The barrier forces the dangerous interleaving, but this isn't a test of truly parallel writers on SQL Server or PostgreSQL. On those, use the database's own row version (rowversion on SQL Server, xmin through Npgsql). The guarded UPDATE ... WHERE available >= qty pattern carries over, but check your isolation level's behavior in your provider's docs.

Building the real thing?

A proof project shows the mechanics. An inventory product needs everything around them. The Inventory Management System (.NET 9) is a complete ASP.NET 9 + SQL Server app with multi-branch, multi-warehouse stock tracking, on-hand, committed and available quantities, inter-warehouse transfers, purchase orders, shipments and role-based access. It includes full source code, costs $20 one-time, and you can use it in client projects. You start with the screens and workflows already built and spend your time on your own business rules.

If you'd rather learn by reading and running complete code, Tech Skill Builder gives you complete, working, tested .NET projects with full source code and step-by-step PDF guides, with new ones added regularly. Tech Skill Builder is $5/month for a very limited time only (normally $10). Grab it now before it goes back to $10. 👉 Join Tech Skill Builder

How do you catch lost updates in your own system: a ledger-vs-counter check like this, a nightly reconciliation job, or the support inbox?

Comments

[Mehdi Mohseni]

On the PostgreSQL caveat: at Read Committed the guarded UPDATE does carry over, because the second writer waits for the row lock and then re-checks its WHERE against the committed row. At Repeatable Read it fails with SQLSTATE 40001 instead, so the re-plan loop has to catch that too, and it isn't enough to check rows == 0.