System Design Interview: An Inventory Reservation Service That Never Oversells
12 views
"Design the inventory service for an online store so that we never sell more units than we have" is a system design prompt that looks small but tests consistency, concurrency and failure handling. Here is a structure you can use in a 45-minute interview.
1. Clarify requirements
Functional: show available stock on product pages, reserve units when a shopper starts checkout, confirm the reservation when payment succeeds, release it when payment fails or the shopper walks away, and accept restocks from warehouses. Non-functional: no overselling (strong correctness on the write path), product pages can tolerate a few seconds of staleness, and checkout latency should stay under roughly 200 ms at peak. Ask about scale: say 50 million SKUs, 5,000 checkouts per second at peak, and a small set of very hot items.
2. Core data model
Keep one row per SKU per warehouse: sku_id, warehouse_id, on_hand, reserved, version. Available = on_hand - reserved. Add a reservations table: reservation_id, order_id, sku_id, warehouse_id, quantity, status (HELD, CONFIRMED, RELEASED), expires_at. The reservation record is what makes the system auditable and lets you recover from crashes.
3. The reserve operation
The heart of the design is an atomic conditional update:
UPDATE stock SET reserved = reserved + :q
WHERE sku_id = :s AND warehouse_id = :w
AND on_hand - reserved >= :q
If one row changed, insert the HELD reservation in the same transaction; if zero rows changed, the item is out of stock. This avoids read-then-write races without long locks. Mention optimistic concurrency with the version column as an alternative, and explain why the conditional update is simpler here.
4. Expiry and release
Shoppers abandon carts constantly. Give each hold a TTL (for example 10-15 minutes). A sweeper job, or a delayed message per reservation, releases expired holds by decrementing reserved. Make release idempotent: only act if status is still HELD, so a late payment confirmation and an expiry cannot both apply.
5. Hot SKUs
A limited sneaker drop sends thousands of writes to one row. Options: split the stock into N buckets (sub-rows) and reserve from a random bucket, falling back to others; or front the hot item with a single-threaded in-memory counter (for example a Redis Lua script) and write reservations through to the database asynchronously. Be explicit about the trade-off: the in-memory approach is faster but needs a reconciliation job and careful recovery.
6. Read path
Product pages read available counts from a cache or read replica, refreshed by change events from the stock table. Show "only 3 left" from the cache, but always enforce the real check at reserve time. Interviewers like hearing that the displayed number is advisory.
7. Integration with orders and payments
Use the reservation_id as an idempotency key across services. The order service calls reserve, then payment, then confirm. If payment times out, do not guess: query the payment provider, or let the hold expire. An outbox table guarantees that stock change events reach downstream systems even if the service crashes mid-request.
8. Failure scenarios to mention
Double-click checkout (idempotency key), database failover during a reservation (transaction either committed or not; client retries with same key), and warehouse counts drifting from reality (periodic cycle counts feed adjustments).
Closing
Summarize: atomic conditional updates for correctness, holds with TTL for abandoned carts, bucketing or in-memory counters for hot items, and cached reads for scale. If you freeze when an interviewer pushes on concurrency, a tool like Linkjob AI (https://linkjob.ai), which gives real-time help during live job interviews and technical interviews, can prompt the next layer of the design so you keep the conversation moving.