// Design itWorked on 2015–2019

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.

#systemdesign#databases#ecommerce#concurrency

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.

A reservation's lifecycle. A request becomes Held through an atomic decrement, or Sold out if zero rows were updated. A held reservation becomes Confirmed when payment succeeds, Expired when its time-to-live passes, or Cancelled when the user leaves or payment fails. Expired and cancelled holds release their stock.A reservation's lifecycle. A request becomes Held through an atomic decrement, or Sold out if zero rows were updated. A held reservation becomes Confirmed when payment succeeds, Expired when its time-to-live passes, or Cancelled when the user leaves or payment fails. Expired and cancelled holds release their stock.
Every hold ends as a sale or goes back on the shelf. Explore the interactive diagram ↗
-- 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.
// TL;DR, in one picture
⚡ FLASH SALE · 100 units · 12:00:00checkout #4127SELECT stock → 1 ✓checkout #4128SELECT stock → 1 ✓checkout #4129SELECT stock → 1 ✓checkout #4130SELECT stock → 1 ✓checkout #4131SELECT stock → 1 ✓checkout #4132SELECT stock → 1 ✓checkout #4133SELECT stock → 1 ✓checkout #4134SELECT stock → 1 ✓…all at 12:00:00.003stock remaining−37orders confirmed: 137!Customer support, Monday morning:“So… who tells the 37 people their order is cancelled?”Stock: 100. Orders confirmed: 137.Every checkout saw “1 left”.ayushwing.com