Ruankao E-Commerce Designer (Intermediate): Flash-Sale Oversell and Duplicate Shipments at an Appliance Retailer

Flash-Sale Oversell and Duplicate Shipments at an Appliance Retailer

On 11 November, an online appliance retailer processed 40,000 requests per minute at peak against two Java application servers, one MySQL primary and a single Redis cache. The checkout path read stock from MySQL, checked it in application code, then issued an UPDATE after payment authorization. A 5,000-unit air-conditioner SKU oversold by 1,200 units. Redis held a stock value that was up to three minutes stale because invalidation happened only after the database commit. The payment provider retried callbacks, and 640 orders were shipped twice; the callback handler had no deduplication. P99 latency reached 18 seconds, the error rate 22%, and customer service received 3,000 complaints. A post-incident review found no load test at 2× peak, no rate limiting at the API gateway, and a nightly reconciliation job that compared only totals, not individual reservations. The CTO wants a target design before the next campaign in ten weeks.

1. Diagnose the technical causes of the oversell and duplicate shipments. Distinguish symptoms from root causes.

Read the worked answer

Symptoms: 1,200-unit oversell, 640 duplicate shipments, 18-second P99, 22% errors, 3,000 complaints.

Root causes

Check-then-act race. Two requests read the same stock value, both pass the check, then both update. There is no conditional update such as UPDATE stock SET available = available - ? WHERE sku = ? AND available >= ?, no row lock and no atomic counter. The number of application servers is irrelevant; the race exists even with one server because requests interleave.
Cache used as a quasi-source of truth. Redis held a value but was not decremented atomically; invalidation after commit left a three-minute window. A cache read cannot enforce inventory unless the operation is atomic and the database remains the final gate.
No reservation boundary. Stock was decremented only after payment authorization, so unpaid or abandoned orders held no stock while paid orders could exceed it. Inventory must be reserved before payment, with a TTL and compensation.
Duplicate callbacks. The provider retries by design; the handler had no idempotency key and no unique constraint on provider transaction ID. The order state machine also allowed a paid order to transition to shipped twice.
Retries amplified load. Gateway and client retries without idempotency multiplied requests; no rate limit or queue.
Weak reconciliation. Comparing totals only hides individual duplicate reservations and shipments.

The oversell is not a 'Redis bug' or a 'MySQL bug'; it is a missing atomicity boundary at the point where stock is committed.

2. Design the target order-and-inventory flow for peak traffic, naming the consistency mechanism at each step and explaining why it prevents oversell.

Read the worked answer

Target flow

Pre-load sellable quantity into Redis as a counter per SKU. On order submission, run a Lua script that atomically checks and decrements; if the result would be negative, reject immediately with 'sold out'. Return a reservation ID and a TTL of about 15 minutes.
Persist the reservation asynchronously to a stock ledger table in MySQL. The ledger is authoritative; Redis is a fast admission gate. The database write uses a unique reservation ID and a conditional update on the stock row.
Create the order in PENDING_PAYMENT with the reservation ID. Do not call the payment provider until the reservation is durable, or use a short-lived pending record with a compensating release.
Payment idempotency. The payment request carries an idempotency key derived from the order ID. The provider callback is inserted into a payment_event table with a unique index on (provider, provider_event_id); duplicate inserts are ignored and the handler returns HTTP 200.
Guarded state transitions. Move PENDING_PAYMENT → PAID → ALLOCATED using updates with WHERE status = 'PENDING_PAYMENT'. On failure or TTL expiry, release the reservation and increment Redis and the ledger in one compensating transaction, itself idempotent.
Reconciliation. A job scans reservations older than TTL, compares the Redis counter with the ledger sum, repairs drift in the safe direction (reduce sellable quantity), and alerts when the absolute difference exceeds a threshold.

Why it prevents oversell: the only decrement that can authorize a sale is atomic, and the database conditional update is the final gate. A cache loss can cause undersell, which is safe, but not oversell if the database rejects negative stock. Duplicate callbacks cannot create a second shipment because the unique event and guarded transition make the operation idempotent.

3. The CTO asks whether to make Redis the source of truth for stock to survive 40,000 requests/min. Argue the trade-off and define a degradation and reconciliation plan.

Read the worked answer

Argument for Redis as source of truth: it can absorb 40,000 requests per minute with sub-millisecond latency and atomic Lua, avoiding database round trips during the spike.

Argument against: Redis persistence is asynchronous by default; a failover can lose recent decrements, and a cache is not a system of record for financial inventory. Making it authoritative turns a cache incident into an inventory incident, and reconciliation becomes guesswork. It also complicates audit and customer-service resolution.

Recommendation: keep Redis as the fast admission gate and MySQL as the authoritative ledger. Accept slight undersell during failure, never oversell. If Redis is unavailable, fall back to a database conditional update with a queue and rate limit; if the database is also saturated, switch to a virtual waiting room and static catalog rather than accepting orders.

Degradation plan

API gateway rate limits per account and IP; checkout requests queued.
Disable recommendations and search facets; serve catalog pages from CDN.
Circuit-breaker the payment provider; show 'limited availability' and a waiting room.
Pre-defined runbook for Redis loss, database failover and provider timeout.

Reconciliation plan

Every minute, compare Redis counters and ledger for hot SKUs; every 15 minutes for all SKUs.
Repair in the safe direction by reducing sellable quantity.
Keep an immutable reservation log with timestamps and order IDs.
Run a game day with Redis killed, database failover and provider timeout before the campaign, and load test at 2× peak.