Section 7: The Three Nights That Must Move Together - Deep Dive: Booking Correctness

Executive Summary: This is where the design either holds or quietly sells a room twice. Four walls: the transaction that decrements Dana's three inventory rows all-or-nothing under an explicit ascending lock, so overlapping bookings cannot deadlock; the hold, whose two failure modes point in opposite directions, so its TTL lives in a query predicate rather than a sweeper's schedule; the payment saga — hold, authorise, commit, capture off the outbox — with a compensation per step, including the authorise that times out; and the dual write, killed by a transactional outbox.

The interviewer taps the Postgres box from Section 6. "Walk me through Dana's booking. Not the happy path — I want the moment two guests want the last suite." At 28 booking writes per second chain-wide you cannot hide behind scale: a system that handles twenty-eight writes a second and still oversells is not under-provisioned. It is wrong.

Key Insight: The invariant from Section 2 — for every (property, room_type, date), booked_count + held_count <= physical_count + overbook_allowance — is the only thing this section defends. A mechanism that does not protect it does not belong in the commit path.

Wall One: One Transaction, Three Rows, Ascending Date Order

Dana wants the nights of 2026-03-13, 03-14 and 03-15. Three inventory rows. The mechanism is one conditional statement, not a read followed by a write:

BEGIN;  -- READ COMMITTED is sufficient; see the pushback below

SELECT 1 FROM inventory
 WHERE property_id = $1 AND room_type_id = $2
   AND stay_date = ANY($3)
 ORDER BY stay_date FOR UPDATE;  -- the lock order IS this ORDER BY

UPDATE inventory
   SET held_count = held_count + :qty
 WHERE property_id  = $1
   AND room_type_id = $2
   AND stay_date    = ANY($3)   -- rows already locked, ascending, above
   AND booked_count + held_count + :qty <= physical_count + overbook_allowance;
-- assert rowcount = 3, else ROLLBACK and return 409 sold_out with alternatives

INSERT INTO holds (hold_id, property_id, room_type_id, check_in, check_out,
                   rate_amount, expires_at)
VALUES ($4, $1, $2, '2026-03-13', '2026-03-16', 420.00,
        now() + interval '10 minutes');

COMMIT;

Three properties make this correct rather than plausible. The predicate and the write are one statement, so the check cannot go stale — the database re-evaluates it against the row it has already locked. The rowcount assertion is the all-or-nothing guarantee: if only two of Dana's nights are open, two rows update, the assertion fails, the transaction rolls back. Three nights or zero, never a stay with a hole in it — and :qty is a parameter, so one predicate serves Dana's single room and the 40-room wedding block of Section 10. The third property is the explicit lock: a plain multi-row UPDATE locks rows in whatever order its plan emits — index scan direction, or physical order under a bitmap heap scan — which you do not control, so the order must be stated outright. The SELECT ... ORDER BY stay_date FOR UPDATE above takes Dana's three rows ascending before the write touches any.

That looks like ceremony until you name the deadlock. Dana locks 03-13 and reaches for 03-14; another guest, iterating the range backwards, holds 03-14 and reaches for 03-13. Each waits on a row the other owns and Postgres kills one. At 0.00076 writes/sec you may never see it; at 28/s on a Grand Prix weekend you lose a day to it. One global lock order — always ascending stay_date — makes the cycle unconstructible, and that ORDER BY is what turns acquisition order into a contract instead of a plan detail.

That is D4, over an application-level distributed lock, optimistic retry alone, and SELECT-then-UPDATE. It flips when a booking stops touching one property's rows: cross-property packages (a flight plus two hotels) need a saga.

"Why not just set SERIALIZABLE and stop thinking?"

Honest answer: at 28 writes per second you can, and refusing on principle is posturing. But name the cost: SERIALIZABLE aborts the loser of every conflict, so every caller needs retry logic, and retries concentrate exactly where contention does — the last suite on the hottest date. The conditional UPDATE needs no retry path, because the row lock plus the re-evaluated predicate is the serialisation point.

Quick check before moving on: