Design it: inventory reservations that don't oversell
A system design for selling limited stock under heavy concurrency: why check-then-decrement oversells, conditional updates, reservations with expiry, hot-row contention during flash sales, sharded counters, admission control and reconciliation.
You have 100 units of something people want. You sell 137 of them.
Nobody wrote an obviously wrong line of code. Every checkout did the sensible thing: read the stock, saw at least one left, placed the order, decremented. They just did it at the same time.
I’ve worked on inventory and checkout at an SMB commerce platform (real-time inventory and automated distributor restocking) and at an e-commerce marketplace (checkout and refunds). This is how I’d design stock reservations so that the count is never wrong, even when a flash sale sends a crowd at one product.
Requirements
- Never oversell. If we have 100, at most 100 orders succeed.
- Never strand stock. If someone starts checkout and walks away, that unit becomes available again.
- Stay fast under a spike. A flash sale might bring tens of thousands of buyers to one product in the same minute.
- Be fair enough. Roughly first come, first served.
- Be correct after failures: crashed workers, retried requests, payment timeouts.
The bug: check, then act
int stock = inventory.available(sku); // reads 1
if (stock >= qty) {
orders.create(cart); // another checkout does the same thing here
inventory.decrement(sku, qty); // stock goes to -1
}
Between the read and the write, other requests read the same value. Locks in the application don’t help once you have more than one instance. The fix isn’t a faster check. It’s to make the check and the decrement the same atomic operation.
Step 1: one conditional update
Let the database do the check as part of the write:
UPDATE inventory
SET available = available - :qty
WHERE sku = :sku AND available >= :qty;
If the update changes one row, you got the stock. If it changes zero rows, there wasn’t enough. There’s no window between check and act, because the database applies the condition and the change under the same row lock. This is the same idea as the conditional state transitions in Model business workflows as state machines: ask the database to move only if the precondition still holds, and treat “zero rows” as an answer.
That alone fixes overselling. It doesn’t yet handle people who leave.
Step 2: hold stock with an expiry
Decrementing at “add to cart” strands stock in abandoned carts. Decrementing at “payment succeeded” lets two people pay for the last unit. The middle ground is a reservation: a hold taken when checkout starts, which either becomes a sale or expires.


-- in one transaction
UPDATE inventory SET available = available - :qty
WHERE sku = :sku AND available >= :qty; -- 0 rows: sold out, stop
INSERT INTO reservations (id, sku, qty, status, expires_at)
VALUES (:reservationId, :sku, :qty, 'HELD', now() + interval '10 minutes');
On payment success, the reservation moves HELD → CONFIRMED with a conditional update (WHERE status = 'HELD'). A background job finds holds past expires_at, moves them HELD → EXPIRED, and adds the quantity back to available, in the same transaction. Because each transition only happens from HELD, a late payment and the expiry job can’t both win.
Two details that matter in production:
- The reservation id comes from the client’s checkout session, so a retried “reserve” request finds the existing hold instead of taking a second one. Retries are normal; double holds would quietly remove stock from sale.
- Pick the expiry from your payment flow, not from a guess. If payment can take up to eight minutes including a bank’s authentication step, a five-minute hold will expire under paying customers.
Step 3: the flash sale problem
Everything above is correct, and on a normal day it’s enough. In a flash sale, it hits a different limit: every buyer updates the same row. Row locks serialise those updates, so throughput for that product is capped by how fast one row can be locked, updated and committed, no matter how big the database is. Requests queue, latency climbs, connection pools fill up, and the rest of the site slows down too.
Three tools, usually combined:
Split the counter. Instead of one row with 1,000 units, keep ten rows (buckets) of 100. Each reservation picks a random bucket and does the same conditional decrement there; if that bucket is empty, try another. Contention drops by roughly the number of buckets. The cost: “is it sold out?” now means “are all buckets empty?”, and the last few units are scattered across buckets.
Gate in memory, settle in the database. Put the sale’s counter in Redis and decrement it atomically there first (a DECRBY that you undo if it goes below zero, or a small Lua script that checks and decrements in one step). Only requests that get a unit from Redis go on to write the reservation in the database. Redis absorbs the crowd; the database sees only as many writers as there are units. The database stays the source of truth, which is why you need step 5.
Admission control. If 100,000 people want 1,000 units, 99,000 of them are going to be disappointed whatever you do. A waiting room or queue in front of checkout lets in a steady number of buyers at a time, so you’re designing for a controlled rate instead of a spike. It’s also kinder to users: “you’re in the queue” beats a timeout.
Step 4: decide what “available” means on the product page
The product page doesn’t need the exact number; it needs “in stock”, “only a few left” or “sold out”. Read that from a cache or a replica, refreshed every few seconds. Only the reservation step must be exact. Mixing the two, so that every page view reads the hot row, adds load to the exact place you’re trying to protect.
Step 5: reconcile
Counters drift: a crashed process between the Redis decrement and the database write, a bug in a release path, a manual fix someone forgot to undo. Run a reconciliation job that recomputes truth from the records that matter:
available = on_hand − confirmed_orders − active_holds
Compare it to the counters, alert on any difference, and correct from the records, never the other way round. A reconciliation that runs every few minutes turns a silent drift into a page with a clear number on it.
Failure modes
| Failure | Without the design | With it |
|---|---|---|
| Concurrent checkouts on the last unit | Both succeed; oversold | One conditional update wins; the other gets “sold out” |
| User abandons checkout | Unit stuck forever | Hold expires and stock returns |
| Client retries “reserve” | Two holds for one cart | Same reservation id; second call returns the first hold |
| Payment arrives after expiry | Sold a unit that went back on sale | HELD → CONFIRMED fails; refund or re-reserve |
| Flash sale crowd | Hot row; whole site slows | Buckets, in-memory gate, admission queue |
| Counter drift | Wrong stock shown or sold | Reconciliation detects and corrects |
Takeaways
- Overselling is a check-then-act race. Make the check and the decrement one conditional update.
- Use reservations with an expiry, so checkout holds stock without stranding it.
- Make “reserve” idempotent with an id from the checkout session.
- Under a flash sale the single row is the bottleneck: split it, gate it in memory, and admit buyers at a steady rate.
- Reconcile counters against orders and holds, and trust the records over the counters.