Overselling Seats With a Per-Process Mutex
A small-venue ticketing service tracks inventory in a single database row, seats_remaining. The purchase path reads the row, checks remaining >= qty, then writes remaining - qty, with the whole read-check-write wrapped in an in-process mutex. The service runs as four replicas behind a load balancer, each with its own mutex instance. During a 90-second flash sale the service recorded 503 confirmed seats against 500 available, and the row now reads -3. Each of the 41 affected orders took 200-900 ms end to end, and p99 latency reached 4 s under a client retry storm. The database runs at READ COMMITTED. The team is choosing between three fixes before the next sale in ten days: (a) replace the in-process mutex with a shared lock in Redis, (b) wrap the read-check-write in a transaction that uses SELECT ... FOR UPDATE on the row, and (c) add a CHECK (seats_remaining >= 0) constraint and surface the resulting failures to users as sold-out errors.
1. Explain why the in-process mutex cannot prevent the oversell, and describe how fix (b) behaves at READ COMMITTED.
Read the worked answer
Why the mutex fails. A mutex synchronizes threads inside one interpreter. There are four replicas, so there are four independent mutexes and up to four concurrent critical sections. The shared state being protected lives in the database, so the mutual exclusion must live where the state lives. This is a mutual-exclusion-at-the-wrong-scope bug, not a threading bug.
What happens without a lock. At READ COMMITTED, a plain SELECT takes no lock and each statement sees a fresh snapshot. Two transactions can both read 1, both pass the check, and both write 0 - a textbook lost update. READ COMMITTED does not prevent this; only locking or an atomic write does.
How FOR UPDATE fixes it. The first transaction takes an exclusive row lock. The second blocks. When the first commits and the lock is released, READ COMMITTED re-reads the latest committed row before returning it, so the second transaction sees 0, fails its check, and rolls back correctly.
The catch this fix does not solve. Holding the row lock for 200-900 ms serializes every purchase on the venue: throughput collapses to roughly 1-5 orders per second, and the retry storm multiplies the contention. Fix (b) is correct, but it must be paired with short transactions (no network calls or logging inside), lock-wait timeouts, and bounded retries with jitter.
2. Evaluate fix (c): is the CHECK constraint a control, a backstop, or a mistake?
Read the worked answer
It is a backstop, and a valuable one - but it is not a control.
What it does well. It makes the invariant unbreakable at the storage layer. Whatever else goes wrong - a code path that forgets the lock, a manual UPDATE, a future refactor - the row can never go negative. As defence in depth it is exactly the kind of thing you want in place before a sale.
Why it cannot be the primary mechanism.
- It does not remove the race; it converts a lost update into a failed commit after the request has done all its work, often after payment authorization. The user is charged or pre-authorized and then told the seat is gone.
- It does not implement first-come-first-served. Whichever transaction commits last fails, regardless of arrival order, so a user who clicked first can lose to a user who clicked later.
- Under a retry storm, constraint failures generate more retries against an already saturated database.
- It gives you no record of which path oversold, so it can mask a regression that a loud exception would have exposed.
Correct framing. Constraint = last line of defence, monitored and alerted on (any constraint violation in production is a bug, not routine sold-out traffic). Primary control = an atomic conditional update or a properly scoped lock. Surfaces as sold-out only if the constraint fires, which should be never.
3. Given the 4 s p99 and the ten-day deadline, what design would you actually ship?
Read the worked answer
Primary mechanism: one atomic conditional statement.
UPDATE inventory SET seats_remaining = seats_remaining - :qty WHERE id = :id AND seats_remaining >= :qty
and treat rowcount = 1 as the success signal, rowcount = 0 as sold out. There is no read-then-write window at all, the lock is held for the duration of a single statement rather than 200-900 ms, and it works identically across all four replicas. It needs no new infrastructure and no new failure modes, which matters with ten days to go.
Idempotency. Require a client-supplied order key with a unique constraint, so the retry storm cannot decrement twice. Without this, the retry storm is the bug's delivery mechanism: the same order retried three times sells three seats.
Load control. Bound retries with exponential backoff and jitter, fail fast on rowcount = 0, and rate-limit or queue at the edge for the sale window. A 4 s p99 under retries is a queuing problem, and no database fix survives an unbounded retry multiplier.
On the Redis lock (a). It works, but it adds a distributed dependency and a lock-expiry failure mode: a stalled holder whose lease expires lets a second holder in. Only acceptable with a fencing token that the database itself validates - at which point you have reimplemented the conditional update with more parts.
Long term: seat-level rows with a unique constraint per seat, for auditability and genuine concurrency. Keep the CHECK constraint as the backstop throughout.