Industrial Engineer Information Processing: Overselling a Flash Sale: Where the Invariant Must Live

Overselling a Flash Sale: Where the Invariant Must Live

Northline runs flash sales on a single MySQL 8.0 primary with six application pods behind a load balancer. The stock reservation code executes three steps: SELECT quantity FROM stock WHERE sku = ?, a Java check that quantity is at least the requested amount, then UPDATE stock SET quantity = quantity - ? WHERE sku = ?. The session isolation level is READ COMMITTED and each pod holds a pool of 20 connections. In the last sale, 500 concurrent requests hit a SKU holding 200 units and 207 orders were created. Separately, 1,300 of 9,000 client requests timed out at 3 seconds and the mobile client retried them, producing duplicate orders for the same customer and cart. One engineer proposes wrapping the reservation method in a Redis distributed lock; another proposes a synchronized block. The product owner wants a fix before the next sale in nine days.

1. Explain precisely why 207 orders were created for 200 units, and why neither the `synchronized` block nor a larger connection pool addresses it.

Read the worked answer

This is a check-then-act race, not a database isolation failure.

The predicate that protects the invariant (quantity >= requested) lives in Java, so the database never evaluates it. Under READ COMMITTED the SELECT reads the latest committed value and takes no lock. Many transactions read quantity = 5, all pass the Java check, and then each UPDATE queues for the same row lock and decrements unconditionally. The row goes negative; the application has already committed to the order.
synchronized guards only one JVM's heap. Six pods means six independent locks, so five pods still oversell. It also serializes every SKU behind one monitor, collapsing throughput to one thread.
Enlarging the pool makes it worse: more transactions in flight means more concurrent readers of the same stale value.
The correct mental model is that stock is a shared invariant, so the enforcement point must be the only component all writers share - the database row.

2. Choose the fix for the single-primary case and compare it against the Redis lock proposal, including failure modes.

Read the worked answer

Collapse the read and the decrement into one atomic statement the database evaluates.

UPDATE stock SET quantity = quantity - :n WHERE sku = :sku AND quantity >= :n, then check the affected-row count: 1 means reserved, 0 means sold out. Run it in the same transaction as the order insert.

One statement is atomic, takes the row X-lock, and re-evaluates the predicate while holding it, so no negative stock is possible regardless of pod count or isolation level. Contention is a single row for 200 units, which is not a bottleneck.
Optimistic version columns are also correct, but under 500-way contention they produce retry storms on the same row; retries multiply load exactly when the system is hottest.
Redis lock failure modes: the lock can expire while a holder is paused (GC, slow query), giving two holders; correctness then requires a fencing token or a TTL far beyond worst-case latency; it also fails open or closed depending on how you handle Redis unavailability; and it protects nothing against a future writer that skips the lock. Reserve it for coordination across services or stores.
Add CHECK (quantity >= 0) as a backstop so a future bug raises an error instead of silently corrupting data.

3. Design the duplicate-order fix. Why is a Redis SETNX or an in-memory set insufficient?

Read the worked answer

Duplicates come from retries, so the fix must be idempotency, not locking.

The client generates one UUID idempotency key per checkout attempt and reuses it across all retries.
The server stores (customer_id, idempotency_key) under a UNIQUE constraint alongside the resulting order id and the serialized response, written in the same transaction as the order. On a repeat, the insert fails on the duplicate key, the server reads back the stored response, and returns it verbatim.
If the first attempt is still in flight, return 409 with a retry-after rather than creating a second order.
Same key with a different payload is a client bug: return 409/422 and never silently honour the new body.
A SETNX with a short TTL reopens the window the moment the key expires or Redis restarts, and an in-memory set dies with the pod - and the retry may land on a different pod anyway. Only a durable, unique, transactionally written record survives restarts, deployments, and load-balancer reshuffling.