Choosing Consistency for a Shared Inventory Counter
A retail platform runs inventory counts in a single relational database. During a flash sale, 3,000 units of one item are offered. The read path caches the count in memory for 5 seconds. The write path decrements the database row and then invalidates the cache. Under load, 4,100 orders were accepted for the 3,000 units, and 1,100 had to be cancelled. Investigation shows the decrement used a read-then-write pattern (SELECT count, then UPDATE with the value read) inside a transaction at the default isolation level. Two application nodes each hold their own cache. The business tolerates a few seconds of stale display but not overselling.
1. Identify the root cause of overselling and explain why the cache is or is not a contributing factor.
Read the worked answer
The root cause is the read-then-write decrement under an isolation level that permits lost updates. Two transactions can both read count = 500, both compute 499, and both write 499, so two units are sold but only one is deducted. The pattern is unsafe regardless of cache.
The cache is a secondary factor, not the cause. A 5-second stale display can show a higher number than reality and invite clicks, but the authoritative check happens at write time. If the write path were correct, stale reads would produce failed orders, not oversold ones. So the cache amplifies the symptom (more users attempt to buy) while the lost update creates the defect.
Fix the write path first: replace read-then-write with an atomic conditional update, e.g. UPDATE inventory SET count = count - 1 WHERE sku = ? AND count >= 1, and treat a zero-row result as sold out. This makes the check and the decrement one operation. Alternatively use SELECT ... FOR UPDATE or an optimistic version column with retry. The business requirement (no overselling, brief staleness acceptable) points to strong consistency on writes and eventual consistency on reads, which is exactly what the atomic conditional update provides.
2. The team proposes moving inventory to a distributed cache with a compare-and-set operation to reduce database load. Evaluate this against the stated requirements.
Read the worked answer
It addresses load but moves the consistency problem rather than solving it, and it risks violating the one hard requirement.
The requirement is asymmetric: stale display is tolerated for seconds, overselling is not tolerated at all. A distributed cache with compare-and-set can enforce atomicity per key, but durability becomes the question. If the cache is the source of truth and a node fails or a partition occurs, decrements can be lost or split-brained, and the cache must then be reconciled with the database. During reconciliation the system may have already accepted orders it cannot honor, which is overselling by another route.
A sounder design keeps the database as the authority for the decrement and uses the cache only for display. To cut database load, batch or pre-allocate: reserve blocks of units to each application node, decrement locally within the block, and reconcile blocks centrally. This reduces write contention while keeping a single authority that can refuse a sale. If the team still wants a distributed store, it must document the failure modes (partition, node loss, cache eviction) and show that each one cannot produce an accepted order without a unit. Absent that, the proposal trades a known defect for an unknown one.