Flash-Sale Overselling in a PostgreSQL Order Service
ShopLite is a Philippine online retailer running a three-tier application on PostgreSQL 14. The order service handles roughly 400 checkouts per minute during flash sales. Its stock update is written as: read the row with SELECT qty FROM stock WHERE item_id = ?; if qty >= ordered, compute new_qty in application code; then UPDATE stock SET qty = new_qty WHERE item_id = ?. During the 11.11 sale, one SKU sold 137 units although only 100 were on hand. Separately, a nightly reconciliation job running at REPEATABLE READ reported 12 units more than the customer-facing stock page showed at the same moment, and finance filed a ticket claiming the report is broken. After a new reservations table was added, the service began throwing deadlock errors under load: two transactions lock the stock table first and reservations second, while a newer promotion path locks them in the opposite order. The team has one 40-minute maintenance window and cannot take the catalog offline for longer.
1. Name the concurrency failure behind the 137-unit oversell and explain why raising the isolation level alone is a weak fix for a 40-minute window. State the code-level change you would ship and the retry behavior you would add for the deadlocks.
Read the worked answer
Failure class. This is a lost update: a read-modify-write race in application code. Sessions A and B both read qty = 1, both decide the order is acceptable, and both write 0. The check and the write are not atomic, so the invariant qty >= 0 is never enforced by the database.
Why isolation level alone is weak. Serializable isolation would make the conflict abort, but every caller would then need correct retry handling (SQLSTATE 40001). Shipping that everywhere in 40 minutes, with no test coverage, converts a data bug into user-visible checkout errors. The application-visible guarantee also depends on all writers using the same pattern, which is not currently true.
Minimal safe change. Replace the read-then-write with one conditional statement inside the order transaction:
UPDATE stock SET qty = qty - :n
WHERE item_id = :id AND qty >= :n;
Check the affected row count. If it is 0, roll back and return "insufficient stock". The single statement takes a row lock, so two sessions serialize and the second sees the updated value; the qty >= :n predicate makes the decrement self-guarding. Put the order insert and the stock decrement in the same transaction so a failed insert cannot leak a decrement.
Deadlocks. Standardize lock order: every path touching stock and reservations must lock stock first. Keep transactions short and avoid user think-time inside them. Then add bounded retries for 40P01 and 40001 (three attempts, exponential backoff with jitter), because even with consistent ordering, gap locks and index order can still produce deadlocks.
Report symptom. The reconciliation number is not a bug in the report; it reflects an MVCC snapshot taken when the job started. Long-running reads will disagree with the live page. Give the report an explicit "as of" timestamp, or run it against a replica with a documented lag tolerance.
2. Design a test that proves the old code oversells and the new code does not, and name two metrics you would watch in the first hour after deployment.
Read the worked answer
Test design. Seed a single SKU with qty = 10. Run 100 concurrent clients, each attempting to buy 1 unit, with a barrier so they fire together rather than in sequence. Assert: exactly 10 successes, qty ends at 0, qty is never negative at any observation, and 90 clients receive a stock error rather than a timeout or a 5xx.
Run the same harness against the old build first. It should reproduce the defect (typically far more than 10 successes); if it does not, the harness is not concurrent enough, and the load level or pool size must be raised until the bug appears. That reproduction is what gives the fix evidential value.
Add a second scenario with two item rows and two endpoints exercising stock and reservations in both orders, run for five minutes, to confirm the deadlock retry path terminates with a correct outcome rather than a silent partial write.
Metrics for the first hour.
- Oversold count - orders accepted where stock went negative, or successes exceeding initial quantity. The expected value is zero; anything else triggers rollback of the release.
- Conditional-update rejection rate and retry rate - how often the
qty >= :npredicate rejects, and how often transactions retry with40P01/40001. A sudden spike in retries signals hot-row contention on a single SKU; a spike in rejections with plenty of stock would signal a logic error.
Watch p99 checkout latency alongside these, because serializing on one hot row is the predictable cost of the fix and will show up as queueing rather than errors.