Notes
Listen
Listen. Product pages show whether an item is in stock. At checkout we hold the stock, and release it if the shopper does not pay. Orders can be placed, cancelled and returned. Stock sits in many warehouses, so each order is assigned to one, or split. Shoppers track their order's status. And the physical world changes stock too: deliveries from suppliers, damaged goods and manual counts. Pricing, search and the payment system itself are out of scope.
The qualities split in two. The number on the product page is only a hint, so it can be a few seconds stale and served from cache. The decision at checkout must be exact: reserved stock can never exceed what is physically on hand. Orders and stock live in different services but must agree within minutes, even after crashes. Checkout stays under three hundred milliseconds during a sale at twenty times normal load, and every stock change is auditable.
- show in stock / low / sold out
- reserve stock at checkout,
- release if unpaid
- place, cancel and return
- orders
- many warehouses; pick one
- per order or split it
- track order status for users
- restocks, damage and counts
- update stock
- no oversell: reserved can never
- exceed on hand, per warehouse
- product page stock: may lag a
- few seconds (it is a hint)
- checkout stock: exact, strong
- order and stock agree within
- minutes, even after crashes
- checkout p99 < 300 ms
- survive a sale: 20× normal
- full audit of every change
Ask scale
Scale. Two million orders on a normal day, ten million on a sale day: about a hundred fifteen a second on average, and around two thousand three hundred a second at the peak minute. With two and a half lines per order, that is under six thousand stock reservations a second, which sharded SQL handles. Stock rows total about forty gigabytes. Reads are much bigger: a hundred thousand availability checks a second from product pages. The real risk is one hot item.
Here is the number that drives a deep dive. A conditional update holds a row lock for about two milliseconds, including the commit, so a single row tops out near five hundred updates a second. A hot deal on one product at two thousand reservations a second is four times over that limit, and checkouts would queue and time out. So we will need to split hot stock across several rows, or absorb the burst in memory first.
orders: 2M/day normal sale day 10M/day ≈ 115/s avg peak ×20 ≈ 2,300 orders/s × 2.5 lines ≈ 5,800 holds/s stock rows: 50M SKUs, sparse ≈ 200M (sku, warehouse) rows × 200 B = 40 GB page views: 1B/day ≈ 12k/s peak ≈ 100k/s availability reads hot SKU: 2,000 holds/s on 1 row
a locked update holds the row ≈ 2 ms (update + commit) → max ≈ 500 updates/s per row deal on one SKU: 2,000 holds/s → 4× over, queues and timeouts options: split stock into N sub-rows pre-deduct in Redis, then SQL (see the flash sale problem)
Define API
The API. Availability takes product ids and a delivery area and returns stock status with a delivery estimate. Reservations are created for an order's lines, return the chosen warehouse and an expiry, and are later committed or released. All of these are idempotent, keyed by the order id. The order endpoints take an idempotency key. Warehouses report stock movements, such as received or damaged, through their own endpoint.
GET /v1/availability?skus=a,b
&zip=560001 → in stock, ETA
POST /v1/reservations
{ order_id, lines, zip }
→ { id, warehouse, expires }
POST /v1/reservations/{id}/commit
POST /v1/reservations/{id}/release
POST /v1/orders Idempotency-Key
POST /v1/orders/{id}/cancel
POST /v1/stock/movements (WMS) Data model
Data model, all relational. The stock table has one row per product per warehouse with on hand and reserved counts; available is the difference. Each hold is a reservation row, unique per order line, with a state and an expiry. Every physical change is an append-only movement with a reason and a reference, which is our audit trail. Orders carry a state and a version, plus an outbox table for events. Shard stock by product id.
stock(sku, wh, on_hand, reserved, version) PK (sku, wh) available = on_hand - reserved reservations(id, order_id, line, sku, wh, qty, state, expires_at) UNIQUE (order_id, line) movements(id, sku, wh, delta, reason, ref, at) append only orders(id, user, state, version) order_lines, outbox(event, sent)
High-level design
Trace a checkout. The order service creates the order in a pending state, then asks the inventory service to reserve each line. Inventory picks a warehouse, holds the stock with a fifteen-minute expiry, and returns. The order service then asks payments to authorize the amount. On success it commits the reservation and marks the order confirmed. If any step fails, it releases what was held.
After confirmation, the rest is driven by events. The order service publishes order confirmed through its outbox. The warehouse system for the chosen site picks and packs the items and books a carrier. When the parcel ships, a shipped event comes back. Inventory then turns the reservation into a real deduction: on hand goes down and reserved goes down by the same amount. The order service moves the order to shipped.
Deep dive: no oversell
Deep dive one: never oversell. Do not read the available count and then write, because two buyers can both read one and both succeed. Instead, do it in one conditional update: add to reserved only where on hand minus reserved is still at least the quantity. The database row lock serialises the two buyers; one updates a row, the other updates zero and is told it is sold out. Insert the reservation in the same transaction, unique per order line, so retries are harmless.
BEGIN UPDATE stock SET reserved = reserved + :q WHERE sku = :s AND wh = :w AND on_hand - reserved >= :q -- 0 rows → not enough stock INSERT reservations (...) ON CONFLICT (order_id, line) DO NOTHING -- idempotent COMMIT
Deep dive: when to hold
Deep dive two: when to hold stock. At add to cart is too early: most carts are abandoned, and stock would sit locked. After payment is too late: you might take money for something already gone. The usual answer is at checkout, with a short expiry, about fifteen minutes. An expiry sweeper runs every thirty seconds and, in one transaction, releases expired holds and gives the units back. If a payment confirms after its hold expired, try to reserve again, or void the payment.
- at add-to-cart: most carts are
- abandoned → stock locked up
- after payment: paid, then
- sold out → refunds, angry
- at checkout + 15 min expiry:
- short, honest hold (chosen)
- sweeper every 30 s:
- expired HELD → RELEASED,
- reserved -= qty, in one txn
- payment late? re-reserve or void
Deep dive: hot items
Deep dive three: the read flood and hot items. Product pages read availability from a cache that is updated from stock change events, so a hundred thousand reads a second never touch the database. For a hot product, split its stock into, say, ten rows of a hundred units each. A reservation picks a random bucket and tries the next if it is empty. Ten rows give ten times the write capacity, around five thousand holds a second. For extreme drops, add an in-memory gate in front, as in the flash sale problem.
- pages read the cache: 100k/s,
- updated from stock changes
- (show "only 3 left" loosely)
- hot SKU: split 1,000 units into
- 10 rows of 100 (sub-buckets)
- reserve from a random bucket,
- try the next one if empty
- → ~5,000 holds/s capacity
- extreme drops: Redis pre-check
- then the same SQL update
Deep dive: warehouses
Deep dive four: warehouses. At reservation time, list the warehouses that can deliver to the shopper's area and score them by delivery time, shipping cost, stock depth and how busy each one is. Try the best one; if the conditional update returns zero rows, try the next. If no single site has every line, decide by policy whether to split the shipment or wait. Keep a small safety stock for channels where stock syncs slowly, such as external marketplaces.
- candidates: warehouses that
- serve the delivery zip
- score: distance (ETA), cost,
- stock depth, current backlog
- try best first; if 0 rows,
- try the next one
- no single site has all lines:
- split shipment vs wait
- (rule per category)
- safety stock for slow syncs
Deep dive: order states
Deep dive five: the order state machine. Pending, reserved, paid, confirmed, picking, packed, shipped, delivered. Cancellation is allowed only before shipping, and it releases stock and voids the payment. Returns go from delivered to return requested to returned, and stock comes back only after inspection. Every transition is a conditional update on the current state and version, so a duplicate or late event cannot move an order backwards.
PENDING → RESERVED → PAID → CONFIRMED → PICKING → PACKED → SHIPPED → DELIVERED cancel: any state before SHIPPED → CANCELLED (release, void) DELIVERED → RETURN_REQUESTED → RETURNED (restock if OK) UPDATE orders SET state = :new, version = version + 1 WHERE id = :id AND state = :old
Deep dive: consistency
Deep dive six: keeping order and stock consistent without a distributed transaction. Checkout is a saga: a series of local transactions, each with an undo. Reserve pairs with release, authorize with void. If payment is declined, the order service releases the hold. Each service writes its state change and an outbox event in one transaction, and consumers deduplicate by event id. Two safety nets: an expired hold frees stock even if a release was lost, and a nightly job checks reserved counts against open holds.
- no distributed transaction:
- a saga of local transactions
- each step has an undo:
- reserve ↔ release
- authorize ↔ void
- state + outbox in one txn,
- consumers dedupe by event id
- nets: hold expiry frees stock
- even if release is lost
- nightly: reserved = open holds
Deep dive: real stock
Deep dive seven: the physical world. The database is only a model of the shelves, and the two drift: items get damaged, lost or miscounted. Every change is recorded as a movement: received against a purchase order, shipped against an order, damaged, returned. On hand is updated in the same transaction. When a cycle count finds four hundred ninety-seven instead of five hundred, we add an adjustment of minus three, never silently overwrite. On hand can always be rebuilt from the movements.
- every change is a movement:
- +500 received (PO 77)
- -2 shipped (order 881)
- -1 damaged, +1 returned
- on_hand updated in the same
- txn as the movement
- cycle count finds 497, not 500
- → adjustment movement -3
- rebuild on_hand from movements
- any time; they must agree
Trade-offs
Review the trade-offs. We are strongly consistent exactly where money and stock meet, the reservation, and eventually consistent everywhere else, like product pages. Holding stock per warehouse is precise but costs more retries than one pooled national number. A saga keeps services independent at the price of brief mismatches, covered by expiries and reconciliation. Monitor oversells, which must be zero, expired holds, orders stuck in one state, and reconciliation differences.
- exact SQL for holds; cached,
- stale counts for pages
- per-warehouse holds: exact,
- but more retries than one
- pooled national number
- saga: services stay separate,
- brief windows of mismatch
- stock DB shard down → those
- SKUs show unavailable
- monitor: oversells (must be 0),
- expired holds, stuck orders
Interview summary
In the interview: stock is on hand minus reserved, per product and warehouse. One conditional update reserves stock, so overselling is impossible. Hold at checkout with a fifteen-minute expiry. Serve pages from a cache and split hot items across rows. Choose the warehouse when reserving. Move orders through a versioned state machine, keep order and stock in step with a saga and outbox events, record every movement, and reconcile nightly.
- available = on_hand - reserved
- conditional UPDATE → no oversell
- hold at checkout, 15 min expiry
- cache for pages, split hot SKUs
- warehouse picked at reserve time
- order state machine, versioned
- saga: reserve/release,
- authorize/void, outbox events
- movements ledger + cycle counts
- nightly reconciliation