Prevent Double Booking with Atomic Claims and Holds

01 The idea

The problem this lesson solves

Two buyers see F7 as available and press Book. Both screens were truthful when rendered, but neither screen reserved the seat. Correctness depends on one authoritative transition that turns “free” into “owned,” and on how a competing transaction observes that transition. We will build the claim, run the race and then extend the reasoning to temporary holds and payment.

You should recognize API resources and failure responses. SQL examples introduce their own tables and constraints.

02 Worked example

Let two buyers compete for seat F7

03 The method

Understand the mechanism

Why reading availability is not ownership

The unsafe sequence is read seat, check free in application memory, then write the buyer. A and B can both complete the read before either writes. If both later perform unconditional updates, the second can overwrite the first while both callers receive success.

Putting those statements inside transactions does not automatically establish the needed invariant at every isolation level. The application has separated eligibility from the deciding write. The earlier read is information about a past observation, not a reservation.

Define the invariant precisely: for one show-seat identity, at most one active owner may hold the relevant booking state. Historical canceled or expired records can exist separately; uniqueness must apply to the active ownership representation, not accidentally prohibit all history.

The database needs a conflict point that competing claims cannot both pass. A conditional update of one authoritative row is one approach. Inserting one reservation under an appropriate unique constraint is another. Both move the decision out of two independent app processes.

Combine eligibility and change in one statement

Use UPDATE sd_lab_seats SET booked_by='A' WHERE show_id=1 AND seat_no='F7' AND booked_by IS NULL RETURNING seat_no. The predicate identifies both the seat and the condition required to claim it. The returned row reports that this statement changed the eligible row.

If the result contains F7, the transaction has made the claim but still needs to commit. If no row is returned, the condition did not hold for a matching row. The application must not treat a successfully executed SQL statement with zero changed rows as a successful booking.

Distinguish zero rows from a query error. Zero rows may mean already claimed or missing under this simple query. A connection timeout means the result is unknown to the caller and may require recovery using an operation identity. Do not collapse these outcomes into one boolean.

Keep database constraints as additional protection. The composite primary key ensures that the row really is the unique representation of that show seat. Without it, duplicate rows could allow one statement to “claim” several inconsistent copies.

Walk the blocking and recheck behavior

At PostgreSQL Read Committed, let A update F7 and keep its transaction open. A holds the relevant row lock. B issues the same conditional update with booked_by='B'. B cannot independently overwrite the row while A’s update is unresolved, so it waits.

If A commits, B proceeds against the updated row and rechecks booked_by IS NULL. The condition is now false, so B returns zero rows. A is the winner. The result is enforced at the deciding database operation, not by trusting either buyer’s earlier seat-map screen.

If A rolls back, its claim does not remain committed. B can then find the row eligible and claim F7. This is why the tutorial tests both commit and rollback instead of assuming “the first request that arrived always wins.”

The application should return success only after its transaction commits under the chosen contract. If it tells A “confirmed” before commit and the transaction later aborts, it has promised a booking that does not exist.

Compare other concurrency mechanisms

SELECT FOR UPDATE can lock the seat before a short sequence of checks and updates. This is useful when the decision requires several fields or related calculations. The code must still check eligibility while holding the relevant lock and keep the transaction short.

Optimistic concurrency adds a version predicate. A client that saw version 4 attempts an update where version=4 and advances it to 5. A zero-row result reports that its assumption no longer holds. This can avoid waiting in some application patterns but requires a conflict experience or retry policy.

A unique reservation insert can represent ownership directly. Two inserts for the same active seat identity compete on the unique key. Choose how cancellation and expiry interact with that representation; an unconstrained collection of status rows does not automatically enforce active uniqueness.

Serializable transactions can protect broader rules but may abort and require complete retries. Do not describe them as a reason to ignore unique constraints or retry handling. Compare mechanisms by the invariant they enforce, the amount of locked or retried work and the resulting user behavior.

Model a hold instead of locking during checkout

A customer may take minutes to enter payment details. Holding a database transaction and row lock for that entire period ties up connections and blocks others. Instead, store a durable hold with an owner, expiry and state, then end the transaction.

A later confirmation checks that the hold still belongs to the caller, remains valid and is in the expected state, and transitions it atomically. An expired hold can be made claimable according to a database-time-based rule or another explicitly defined clock model. A background cleanup job helps maintenance but should not be the only thing preventing use of an expired hold.

Hold expiry and payment introduce a real race. Payment can succeed after the hold becomes invalid. A local database transaction cannot make the external provider’s result atomic with seat ownership. Define an idempotent payment workflow, operation identities and reconciliation or refund behavior for the product’s chosen policy.

Do not hide this behind “use a distributed lock.” The system needs a state machine describing held, confirmed, expired and canceled transitions, plus recovery when external steps complete late. Locks alone do not define those business outcomes.

Extend to multiple seats and recovery

If one booking needs F7 and F8 together, decide whether partial success is allowed. For all-or-nothing ownership in one database, claim both within one transaction and roll back if either cannot be obtained. Acquire rows in a stable order to reduce deadlock cycles between overlapping requests.

Even with a stable order, handle deadlocks and transaction aborts. Other code paths and resources can still introduce cycles. Retry the complete transaction within a bounded deadline, using the same logical operation identity where required.

After a response loss, recover the booking by operation id or another documented identity. A client should not initiate a new independent purchase merely because its first confirmation response was lost. Store enough durable outcome information to answer the recovery query.

Finally, monitor more than successful bookings. Track claim conflicts, hold expirations, lock-wait duration, payment reconciliation backlog and duplicate-operation attempts. These signals distinguish healthy competition for popular seats from infrastructure failures or a broken workflow.

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