Case Study 1 — Overselling the Last Ticket

The lost-update race, in production. A ticketing system sold more tickets than existed because concurrent purchases each read the same remaining count before either decremented it. Transactions (Chapter 26) gave atomicity; this needed concurrency control on top.

Background

An event platform sold tickets. The "buy" code, simplified, did a read-modify-write:

-- (1) check availability
SELECT remaining FROM events WHERE event_id = 42;     -- say it returns 1
-- (application: remaining > 0, so proceed)
-- (2) record the sale and decrement
INSERT INTO tickets (event_id, buyer_id) VALUES (42, :buyer);
UPDATE events SET remaining = remaining - 1 WHERE event_id = 42;

Each purchase even ran inside a transaction (atomic — good). But for a popular event, two buyers clicked "buy" at nearly the same instant for the last ticket, and both transactions read remaining = 1 before either decremented. Both passed the remaining > 0 check, both inserted a ticket, and remaining went to 0 — having sold two tickets for one seat. The event oversold, and staff had to apologize and refund. Under load, this happened repeatedly.

What went wrong: a lost-update race

Atomicity (the transaction) ensured each purchase's insert+decrement happened together — but it did not prevent two purchases from interleaving:

   T1: SELECT remaining → 1
   T2: SELECT remaining → 1        ← both read 1 before either decrements
   T1: INSERT ticket; UPDATE remaining = 0; COMMIT
   T2: INSERT ticket; UPDATE remaining = -1 (or 0); COMMIT   ← oversold!

This is the lost-update / read-modify-write race (this chapter). The decision ("is a ticket available?") was made on a value that another transaction changed before this one acted. Transactions alone don't fix it — MVCC let both reads see remaining = 1 from their snapshots. You need concurrency control to serialize the conflicting purchases on that row.

The fixes

Fix 1 — Pessimistic lock (SELECT ... FOR UPDATE). Lock the event row while checking and decrementing, so the second buyer waits and then sees the updated count:

BEGIN;
SELECT remaining FROM events WHERE event_id = 42 FOR UPDATE;   -- lock the row
-- T2 blocks here until T1 commits, then re-reads remaining = 0 and aborts the sale
INSERT INTO tickets (event_id, buyer_id) VALUES (42, :buyer);
UPDATE events SET remaining = remaining - 1 WHERE event_id = 42;
COMMIT;

Now the two purchases are serialized on the event row: T2 can't read remaining until T1 commits, by which point it's 0, and T2 correctly refuses the sale.

Fix 2 — Atomic, guarded decrement (often cleaner). Make the check and decrement a single conditional statement, and confirm it affected a row:

UPDATE events SET remaining = remaining - 1
WHERE event_id = 42 AND remaining > 0
RETURNING remaining;
-- if 0 rows returned, there was no ticket to sell → don't insert, tell the buyer "sold out"

Because the UPDATE locks the row for its duration and the remaining > 0 condition is evaluated atomically with the decrement, only one of two concurrent attempts on the last ticket can succeed; the other affects 0 rows and is told "sold out." No oversell, no explicit FOR UPDATE.

The team used Fix 2 for the decrement (simple and race-free) plus the ticket insert in the same transaction, gated on the UPDATE having succeeded.

The analysis

  1. Atomicity ≠ concurrency control. The purchase was already a transaction (insert+decrement together), yet still oversold. Atomicity bundles your steps; it doesn't stop another transaction from interleaving. Preventing the race needs locking or an atomic guarded write.

  2. The lost update hides in read-modify-write. "Read remaining, decide, then write" is the danger pattern. Anywhere you read a value into the application, branch on it, and write back, two concurrent runs can both act on the stale value.

  3. Two good fixes, by style. Pessimistic (FOR UPDATE) locks the row so others wait — simple, correct, blocks on hot rows. Atomic guarded UPDATE ... WHERE remaining > 0 does the check-and-act in one race-free statement — often the cleanest for counters/inventory. Both serialize the conflict.

  4. Check the affected-row count. With the atomic-decrement approach, the signal that you got the last ticket (or didn't) is whether the UPDATE affected a row. Always act on that count, not on a prior SELECT.

  5. It only appears under concurrency. Single-user testing never oversells; the bug needs two near-simultaneous buyers. Load-test concurrent paths, and reason explicitly: "what if two of these run at the same instant?" (the recurring question of Chapters 13, 26, 27).

Discussion questions

  1. The purchase was already atomic. Why did it still oversell?
  2. Draw the interleaving of two purchases that produces the oversell.
  3. How does SELECT ... FOR UPDATE prevent it? How does the atomic UPDATE ... WHERE remaining > 0?
  4. Why must you check the affected-row count with the atomic approach?
  5. ⭐ Compare the two fixes for a very hot event (thousands of simultaneous buyers). Which scales better, and why?