Design a Flash Sale

Model answer

Tables: products; sale_items(sale_id, product_id, price, stock_total, stock_left, starts_at, ends_at, per_user_limit); orders(id, user_id, sale_item_id, quantity, status, amount, expires_at); and payments(order_id, gateway_ref, status).

API: GET /sales/{id} for the page; POST /orders to reserve a unit, which returns an order with an expiry; the payment gateway's callback to mark it paid; and GET /orders/{id} for its status.

An order moves from RESERVED to PAID to CONFIRMED, or from RESERVED to EXPIRED when the buyer does not pay in time, which returns the unit to stock. "One per customer" is a unique index on (sale_item_id, user_id) over orders that have not expired, because a check in code is something two requests can pass at once.

Model answer

Read-then-write oversells: two requests both read stock_left = 1, both decide one is left, and both create an order. The check and the decrement must be one atomic step.

In SQL, a conditional decrement in the same transaction as the order insert:

UPDATE sale_items SET stock_left = stock_left - 1
WHERE id = $1 AND stock_left > 0;
-- 0 rows changed: sold out. 1 row: insert the order, then commit.

In Redis, a Lua script that checks and decrements together, since Redis runs a script without letting other commands in between.

Reserve, then confirm: take the unit when the buyer reserves, hold it for a few minutes while they pay, confirm on payment, and put it back if the reservation expires. Taking stock only at payment would let 5,000 people reach the payment page for 1,000 phones.

Model answer

The update takes a row lock, so purchases of this item run one at a time. If each transaction holds the lock for about 5 ms, that row tops out near 200 purchases a second, while a million people click in the first minute. Requests queue for the lock, hold connections while they wait, and drag the whole database down.

• Move the counter to Redis: an atomic decrement in a Lua script handles tens of thousands a second, and SQL records only the winners.
• Or split the stock across rows: 10 rows of 100 units each, with each request picking one at random and trying another if it is empty. Ten rows, roughly ten times the throughput.
• Pre-issue tokens: load 1,000 tokens into a Redis list before the sale, and buying starts with LPOP. Once the list is empty, everyone else gets "sold out" straight away without touching the database.
• Put a waiting room in front of checkout that admits people at the rate checkout can handle.

Keep most of the crowd off your servers entirely: the product page on a CDN, with stock shown loosely ("selling fast"), rate limits per user, and bot checks (a captcha, one per account) before 12:00. Most people will get a no, so give it to them fast, from cache.

Model answer

Every reservation has a TTL (say 10 minutes, shorter during the sale). An expiry worker finds the expired ones, from a Redis sorted set scored by expiry time or an indexed expires_at column, and returns their stock.

Expiry and a late payment can race. Make each move a conditional state change: the worker runs UPDATE orders SET status = 'EXPIRED' WHERE id = $1 AND status = 'RESERVED' and returns the unit only if a row changed. Payment does the same from RESERVED to PAID. Exactly one of them wins.

The payment callback is idempotent, keyed by the payment id. If money arrives for an order that already expired and its phone was resold, refund automatically, or honour the order if stock remains.

Fairness: returned units go to the next people in the queue, in order, not to whoever refreshes fastest, and each user holds one reservation at most. The trade-off: shorter TTLs free stock sooner but fail buyers whose bank OTP is slow; taking payment before reserving avoids unpaid holds but turns every lost race into a refund.