Two people buy the last concert ticket

01One ticket left

The page says 1 ticket left. Linh and Minh, two strangers in two cities, both press Buy in the same few milliseconds. Both requests ask the database how many tickets remain, both hear “one”, both think “mine”, and both write down a sale. The concert hall seats a crowd, the box office had a single ticket, and two people now hold a confirmation email for it.

I met this exact bug first as a story, in Chapter 7 of Martin Kleppmann's Designing Data-Intensive Applications (first edition), and then in production-shaped form in the clinic booking post, where one conditional UPDATE ended it. That post answered “what do I type?”. This one answers the question I skipped: what is this bug actually called, and which database guarantees make it go away? The interesting discovery is that the name is not a property of the bug. It is a property of how I stored the tickets.

buyers per ticket

2 : 1

data models, 2 different bug names

2

ways to fix a counter

4

the SQLSTATE you must learn to retry

40001

02ACID, read the fine print

Everyone can recite ACID. Few of us read what each letter really promises, and Kleppmann is blunt that the word means different things on different databases. Four notes that matter for tickets:

• Atomicity is not about concurrency at all. It means abortability: if a transaction fails halfway, every write it made is thrown away. Two threads racing is the job of the next letter.
• Consistency is your responsibility, not the database's. “Tickets sold never exceeds capacity” is an application invariant. The database can only enforce the pieces you declare (CHECK, UNIQUE, foreign keys); the rest depends on you using transactions correctly. Kleppmann repeats the old joke that the C was tossed in to make the acronym work.
• Isolation is the one that bites. In theory it means transactions behave as if they ran one after another (serializability). In practice, most databases quietly default to something weaker, and the weaker levels have names and bugs of their own.
• Durability means committed data survives a crash, usually via a write-ahead log, sometimes via replication. It is a promise with a probability attached, not an absolute.

There is one more distinction: a single-object write (increment one counter) versus a multi-object transaction (insert a ticket row and update the stock and write an order). Databases give you cheap atomic guarantees on one row, which will turn out to be the escape hatch for the counter.

03What your transaction actually sees

Picture a ledger in a box office with a clerk who reads it aloud over a phone. At read committed, every sentence the clerk reads is up to date: each statement sees only data committed before that statement began, and never sees someone else’s half-finished work (no dirty reads). But two sentences within the same transaction can disagree, because the world moved between them. That is Postgres’s default, and it is why “read, think, then write” has a hole in it.

At snapshot isolation the clerk instead hands you a photocopy of the ledger at the moment you walked in, and you read only the photocopy until you leave. Databases build this with MVCC (multi-version concurrency control): each write creates a new version of a row instead of overwriting the old one, and each transaction sees only the versions that were committed before its snapshot. The motto in the book is that readers never block writers and writers never block readers. Postgres calls this level REPEATABLE READ; so does MySQL, but the two behave differently in ways that matter below.

04Model A: a counter, and the lost update

The first model is the one a student would write: one row per event with a tickets_left column. Buying means “read it, if above zero subtract one, write it back”. Here is a schema you can paste into psql:

CREATE TABLE events (
  id           int PRIMARY KEY,
  name         text NOT NULL,
  tickets_left int  NOT NULL CHECK (tickets_left >= 0),
  version      int  NOT NULL DEFAULT 0
);
CREATE TABLE orders (
  id       bigserial PRIMARY KEY,
  event_id int NOT NULL REFERENCES events(id),
  buyer    text NOT NULL
);
INSERT INTO events VALUES (1, 'Opera Night', 1, 0);   -- exactly one ticket left

The naive handler does three steps in the application: SELECT tickets_left, compute left - 1 in code, then UPDATE … SET tickets_left = 0. That is a read-modify-write cycle, and the timeline below shows why it breaks: the second writer computes its answer from a value that stopped being true while it was thinking.

This is a lost update: two read-modify-write cycles on the same value, where the later write silently discards the earlier one. Notice what it is not. It is not a dirty read (everything read was committed) and the CHECK did not save us, because 0 is a perfectly legal number. The invariant broke in the application’s head, not in the table’s.

Fix 1: lock the row (my first instinct)

When I first worked through this, my answer was the one nearly everyone gives: lock that row so the second buyer cannot touch it until the first is done. That is correct, and it is called a pessimistic lock. In SQL it is SELECT … FOR UPDATE:

BEGIN;
SELECT tickets_left FROM events WHERE id = 1 FOR UPDATE;   -- second buyer waits here
-- app: if tickets_left > 0 ...
UPDATE events SET tickets_left = tickets_left - 1 WHERE id = 1;
INSERT INTO orders (event_id, buyer) VALUES (1, 'linh');
COMMIT;

Minh’s transaction blocks on the FOR UPDATE until Linh commits, then re-reads the row and sees 0. The price is the one from the booking post: the lock is held across your application round trip, so on a hot row buyers queue up holding database connections. Use it when the decision genuinely needs several statements, not for a single increment.

Fix 2: make it one atomic statement

The read-modify-write cycle only exists because the arithmetic happens in the application. Move it into the database and the cycle disappears; a single-row update is atomic on every engine:

UPDATE events
SET    tickets_left = tickets_left - 1
WHERE  id = 1 AND tickets_left > 0
RETURNING tickets_left;
-- 1 row  -> you got the ticket
-- 0 rows -> sold out, tell the user

Under Postgres’s READ COMMITTED the second buyer waits for the first, then ==!re-evaluates the WHERE clause against the new row version==, sees tickets_left = 0, and updates nothing. This is the same conditional-update trick the clinic post leans on; I will not repeat its reasoning here. Even without the WHERE, the CHECK constraint would reject the second decrement with an error. Best fix for a counter, and it needs no isolation upgrade.

Fix 3 and 4: compare-and-set, and a version column

Sometimes the new value cannot be written as arithmetic: you computed it from a whole document or from several fields. Then use compare-and-set: write only if the row still holds the value you read. Its sturdier form is optimistic locking, where a version column stands in for “nothing changed since I looked”.

-- compare-and-set on the value itself
UPDATE events SET tickets_left = 0
WHERE  id = 1 AND tickets_left = 1;          -- 0 rows: someone changed it, re-read and retry

-- optimistic locking with a version column
SELECT tickets_left, version FROM events WHERE id = 1;      -- say (1, 7)
UPDATE events
SET    tickets_left = 0, version = version + 1
WHERE  id = 1 AND version = 7;               -- 0 rows: lost the race, retry or give up

A version column beats comparing the value when the value can go back to what you read (1, then 0, then refunded to 1: an “ABA” problem), and it works for rows with many columns. The cost is that the loser must retry in application code, which is cheap when conflicts are rare and miserable when forty people want one ticket. One caveat from the book: compare-and-set is only safe if the WHERE reads the current row, not an old snapshot. Postgres and InnoDB both do that for UPDATE; check before trusting it on another engine.

Bonus, free of charge: let the database notice

Some engines detect a lost update for you when the isolation level is snapshot-based. Run this in two psql windows (S1 and S2) against the schema above:

-- S1
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT tickets_left FROM events WHERE id = 1;            -- 1
-- S2
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT tickets_left FROM events WHERE id = 1;            -- 1
-- S1
UPDATE events SET tickets_left = 0 WHERE id = 1;
COMMIT;
-- S2
UPDATE events SET tickets_left = 0 WHERE id = 1;
-- ERROR:  could not serialize access due to concurrent update   (SQLSTATE 40001)
ROLLBACK;                                                 -- then retry the whole transaction

Postgres notices that the row S2 wants to change was modified by a transaction that committed after S2’s snapshot, and aborts S2. ==!MySQL InnoDB at REPEATABLE READ does not.== Its plain SELECT reads the snapshot, but its UPDATE reads the latest committed row, so the second SET tickets_left = 0 goes through and Linh’s sale is silently overwritten. Oracle’s serializable and SQL Server’s snapshot isolation also detect it, according to the book. The practical lesson: the same code is correct on one engine and broken on another, so for portable correctness use fix 2.

05Model B: one row per ticket, and write skew

Now change the model, and watch the bug change its name. Real ticketing systems often store each sold ticket as its own row and treat capacity as a number on the event. To sell, the handler counts the existing rows and inserts a new one if there is still room.

CREATE TABLE events_b (id int PRIMARY KEY, capacity int NOT NULL);
CREATE TABLE tickets (
  id       bigserial PRIMARY KEY,
  event_id int  NOT NULL REFERENCES events_b(id),
  buyer    text NOT NULL
);
INSERT INTO events_b VALUES (1, 1);                       -- capacity 1

-- the naive handler, run by Linh and by Minh at the same time:
BEGIN;
SELECT count(*) FROM tickets WHERE event_id = 1;          -- 0, so the app decides there is room
INSERT INTO tickets (event_id, buyer) VALUES (1, 'linh'); -- Minh inserts 'minh'
COMMIT;

This is write skew: two transactions read the same premise, then each writes a different object, and together the writes falsify the premise. The book’s example is two on-call doctors who both go off duty because each saw “the other one is still on”. Here the premise is “count is below capacity”. The root cause has its own name, the phantom: the thing the premise depends on (a row that would make the count 1) did not exist yet, so there was nothing to lock or to version.

Compare with model A. There, both buyers fought over one row, so a row lock, a version check or Postgres’s REPEATABLE READ would stop them. Here they never touch the same row. My first fix (“lock the row”) has no row to lock, and Postgres will not even let you try: SELECT count(*) … FOR UPDATE is rejected because locking is not allowed with aggregates. (InnoDB accepts it and its gap locks can block the race, but typically as a deadlock error rather than a clean wait.) Postgres at REPEATABLE READ lets both inserts through as well, since it only checks for write-write conflicts on the same row.

Three ways out of write skew

1. Materialize the conflict. Give the phantom a body. Lock a row that every buyer of this event must touch, such as the event itself:

BEGIN;                                                    -- READ COMMITTED (the default)
SELECT capacity FROM events_b WHERE id = 1 FOR UPDATE;    -- all buyers of event 1 line up here
SELECT count(*) FROM tickets WHERE event_id = 1;          -- fresh statement, sees the committed insert
INSERT INTO tickets (event_id, buyer)
SELECT 1, 'minh'
WHERE  (SELECT count(*) FROM tickets WHERE event_id = 1)
     < (SELECT capacity FROM events_b WHERE id = 1);
COMMIT;

This works at READ COMMITTED because each statement takes a fresh snapshot after the lock is granted. At REPEATABLE READ it would not be enough: the snapshot is fixed at the first statement, before the wait, and since nobody changed the parent row there is no conflict for Postgres to flag, so the count still reads 0. (If every buyer also UPDATEd the parent row, Postgres would abort the loser with 40001, which is safe but means a retry.) It also turns every buyer of an event into a queue, which is a hot row again. If you are willing to serialize on one row anyway, model A’s counter is simpler.

2. Make the database enforce it. If seats are numbered (or you pre-create a row per ticket, like the booking post’s slots), a UNIQUE (event_id, seat_no) constraint turns the phantom into a real conflict. Two buyers choosing the same seat collide on the unique index and one gets 23505. For general admission, pre-create the rows and claim one with SKIP LOCKED:

CREATE TABLE seats (
  event_id int  NOT NULL,
  seat_no  int  NOT NULL,
  buyer    text,                                   -- NULL = free
  PRIMARY KEY (event_id, seat_no)
);
INSERT INTO seats VALUES (1, 1, NULL);

UPDATE seats SET buyer = 'minh'
WHERE  (event_id, seat_no) = (
         SELECT event_id, seat_no FROM seats
         WHERE  event_id = 1 AND buyer IS NULL
         LIMIT  1 FOR UPDATE SKIP LOCKED)
RETURNING seat_no;                                -- 0 rows: nothing free right now (rows held by in-flight buyers are skipped)

3. Raise the isolation level. Run the original handler at SERIALIZABLE and let the database detect the dangerous pattern. This is the only fix that works on the naive code unchanged, and it comes with a contract: ==!you must catch 40001 and retry the whole transaction==.

BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT count(*) FROM tickets WHERE event_id = 1;          -- 0
INSERT INTO tickets (event_id, buyer) VALUES (1, 'linh');
COMMIT;
-- the app only inserts if count < capacity; when both run concurrently, one statement (often the COMMIT) fails with:
-- ERROR:  could not serialize access due to read/write dependencies among transactions
-- SQLSTATE 40001  ->  retry the whole transaction; it now sees count = 1 and sells nothing

async function withRetry<T>(fn: (tx: Tx) => Promise<T>, max = 5): Promise<T> {
  for (let attempt = 1; ; attempt++) {
    try {
      return await db.transaction(fn, { isolationLevel: "serializable" });
    } catch (e: any) {
      if (e.code !== "40001" || attempt === max) throw e;   // only retry serialization failures
      await sleep(10 * 2 ** attempt + Math.random() * 10);   // backoff with jitter
    }
  }
}

06A map of levels and anomalies

07Three ways to be truly serializable

Serializable is the only level that promises: the result is the same as if the transactions ran one at a time, in some order. If you want the invariant to hold without thinking up a custom fix per query, the book describes three families, and each one pays in a different currency.

Serial execution is the blunt tool: it makes the whole class of bugs vanish, but it only suits short, in-memory transactions, and ticket checkout that calls a payment gateway mid-transaction would stall the single thread. 2PL is the pessimistic way: it locks everything it reads, including the range “tickets of event 1”, which is exactly what stops our phantom. It is also why many engineers avoid it: a transaction waiting on a lock holds up the line behind it, and two transactions waiting on each other deadlock (post 06 digs into exactly that).

SSI is the optimistic one, and the one Postgres ships. Nobody blocks; the database watches for the dangerous shape (a transaction that read something a concurrent transaction then changed) and aborts one of them, usually at commit. It is conservative, so it can abort a transaction that would have been fine. That is the 40001 in the example above. The difference between the two worlds is when you pay: 2PL makes you wait up front, SSI lets you run and sometimes tells you to go again. Under low contention SSI is a bargain. For one ticket and ten thousand buyers, every loser retrying at once is its own storm, which is why high-demand sales still prefer a single atomic update on a counter, and why that counter becomes a hot row of its own (post 07).

08Closing: the name depends on the model

Same ticket, same two thumbs. Store a number and the bug is a lost update; store rows and it is a write skew. Name the shape first, then choose the tool.

Duy Anh, notes on DDIA ch. 7

That is exactly why interviewers love this question. A candidate who says “lock the row” has the right instinct and half the answer. The follow-up (“now you model each ticket as a row and check count < capacity: what changes?”) separates people who memorized a fix from people who understand what an isolation level promises and what it does not. A DBA I used to work with kept a one-line version of this in his notes: find the shared premise, then decide who owns it. It has saved me more than once. Next up (post 03): the buyers queueing behind a lock are exactly what a p99 chart shows and an average hides.

• Counter? ==Atomic UPDATE … WHERE n > 0==. Fall back to CAS or a version column when the new value is not arithmetic, and FOR UPDATE when the decision spans several statements.
• Rows plus a count? The premise is a phantom. ==Materialize it, constrain it, or go SERIALIZABLE and retry 40001.==
• Do not trust level names across engines. Postgres REPEATABLE READ catches a lost update; InnoDB’s does not.

Interview questions to practice

1. One ticket left, two concurrent buyers. Name the anomaly and give three fixes.
2. On Postgres, why does SELECT … FOR UPDATE not help when each ticket is a separate row and you count them?
3. Does REPEATABLE READ prevent lost updates? Answer for Postgres and for MySQL.
4. Compare 2PL and SSI: when does each one make you pay?

Written by Vũ Duy Anh, backend engineer in Hà Nội. Questions or corrections are welcome by email.