Overbooking Under Snapshot Isolation
A ticketing service stores one row per sold seat in Seat(event_id, seat_no), with PRIMARY KEY(event_id, seat_no). Event 7 has capacity 50 and currently contains exactly 49 rows, seats 1 through 49. Two sessions run concurrently at REPEATABLE READ, which this database implements as snapshot isolation. T1 executes SELECT COUNT(*) FROM Seat WHERE event_id = 7 and sees 49. T2 executes the same query and also sees 49. T1 inserts seat 50 and commits. T2 inserts seat 51 and commits. Both transactions succeed, and the table now has 51 rows for event 7. The application checks capacity only through the COUNT query; there is no counter row and no database CHECK constraint referencing other rows. The team wants to know whether the execution is serializable and what minimal change prevents the overbooking without switching the whole database to SERIALIZABLE.
1. Is the execution serializable? Construct the possible serial orders and show why neither matches the observed outcome.
Read the worked answer
No. In any serial order, the second transaction would run after the first committed. If T1 precedes T2, T2's COUNT sees 50 rows and the application's capacity check rejects the insert. If T2 precedes T1, T1 sees 50 and rejects. The observed outcome has both transactions reading 49 and both committing, producing 51 rows, which matches neither serial order. The anomaly is write skew: the two transactions read the same predicate but write different rows (seats 50 and 51), so snapshot isolation sees no write-write conflict and permits both. The primary key does not help because the inserted keys are distinct. Serializable execution would require the read of the count and the insert to be atomic with respect to the other transaction's insert, which snapshot isolation does not provide for this predicate.
2. Would a UNIQUE constraint on (event_id, seat_no) prevent this overbooking? Would SELECT ... FOR UPDATE on the 49 existing Seat rows prevent it? Explain.
Read the worked answer
The UNIQUE constraint prevents duplicate seat numbers, but here T1 inserts seat 50 and T2 inserts seat 51, which are distinct, so it does not prevent the 51st row. SELECT ... FOR UPDATE on the 49 existing Seat rows also fails in the general case: both transactions can lock the same existing rows, but neither locks a row for the not-yet-existing seat 50 or 51. After T1 commits, T2's locking read may still be based on its snapshot, and it inserts a new row outside the locked set. This is a phantom problem. A range or gap lock on the event's seat index might block the insert in some engines, but that behavior is engine-specific and not portable. The reliable fix is to serialize on a row that both transactions must update, such as the event row or a counter row.
3. Recommend a minimal schema or application change that enforces the capacity under snapshot isolation. Give the critical SQL and explain why it works.
Read the worked answer
Add a counter to the event row: Event(event_id, capacity, seats_taken). Then perform a conditional atomic update in the same transaction as the seat insert: UPDATE Event SET seats_taken = seats_taken + 1 WHERE event_id = 7 AND seats_taken < capacity; check that exactly one row was affected; if zero, abort with 'sold out'; otherwise insert the Seat row and commit. The UPDATE takes a row-level write lock on the event row. The second transaction blocks on that lock, and after the first commits it re-evaluates the predicate against the new seats_taken value, so it sees 50 and affects zero rows. This serializes only the capacity decision, not the whole database. An equivalent alternative is SELECT ... FOR UPDATE on the event row before counting, but the conditional UPDATE is usually shorter and avoids a separate read.