Ticket On-Sale: Double-Sold Seats Under Read Committed Isolation
An arena with 12,000 seats puts a concert on sale at 10:00. About 40,000 users are queued and the booking service peaks at 3,000 requests per second. The booking transaction does the following: it issues SELECT status FROM seats WHERE event_id = ? AND seat_id = ?, checks in application code that the status is 'free', and then inserts a row into bookings and updates the seat to 'sold'. The database runs at READ COMMITTED. Average transaction latency is 40 ms and the connection pool is capped at 200 connections. The post-mortem finds 214 seats sold twice within a 30-second window, all during the peak; no duplicates occur in the quiet period an hour later. An engineer proposes switching the whole booking path to SERIALIZABLE; a load test indicates that 8% of booking transactions would then abort with serialisation failures and need a retry.
1. Identify the anomaly and show, with an interleaving, why READ COMMITTED permits it. Explain why it disappears outside the peak.
Read the worked answer
The anomaly is a check-then-act race - a lost update implemented as a phantom on the seats predicate. Two sessions for the same seat can interleave as follows:
- T1: SELECT status for seat 5001 → 'free'.
- T2: SELECT status for seat 5001 → 'free' (T1 has written nothing yet, so nothing blocks or is visible).
- T1: INSERT booking, UPDATE seat → sold; COMMIT.
- T2: INSERT booking (a different row, so no uniqueness conflict on a surrogate key), UPDATE seat → sold; COMMIT.
READ COMMITTED only guarantees that a statement sees committed data as of its start; it takes no predicate locks and does not prevent T2 from reading the same 'free' value that T1 read before committing. The write in step 4 overwrites the value T1 wrote, and both bookings exist: two customers, one seat. Neither the SELECT nor the UPDATE raises an error, so the application never learns it lost.
Why only at peak. The window requires T2's SELECT to fall between T1's SELECT and T1's COMMIT, i.e. within roughly the 40 ms transaction. At 3,000 requests per second and very high contention on popular seats, collisions are frequent; in the quiet period, requests for the same seat are seconds apart and the window is essentially never hit. Contention, not code, changed.
2. Compare two fixes - a UNIQUE constraint on (event_id, seat_id) plus retry, versus an atomic conditional UPDATE that reports affected rows - and recommend one for the 3,000 req/s peak.
Read the worked answer
Fix A: UNIQUE (event_id, seat_id) on bookings. The second insert fails with a constraint violation: correctness restored by the database, with no application locking. The cost is that the loser gets an exception, and the application must translate it into 'seat taken, choose another' rather than retry blindly on the same seat. Note that a unique key on a surrogate booking_id would not have caught this; the constraint must be on the natural key.
Fix B: one atomic statement. UPDATE seats SET status='sold' WHERE event_id=? AND seat_id=? AND status='free', then check the affected-row count (1 = you won, 0 = someone else did) before inserting the booking. The decision and the write happen in a single statement under a row lock, so the read-modify-write window that caused the anomaly is eliminated, and no retry loop is needed for the common case. The lock is held only on that seat's row, so unrelated bookings do not contend.
Recommendation. Fix B for the hot path, with Fix A as a belt-and-braces invariant: B removes the window and the retry storm, A guarantees that no future code path can ever double-sell. Keep transactions short and always touch seat rows in a consistent order (for example, ordered by seat_id for multi-seat baskets) to avoid deadlocks between concurrent baskets.
3. Size the SERIALIZABLE alternative: compute the throughput ceiling at 40 ms per transaction, the retry load at an 8% abort rate, and judge whether it is safe to adopt this weekend.
Read the worked answer
Capacity. Each connection completes 1 / 0.040 = 25 transactions per second. With 200 connections, the ceiling is 200 × 25 = 5,000 tx/s. Peak demand is 3,000 tx/s, so steady-state utilisation is about 60% - enough headroom on paper.
Retry load. An 8% abort rate means every successful booking consumes 1 / (1 − 0.08) ≈ 1.087 attempts, so 3,000 bookings/s become about 3,261 transactions/s, i.e. 65% utilisation, with about 240 aborted transactions per second each requiring a fresh round trip and re-read. Because retries arrive on top of the original load, latency rises, which lengthens the conflict window and raises the abort rate further: the mechanism is self-reinforcing exactly when the system is busiest.
Judgement. Do not switch the whole path to SERIALIZABLE on the eve of an on-sale. It fixes correctness but converts tail latency into an availability risk, and the failure mode (retry amplification under flash-crowd load) appears precisely during the 10:00 peak that the change is meant to protect. Use the conditional UPDATE plus the unique constraint, which need no retries, and, if SERIALIZABLE is wanted later, apply it only to the narrow seat-allocation transaction, load-test at 1.5× peak, and cap retries with jittered backoff before retiring the current path.