Design an Inventory System: Prevent Overselling

A product launch: one item, a few hundred units, thousands of carts a second reaching for it. Storing a count is trivial; the design question is who may hear "yes" for the last unit, and what happens to units held by buyers who wander off. Wrong one way, you sell stock you do not have. Wrong the other way, units sit unsellable while customers see "sold out".

The problem it solves

• Never oversell. Selling one unit twice means a cancelled order and an apology.
• Hold stock during checkout. Payment takes seconds to minutes, and the buyer who started paying should not lose the unit to someone faster.
• Give abandoned stock back, without anyone noticing.
• Survive a hot item. The most wanted product has the most concurrent writes.

Product pages can show a cached, slightly stale count. Only the reservation path must be exact, and it goes to the primary database.

The intuition

Four decisions carry the design:

1. Check and write in one statement. UPDATE stock SET available = available - n WHERE sku = ? AND available >= n. The database applies it atomically, so two carts cannot both see "1 left" and both take it. Zero rows updated means sold out. Reading the count and writing it back in two steps is the classic race; the hotel booking design runs it with two real connections.
2. A hold, not a sale. A successful decrement creates a hold with an expiry, say ten minutes. The unit is neither sold nor available.
3. Confirm conditionally too. Payment confirms the hold only if it is still held and unexpired. Otherwise the unit may already be back on the shelf, or in someone else's cart, and the right answer is to refund, not to ship.
4. A sweeper returns expired holds. It flips each expired hold to released and adds its units back, each hold exactly once.

A very hot item adds a fifth: split its stock across several rows, buckets, so concurrent buyers rarely queue on the same row.

Watch it run

The animation follows the lesson's design. One unit is left on the hottest item, and two carts reach for it in the same second. Reading the count and then writing it back leaves a gap wide enough for both: both see 1, and overselling is a real risk. So the decrement itself carries the condition: subtract one only while one remains. Cart A wins the row: one statement, one row updated, decided by the store, not by the caller, and available drops to 0. Cart B runs the same statement a moment later and matches no rows at all; it hears "sold out", and overselling is impossible. The unit is not sold yet: it is held for cart A, with a ten-minute timer attached. Payment clears inside the window, the hold is spent, and the order is written. Had the buyer walked away, the timer would have handed the unit back by itself, available 1 again. Which is the first cost: stock sitting unsellable while somebody else's timer runs. And the second: every buyer serialises on one hot row, exactly when it is hottest. Correct beats fast here: a sold-out message is survivable, a double sale is not, and the unit is sold once.

The code

A toy model on Python's built-in SQLite, with time passed in as a number of seconds so expiry is deterministic. Every write is a conditional statement inside a real transaction:

import random
import sqlite3

HOLD_SECONDS = 600

def open_shop(stock):
    db = sqlite3.connect(":memory:", isolation_level=None)       # we issue BEGIN/COMMIT
    db.executescript("""
    CREATE TABLE stock (sku TEXT PRIMARY KEY, available INTEGER NOT NULL CHECK (available >= 0));
    CREATE TABLE hold (id INTEGER PRIMARY KEY, sku TEXT, qty INTEGER, cart TEXT,
                       expires_at INTEGER, status TEXT);          -- held, sold or released
    CREATE TABLE orders (hold_id INTEGER PRIMARY KEY);           -- one order per hold
    """)
    db.executemany("INSERT INTO stock VALUES (?, ?)", stock.items())
    return db

def reserve(db, sku, qty, cart, now):
    db.execute("BEGIN IMMEDIATE")
    cur = db.execute("UPDATE stock SET available = available - ? "
                     "WHERE sku = ? AND available >= ?", (qty, sku, qty))
    if cur.rowcount == 0:                         # the condition failed: sold out
        db.execute("ROLLBACK")
        return None
    hold = db.execute("INSERT INTO hold (sku, qty, cart, expires_at, status) "
                      "VALUES (?, ?, ?, ?, 'held')", (sku, qty, cart, now + HOLD_SECONDS)).lastrowid
    db.execute("COMMIT")
    return hold

def confirm(db, hold, now):
    """Payment succeeded: sell the hold, but only if it is still held and unexpired."""
    db.execute("BEGIN IMMEDIATE")
    cur = db.execute("UPDATE hold SET status = 'sold' WHERE id = ? AND status = 'held' "
                     "AND expires_at > ?", (hold, now))
    if cur.rowcount == 0:
        status = db.execute("SELECT status FROM hold WHERE id = ?", (hold,)).fetchone()[0]
        db.execute("ROLLBACK")
        return "sold" if status == "sold" else "expired: refund the payment"
    db.execute("INSERT INTO orders VALUES (?)", (hold,))
    db.execute("COMMIT")
    return "sold"

def sweep(db, now):
    """Return every expired hold's units to stock, each exactly once."""
    db.execute("BEGIN IMMEDIATE")
    released = 0
    for hold, sku, qty in db.execute("SELECT id, sku, qty FROM hold WHERE status = 'held' "
                                     "AND expires_at <= ?", (now,)).fetchall():
        if db.execute("UPDATE hold SET status = 'released' WHERE id = ? AND status = 'held'",
                      (hold,)).rowcount:
            db.execute("UPDATE stock SET available = available + ? WHERE sku = ?", (qty, sku))
            released += qty
    db.execute("COMMIT")
    return released

available = lambda db, sku: db.execute("SELECT available FROM stock WHERE sku = ?", (sku,)).fetchone()[0]

shop = open_shop({"sku-42": 1})
a = reserve(shop, "sku-42", 1, "cart A", now=0)
print(a, reserve(shop, "sku-42", 1, "cart B", now=1), available(shop, "sku-42"))   # 1 None 0
print(sweep(shop, now=300), sweep(shop, now=601), available(shop, "sku-42"))       # 0 1 1
b = reserve(shop, "sku-42", 1, "cart B", now=602)
print(confirm(shop, a, now=650))     # expired: refund the payment
print(confirm(shop, b, now=700), confirm(shop, b, now=701))   # sold sold

Cart A held the unit, B was told "sold out", A's hold expired and the sweeper gave the unit back, B got it, and A's payment at 650 seconds was refused instead of selling the unit twice. A repeated confirm for B returns "sold" again, so a retried payment callback is harmless.

Now the hot item. Split 100 units over four rows; each buyer starts at a random row and moves on only if it is empty, so "sold out" means every row said no. The model counts how many of eight simultaneous buyers land on the busiest row:

def open_buckets(sku, units, buckets):
    db = sqlite3.connect(":memory:", isolation_level=None)
    db.execute("CREATE TABLE bucket (sku TEXT, b INTEGER, available INTEGER CHECK (available >= 0), "
               "PRIMARY KEY (sku, b))")
    db.executemany("INSERT INTO bucket VALUES (?, ?, ?)",
                   [(sku, b, units // buckets + (b < units % buckets)) for b in range(buckets)])
    return db

def reserve_bucketed(db, sku, qty, rng, buckets):
    """Start at a random row so concurrent buyers rarely touch the same one."""
    first = rng.randrange(buckets)
    for k in range(buckets):
        b = (first + k) % buckets
        if db.execute("UPDATE bucket SET available = available - ? WHERE sku = ? AND b = ? "
                      "AND available >= ?", (qty, sku, b, qty)).rowcount:
            return b
    return None                                   # sold out only when every row said no

rng = random.Random(42)
hot = open_buckets("sku-42", 100, 4)
sold = sum(reserve_bucketed(hot, "sku-42", 1, rng, 4) is not None for _ in range(150))
print(sold, hot.execute("SELECT b, available FROM bucket").fetchall())
# 100 [(0, 0), (1, 0), (2, 0), (3, 0)]

tight = open_buckets("sku-42", 3, 3)
print(reserve_bucketed(tight, "sku-42", 3, rng, 3), tight.execute("SELECT SUM(available) FROM bucket").fetchone())
# None (3,)

for buckets in (1, 4, 16):
    worst = [max(sum(rng.randrange(buckets) == b for _ in range(8)) for b in range(buckets))
             for _ in range(10_000)]
    print(buckets, round(sum(worst) / len(worst), 2))
# 1 8.0
# 4 3.28
# 16 1.93

Exactly 100 of 150 attempts succeed, never 101, and the busiest row's queue drops from 8 to about 3.3 with four rows. The price: three units exist, one per row, and an order for three fails. Multi-unit orders then need a transaction across rows, or rebalancing.

The seeded check: 200 shops, each taking 60 random reserves, payments (some late), abandons and sweeps, replayed against a plain-Python reference. After every step, available plus held plus sold units must equal the starting stock, and no expired hold may ever become an order:

ok = True
for seed in range(200):
    r = random.Random(seed)
    start = r.randint(0, 5)
    db, now = open_shop({"sku": start}), 0
    ref = {"available": start, "holds": {}}            # hold -> [qty, expires_at, status]
    for _ in range(60):
        now += r.choice([1, 30, 200, 700])
        roll, open_holds = r.random(), [h for h, v in ref["holds"].items() if v[2] == "held"]
        if roll < 0.5:
            qty = r.randint(1, 3)
            got = reserve(db, "sku", qty, "c", now)
            want = ref["available"] >= qty
            ok &= (got is not None) == want
            if want:
                ref["available"] -= qty
                ref["holds"][got] = [qty, now + HOLD_SECONDS, "held"]
        elif roll < 0.8 and ref["holds"]:
            h = r.choice(sorted(ref["holds"]))
            got, v = confirm(db, h, now), ref["holds"][h]
            if v[2] == "held" and v[1] > now:
                v[2] = "sold"
            ok &= got == ("sold" if v[2] == "sold" else "expired: refund the payment")
        else:
            back = sum(v[0] for v in ref["holds"].values() if v[2] == "held" and v[1] <= now)
            for v in ref["holds"].values():
                if v[2] == "held" and v[1] <= now:
                    v[2] = "released"
            ref["available"] += back
            ok &= sweep(db, now) == back
        held = sum(v[0] for v in ref["holds"].values() if v[2] == "held")
        sold_units = sum(v[0] for v in ref["holds"].values() if v[2] == "sold")
        ok &= available(db, "sku") == ref["available"]
        ok &= ref["available"] + held + sold_units == start
    orders = {h for (h,) in db.execute("SELECT hold_id FROM orders")}
    ok &= orders == {h for h, v in ref["holds"].items() if v[2] == "sold"}
print(ok)                                          # True

The complexity

• Reserve, confirm: one indexed row update and one insert each, in short transactions; the lock on the stock row is held for microseconds, not for the payment.
• Sweep: proportional to the holds that expired since the last run, with an index on status and expiry.
• Hot item: throughput on one row is bounded by how fast the database serialises updates to it; k buckets divide the queue roughly by k, at the cost of k probes when stock runs low.

Where it goes wrong

• Read, then write. Two carts both see 1 and both buy. Put the condition in the UPDATE.
• Confirming without a condition. A payment that lands after expiry sells a unit that was already given back.
• Holding a database lock across payment. Use a hold with an expiry instead of a long transaction.
• Expiry too long or too short. Long holds strand stock; short ones expire while honest buyers type their card.
• Decrementing a cached count. The cache is for display only.

When it shows up in interviews

As "design an inventory system", "design a flash sale" or "how do you prevent overselling?", and inside e-commerce checkout designs. Follow-ups: the last-unit race, abandoned carts, payment arriving late, a hot item, and the payment itself, covered in the payment ledger design. As of October 2026, conditional updates, CHECK constraints and short transactions behave this way in every mainstream relational database; some designs put a queue in front of a hot item during launches instead (from memory).

How to say it in an interview

"The reservation is one conditional statement: decrement where available is at least n, and zero rows updated means sold out, so the database decides who gets the last unit. A successful decrement creates a hold with a ten-minute expiry; payment confirms it only if it's still held and unexpired, otherwise we refund, and a sweeper returns expired holds exactly once. Product pages read a cache; reservations hit the primary. The cost is contention on a hot item, so for launches I'd split its stock into a few bucket rows and probe them from a random start, accepting that multi-unit orders then need a transaction across rows."