Skip to content
Tutorials Center
My learning

Topics

Lesson 8 of 40 ยท Inventory

Reservations and Overselling

Prevent overselling in your online shop: when to reserve stock, TTL holds, race conditions, atomic decrements, optimistic locking and flash-sale tactics.

There is one tent left. Two shoppers click "Pay" within the same few milliseconds. Both pages say "in stock", both payments succeed, and one customer is about to get an apology email. That is overselling, and it is the classic concurrency bug of online shops.

Two separate problems hide inside it. The first is a policy question: at what moment in the shopping journey does a unit become "taken"? The second is a technical one: how do you make sure two requests can never both claim the last unit? This lesson answers both, ending with a small reservation system with expiring holds that you can run. It builds on the available-stock idea from Inventory Tracking.

When should stock be reserved?

A reservation (also called a hold or allocation) lowers available without yet lowering on hand. You can create one at four different moments:

Moment Pros Cons
Add to cart Shoppers never see "sorry, gone" at checkout Abandoned carts lock stock; bots can empty your shelves
Start of checkout Locks only people who are serious Still some abandonment
Payment attempt Very little stock wasted The customer may type their card number and then be told it is gone
After payment succeeds Zero wasted holds You may have taken money for something you cannot ship

There is no universal answer. Most shops reserve when checkout begins with a short time limit, and re-check at payment. Very scarce items (concert tickets, limited drops) reserve earlier and shorter. Very plentiful items (t-shirts with 5,000 in stock) often do not reserve until the order is placed, because the risk is tiny.

flowchart LR
    C["Cart<br/>no hold"] --> K["Checkout starts<br/>hold, TTL 10 min"]
    K --> P["Payment<br/>extend or re-check"]
    P --> O["Order placed<br/>hold becomes committed"]
    K -. "timeout" .-> R["Hold expires<br/>units return to available"]
    P -. "payment fails" .-> R

A common flow: hold at checkout, commit on success, release on timeout or failure.

TTL holds

Every hold must have an expiry, or a customer who closes the tab will lock a unit forever. A TTL (time to live) of 5 to 15 minutes is typical. When a hold expires its units become available again, which usually needs only two pieces of logic:

  1. Lazy filtering: whenever you compute reserved, ignore holds whose expires_at is in the past. No cleanup job is needed for correctness.
  2. A sweeper: a scheduled job deletes expired rows so the table stays small and any counters you cache are corrected.

Think about the late-payment case, too. If a customer pays one second after the hold expired, you must either re-check stock and reserve again (and refund if it fails) or reject the payment attempt. Do not simply create the order and hope.

Payment providers confirm payments asynchronously, often through webhooks. Make the TTL longer than the longest normal payment flow, or extend the hold when a payment is in progress.

Race conditions: how overselling really happens

The root cause is almost always check-then-act: read the stock, decide in application code, then write. Between the read and the write, someone else can do the same thing.

Python Check-then-act versus atomic updates

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The first line is the bug. Two orders were accepted for one tent, and the final stock of 0 looks perfectly healthy, so nothing alerts you. The stock number even looks right; only the order count gives the problem away. The two fixes, in order of preference, are described next.

Fix 1: atomic conditional decrement

Let the database do the check and the write as a single statement, so no one can slip in between:

SQL
UPDATE stock_levels
SET    on_hand = on_hand - :qty
WHERE  sku = :sku
  AND  location_id = :loc
  AND  on_hand - committed >= :qty;

Then look at the number of affected rows. One row means you got the units. Zero rows means someone else did, and you tell the shopper. The row is locked for the duration of the statement, so two concurrent updates are applied one after another and the second sees the first one's result. Compare this with the lost-update replay in Isolation Levels and Locking, which explains why a plain SELECT followed by UPDATE is unsafe.

This works with one condition: the decision must be made inside the statement. A database check constraint such as CHECK (on_hand >= 0) is a useful second safety net, because it makes the database refuse any write that would go negative.

Fix 2: optimistic locking

Sometimes the decision needs several reads (for example "does this bundle have all its parts?"). Optimistic locking keeps a version number on the row. You read the row and its version, compute the change, and write with WHERE version = :seen. If someone changed the row in the meantime, zero rows match and you retry from the read.

It is called optimistic because it assumes conflicts are rare and takes no lock while you think. When conflicts are common (a hot product), retries pile up, and pessimistic locking (SELECT ... FOR UPDATE) or a plain atomic update is a better fit. Whichever you use, code for the failure path: a bounded number of retries, then a clear error to the shopper.

A reservation system you can run

Now combine holds, TTL, commit and a sweeper. The Clock is fake so the run is deterministic. In real code you would use database time and a lock (or a single SQL statement) around the check-and-insert.

Python Holds with expiry

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Walk through the output. Ana's hold of 2 leaves 1 available, so Ben's request for 2 is refused (None) even though nobody has paid yet. That is the point of a reservation. Cy takes the last unit. When Ana pays, her hold is converted: on hand drops from 3 to 1 and her hold disappears. Cy never pays, so 400 seconds later his hold has lapsed and the unit is available again without any job having run. His late payment is rejected, the sweeper cleans up, and Ben can finally buy the unit.

Choosing a strategy under heavy load

  • Normal traffic: an atomic conditional update on the stock row, plus a short hold at checkout.
  • Hot products: every request hitting one row serializes on its lock. Keep the transaction tiny (one statement, no network calls inside it) and avoid doing slow work such as calling a payment provider while holding a lock.
  • Flash sales and drops: put a queue or waiting room in front of checkout so only as many people enter as there is stock, pre-load the number of units into a fast counter (for example Redis DECR), limit quantity per customer, and add bot protection. Let the database remain the final authority and reconcile afterwards.
  • Splitting stock into buckets (for example 10 rows of 10 units) spreads the lock contention for extreme cases, at the cost of a more complex "which bucket" rule.

Always monitor the invariant: committed plus reserved never exceeds on hand, and no stock level is negative. A scheduled check that alerts when it breaks turns silent overselling into a visible alarm.

Do not rely on a caching layer to enforce stock. A cached "in stock" label is fine for display, but the final decision must always come from the authoritative store at the moment of reservation.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Two requests read stock = 1, both decide it is enough, and both write back stock = 0. What is this bug called?

    The check and the write were separate steps, so both requests passed the check. Making the check and the write one atomic statement prevents it.
  2. 2. You run UPDATE stock_levels SET on_hand = on_hand - 1 WHERE sku = 'X' AND on_hand - committed >= 1; and the database reports 0 affected rows. What should the shop do?

    Zero affected rows means the condition was false at that moment, so there was not enough available stock. Retrying will not create stock.
  3. 3. Why does every stock hold need a TTL?

    Customers close tabs and payments fail. An expiry returns those units to available automatically.
  4. 4. Which situation suits optimistic locking best?

    Optimistic locking takes no lock while you compute and simply retries on a version mismatch, which is cheap when conflicts are rare. Under constant conflict the retries dominate.

Summary

  • Decide when to reserve: most shops hold stock when checkout starts, with a short TTL, and re-check at payment.
  • Overselling comes from check-then-act races; never decide in application code from a stale read.
  • Prefer one atomic conditional UPDATE and check the affected-row count; add a CHECK (on_hand >= 0) safety net.
  • Use optimistic locking (a version column plus retries) for multi-step decisions with rare conflicts.
  • Holds expire lazily and are cleaned by a sweeper; late payments must re-check stock.
  • For flash sales, use a queue, per-customer limits and a fast counter, with the database as the final authority.

Next: Pricing Models.