Bookings and row locks

In 1 minute

Booking is “read the seats left, then write”. Without care, two requests can both read “1 seat left” and both book it. MiniTickets books inside one Postgres transaction that first locks the event’s row with SELECT … FOR UPDATE. A second booking for the same event waits for the lock, then sees the new number. A CHECK constraint also refuses a negative seat count, even if the code had a bug.

Picture it

sequenceDiagram
  participant A as Booking A
  participant P as Postgres
  participant B as Booking B
  A->>P: FOR UPDATE: lock
  B->>P: FOR UPDATE: waits
  A->>P: take seat, COMMIT
  P-->>B: lock, sees 0 seats
  B->>P: ROLLBACK, 409

How it works

1. Lock the row. FOR UPDATE locks only this event’s row until COMMIT or ROLLBACK. Bookings for other events, and plain reads, never wait.
2. Check on the locked row. Event exists (else 404), not started (else 400), enough seats (else 409). Nobody can change the row between the check and the write.
3. Write relative to the row. seats_left = seats_left - quantity, computed by Postgres. With the CHECK seats_left >= 0, even a missing lock could not oversell.
4. All or nothing. Seats, booking and outbox row commit together. Any failure rolls all of them back.

In MiniTickets

The transaction is apps/api/src/queries/bookings.ts → createBookingTx. The race demo (pnpm demo:booking-race) sends 20 buyers at 5 seats. Measured locally: 5 bookings, 15 answers of 409, 0 seats left, every time. With --unsafe (no lock, the app computes the number), 20 bookings were made for 5 seats: 15 oversold.

The cost is the hot row: all bookings for one popular event queue on one row, a few milliseconds each, and each waiting booking holds one of the pool’s 10 connections. That is why the sale-day gate in Valkey rejects most requests before they reach this lock.

Play the sale-day flow on the map →

See it in AWS

Console

RDS → minitickets-dev-db → Monitoring: DatabaseConnections and CPU rise on a busy sale. The lock itself is inside Postgres.

CLI

aws logs tail /minitickets/dev/api --since 10m --filter-pattern "Not enough seats"

Code

apps/api/src/queries/bookings.ts → createBookingTx

Break it

Symptom	Cause	Where to look
More bookings than seats	The lock was removed and the app wrote its own number	The query; run the race demo
Bookings slow down on a sale	Hot row: every booking queues on one row	Pool waits; the inventory gate
409 although seats seem left	Someone else committed first; the page was stale	Working as designed

Say it in an interview

Say it like this (20-30 seconds)

A booking runs in one Postgres transaction. It locks the event row with SELECT FOR UPDATE, checks the event exists, has not started and has enough seats, decrements seats_left relative to the row, and inserts the booking and an outbox row before committing. Concurrent bookings for the same event queue on the lock and re-read the row. A CHECK constraint on seats_left also rejects negative values. The trade-off is a hot row under heavy demand.

Follow-ups

Could a single conditional UPDATE do the same?

Yes: UPDATE … SET seats_left = seats_left - n WHERE id = $1 AND seats_left >= n. It is just as safe and one statement shorter. But when it updates 0 rows you do not know why (unknown, started or sold out) without a second query, which may already be out of date.

Do reads wait for the lock?

No. Postgres keeps the old version of the row for readers (MVCC), so events.list never waits for a booking.

How would you scale a hugely popular event?

Split the seats over several rows, or put requests in a queue and confirm them in order. Today the Valkey gate rejects most requests early, and Postgres stays the final authority.

Advanced: Why the checks live in the query layer

A service never holds a transaction, but the checks must run on the locked row inside it. So the three checks sit in the query, a deliberate exception to the layer rule. Errors from them roll the transaction back and pass up as 404, 400 or 409. A violation of the CHECK constraint is translated to the same 409.

Checkpoint

Answer these without scrolling up. If one feels hard, reread that section.

1. What is the lost-update race, in one sentence?
2. What exactly does FOR UPDATE lock, and for how long?
3. Why write seats_left - quantity instead of a number computed by the app?
4. What commits together in the booking transaction?
5. What is a hot row, and what protects it on a sale day?

Redraw it from memory: the sequence: A locks, B waits, A commits, B sees 0 and gets 409

Try this: run pnpm demo:booking-race on the local stack and count the 200 and 409 answers

Mark this page as read