Prevent Double Booking with Atomic Claims and Holds

01 The idea

The problem this lesson solves

Show 1, seat F7 has booked_by = NULL. Both clients have an old screen showing it available.

Prerequisite: earlier lessons introduce the vocabulary and shared examples used here. Follow the module order.

02 Worked example

Follow one worked example

Trace the example in order. Follow the same identities, units and state transitions through the diagram and the events below.

A seat is booked only when the authoritative atomic claim succeeds; an earlier free-seat read is not a reservation.

03 The method

Mechanisms decisions and a guided tutorial

Work through the explanation in order, then apply it in the tutorial. Each comparison names a cost as well as a benefit.

Make claiming a seat one decision

UPDATE show_seats SET booked_by = 'A' WHERE show_id = 1 AND seat_no = 'F7' AND booked_by IS NULL RETURNING seat_no combines eligibility and change. At Read Committed, a concurrent update waits on the row; after the first transaction commits, PostgreSQL rechecks the predicate against the updated row. One contender gets one returned row, the other gets none. The application treats zero rows as a conflict, not a successful booking. A unique reservation row is another useful pattern when ownership is represented as an insert.

Locks and versions solve related problems

SELECT ... FOR UPDATE is useful when a decision needs several steps before updating the locked row. Keep the transaction short. Optimistic locking instead includes a version in the update predicate and treats a zero-row update as a conflict requiring reread or retry. Serializable can protect more complex invariants but needs correct retry handling. Choose the smallest mechanism that enforces the stated rule, and keep database constraints as a final guard.

Do not keep a transaction open during checkout

A person can take minutes to pay. Holding row locks during that interaction ties up connections and blocks other users. Model a time-limited hold with an owner, expiry and state. Converting the hold to a booking must verify ownership and expiry atomically. Multiple-seat transactions should acquire row locks in a consistent order to avoid the usual lock-order cycle. Other transactions or resources can still introduce deadlocks, so handle database aborts even with careful ordering.

Separate UI state from ownership

The seat map is a view of some earlier moment. Two people can both see F7 free without either owning it. The claim must check current eligibility and change ownership in one defended operation. At Read Committed, a second PostgreSQL updater waits for the first row writer and then reevaluates its predicate against the updated version.

The application must inspect RETURNING or the affected-row count. One returned row means this request obtained the claim under the predicate. Zero rows means it did not. A successful SQL call with zero eligible rows is a business conflict, not a successful booking.

Use holds for human-length workflows

Checkout can take minutes. Keeping a transaction and row lock open throughout that interval consumes connections and prevents useful work. Instead persist a hold with a unique hold_id, holder identity, expiry and state. Confirmation checks that the same hold still owns the seat and satisfies the expiry policy.

A late payment callback must not overwrite a newer holder. Include hold identity or a version in the confirmation predicate and make callbacks idempotent. If payment and seat ownership disagree, the business workflow needs reconciliation or refund behavior; a SQL transaction cannot atomically include an arbitrary remote payment.

Several seats need one all-or-nothing decision

For F7 and F8, acquire or inspect the complete requested set inside a short transaction. Lock rows in a canonical order, confirm both exist and remain available, then update both and commit. Roll back if any required seat fails.

Canonical ordering prevents the usual cycle caused by two transactions requesting the same pair in opposite orders. Continue to handle deadlock and serialization aborts because additional tables and code paths can still introduce conflicts. Keep a retryable database abort distinct from an unavailable-seat response.

Guided tutorial

1. Create disposable data

Use a local practice database and run the setup below once. Open two psql sessions connected to that same database. Do not run the lab against production data.

2. Hold the first claim

In session A run BEGIN ISOLATION LEVEL READ COMMITTED; then the UPDATE below with owner A. It returns F7. Leave the transaction open.

3. Compete from session B

In session B run the same UPDATE with owner B. The statement should wait. Return to A and COMMIT. B should finish with no returned row and UPDATE 0.

4. Verify and reverse

SELECT * FROM sd_lab_seats should show owner A. Reset booked_by to NULL after both transactions finish, repeat, and ROLLBACK A instead of committing. B can now claim the row. Rollback did not consume the seat.

CREATE TABLE sd_lab_seats (
  show_id integer, seat_no text, booked_by text,
  PRIMARY KEY (show_id, seat_no)
);
INSERT INTO sd_lab_seats VALUES (1, 'F7', NULL);
-- In session A, after BEGIN ISOLATION LEVEL READ COMMITTED:
UPDATE sd_lab_seats SET booked_by = 'A'
WHERE show_id = 1 AND seat_no = 'F7'
  AND booked_by IS NULL RETURNING seat_no;

05 Cheat sheet

Review the decisions

Use this table to explain the mechanism and its limit without memorizing a product name.

06 Where & why

Check the primary references

Use official references for protocol and implementation details. The questions here are teaching prompts.

07 Interview questions

Explain the reasoning out loud

Answer before opening the explanation. The questions progress from vocabulary to decisions and failure behavior.

08 Practice problems

Practice a new case

Solve the new cases before revealing hints. Complete worked solutions follow the set.

Worked solutions