Consistency and Latency Trade-off in a Multi-Region Inventory Service
An online retailer operates a single-primary inventory database in us-east with read replicas in eu-west and ap-southeast. Peak load is 18,000 orders/min; 85% of traffic is reads. During a flash sale, EU customers purchased 1,200 units that were already out of stock because replica lag reached 2.4 seconds and the application read stale inventory. The business requires local p99 read latency under 100 ms and zero oversell. Engineering proposes a multi-primary topology with last-write-wins conflict resolution to reduce latency. The platform team must decide whether to accept that proposal, and if not, design an alternative that preserves both correctness and latency. The database supports conditional writes, quorum reads/writes, and change data capture. There is no budget for a full active-active deployment in all three regions, but a per-SKU home-region model is under consideration.
1. Evaluate the proposed multi-primary last-write-wins design against the zero-oversell requirement. Explain the failure mode and the minimum consistency mechanism you would require.
Read the worked answer
Last-write-wins (LWW) resolves concurrent writes by picking a timestamp winner and discarding the loser. That is exactly the wrong primitive for inventory. If two regions each read stock=1 and both decrement to 0, both writes can be accepted locally; LWW later keeps one and silently drops the other. Both customers may receive an order confirmation, but only one unit existed. Clock skew and write skew make this worse, and LWW cannot enforce the invariant stock >= 0 because it resolves conflicts after the fact.
Minimum mechanism: reject LWW for the stock counter. Require an atomic conditional write (compare-and-set) against an authoritative replica set with quorum write concern and read concern majority, or route all writes for a SKU to a single home region that uses consensus or a lease. The write must fail if the expected version or remaining quantity does not match. Add idempotency keys so retries do not double-decrement, and a short reservation/lease so stock is held during checkout. For display reads, eventual consistency is acceptable only if the checkout path performs the authoritative conditional write. The decision should be: no multi-primary LWW for inventory; use per-SKU single-writer or quorum conditional writes.
2. Design an architecture that meets the <100 ms p99 local read latency and zero-oversell goals. Specify write routing, read strategy, and conflict handling.
Read the worked answer
Partition SKUs into home regions. All stock writes for a SKU go to its home region's three-replica quorum across availability zones. The checkout service calls the home region with a conditional decrement and a reservation TTL; if the conditional write fails, return out-of-stock or retry after a fresh read. This gives zero oversell without requiring global active-active writes.
For reads, serve product display and search from local read models populated by change data capture. Use a short local cache TTL and bound staleness; publish a staleness budget. For cart and checkout, use read-your-writes by routing to the home region or by passing a version token from the reservation. If replication lag exceeds the budget, degrade gracefully: show "check availability at checkout" rather than promising stock. Hot SKUs can shard the counter into buckets and sum them for availability, but the conditional decrement must still be atomic per bucket. Conflict handling is not LWW: retries re-read the current version, and conflicting writes are rejected rather than merged. If the home-region quorum is unavailable, fail closed for that SKU instead of accepting a local write that cannot be reconciled.
3. List the operational metrics and alerts you would add to detect oversell risk before customers are affected.
Read the worked answer
Add metrics per region and per SKU class: replication lag seconds, conditional-write conflict rate, reservation expiry rate, failed reservation ratio, cache hit ratio, p99 local read latency, quorum write latency, and stock version mismatch rate. Also track any negative stock value as a hard invariant violation.
Alert on leading indicators: replication lag above 1 second for 1 minute on hot SKUs; conditional-write conflict rate above 5% for 5 minutes; reservation failures above 2% for 5 minutes; p99 read latency above 100 ms for 10 minutes; and any detected negative stock immediately. Add a synthetic checkout canary per region that reserves and releases a test SKU every minute. Route alerts to the inventory owner, not a general queue, and include the affected SKU class and region. These alerts detect the conditions that caused the 1,200-unit oversell before customers complete checkout.