Hotel Reservation Failure & Scale Scenarios

Each conditional UPDATE takes the row lock and evaluates sellable - held - confirmed >= 1 against the latest committed row, so this works at ordinary READ COMMITTED. Because both transactions lock in the same ascending order, B queues behind A on the first shared row instead of interleaving. The application requires summed rows_affected to equal the number of nights; B's 0 on 11-15 rolls back its whole transaction. The loser gets a fast 409 INVENTORY_UNAVAILABLE naming the night, and the client offers an alternative such as RT-STD for the same dates. Idempotency keys play no part here: they stop the same request applying twice, not two different requests competing (Stage 3, Complete Design Ch.11).

Nothing to repair — this is the designed outcome. The client re-quotes an alternative rather than retrying the same night, because a retry against a night that is gone is wasted work.

409 rate and lock-wait time per (hotel, room type, night). Invariant: held + confirmed never exceeds sellable on any night; the CHECK constraint makes a violating commit impossible even for a buggy code path.

The same transaction updates 11-13 (1 row), 11-14 (1 row) and 11-15 (0 rows). Summed rows_affected is 2 of 3, so the whole transaction rolls back — including the two nights that matched — and the 409 names 2026-11-15. The mechanism does not depend on stay length: an N-night stay is N ordered statements and one all-or-nothing check (Ch.11).

Designed behaviour. The client offers explicit alternatives — shifted dates, or a different room type for the blocked night as a separate, traveler-chosen booking — never a silent partial commit.

The bug class to test for is checking each statement's success on its own instead of the sum. Invariant: a multi-night stay is allocated all nights or none.

Every path that touches more than one night — hold, confirmation, expiry, cancellation, modification — issues one UPDATE per night sorted by stay_date (Ch.11). All transactions therefore acquire shared rows in the same global order, so a cycle cannot form: the later transaction holds nothing the earlier one needs, waits on the first shared row, then evaluates its condition against the committed result. Sorting also lets the application name the blocking night in the 409.

With the rule in place there is nothing to recover. Without it, the database's deadlock detector aborts one transaction; the system stays correct, but the abort, retry and extra latency cluster on exactly the popular nights where the row lock is already contended.

Deadlock count should be zero; a non-zero value means some path (a new admin tool, a modification swap) issues unordered or set-based updates. Invariant: no oversell and no deadlock, however the overlapping ranges relate.

The confirmation's first statement claimed the hold, so the worker's identical guard finds nothing to do. Earlier in the window the deferral would have skipped HLD-1 anyway while its attempt was in flight; the fence is the backstop that makes the outcome safe regardless of timing. A version column on the shared inventory row would be the wrong fence: every unrelated hold or booking on 11-15 changes it, turning normal traffic into false conflicts (Ch.12).

Nothing to recover. The worker's 0-row result is logged as a fence miss — expected, healthy noise.

Fence-miss rate per worker run. Invariant: expiry never releases inventory that now belongs to a reservation.

The fence protects HLD-2: a stale success can never turn someone else's unit into this traveler's reservation. Money that moved is returned through Stage 7's branch (Ch.15): the attempt moves to PAYMENT_CAPTURED_BOOKING_FAILED → RECONCILING, the confirmation's fence matches 0 rows, and the payment is refunded (or the authorization voided if nothing was captured), ending REFUNDED. A product may first try a fresh conditional allocation of the same nights before refunding — a policy choice that still goes through the normal allocation, never around the fence.

The late success is caught by the webhook handler or by the reconciliation sweep that compares provider records with attempts already marked failed. The traveler is told the payment was refunded and is offered a fresh search.

Late-success-after-expiry count and refund latency; provider p99 authorization time against the 60 s auth_timeout. A longer TTL changes how often this happens, never whether it is safe. Invariant: a stale operation never resurrects an expired hold or steals a newer one; payment success alone never creates a booking.

Forward recovery with the same T3 the online path uses. Nothing is charged again: the worker queries the provider, it does not call authorize or capture again, and any retried call reuses BKA-1's reference. A traveler retrying with the same key meanwhile gets 202 PENDING_PAYMENT, then the final result (Ch.15).

Detected by the PENDING_PAYMENT sweep a few minutes after auth_timeout, not by a support ticket. The traveler sees the confirmation about two minutes late — degraded, but correct.

Count and age of the oldest PENDING_PAYMENT attempt; reconciliation outcomes by type. Invariant: capture and confirmation converge — every captured attempt ends CONFIRMED or REFUNDED.

The worker marks the attempt PAYMENT_CAPTURED_BOOKING_FAILED → RECONCILING and runs T3; the fence on HLD-1 matches 0 rows, so the transaction rolls back. The worker refunds PAY-1 through the Payment Service with a stable refund reference, and the attempt ends REFUNDED. Policy may first try a fresh conditional allocation of the same nights; if that fails, refund. It never oversells to honour a payment (Ch.15).

The same sweep detects it; a slower comparison against the provider's settlement export also catches captures whose attempt was already marked failed. The traveler is told the payment was refunded and is offered alternatives.

Refunds caused by reconciliation, by cause; sweep lag. A growing backlog turns 3A outcomes into 3B outcomes, so sweep lag is the metric to alert on. Invariant: payment success is never treated as booking success.

The handler verifies the webhook, deduplicates it by the provider's event id and asks the provider for the payment's current status rather than trusting the payload. The state machine has no automatic transition out of CONFIRMED driven by a payment event, so inventory is untouched. If the provider shows the charge is intact, the event is recorded and closed. If money really was reversed, the case goes to a payment-exception workflow whose outcome — collect payment again, or cancel through the normal guarded cancellation that releases the nights — is a policy decision, not a side effect of a webhook (Ch.13).

Detected when the handler finds no valid transition; the case lands in the exception queue with the provider's status attached.

Exception-queue depth and age; webhooks rejected as stale. Invariant: payment and booking state converge only through explicit, guarded, auditable transitions.

The orchestrator inserts the attempt first and lets the unique constraint decide, so two retries landing on two servers in the same millisecond cannot both win. On the violation it loads BKA-1: same fingerprint and COMPLETED → the original 201 with RES-1; same fingerprint and still PENDING_PAYMENT → 202 Accepted without a new provider call. Provider calls carry BKA-1 as their idempotency reference, so even a retried authorize resolves to the same charge (Ch.14).

Logged as an idempotent replay, not an error. Nothing to recover.

Replay rate by client version — a spike usually means a client timeout set too low. Invariant: repeated create requests are idempotent.

A new key for the same hold collides with the partial unique index (one PENDING_PAYMENT attempt per hold), so a second payment cannot start while BKA-1 is in flight; once BKA-1 completes, the hold is CONSUMED and is no longer payable. A reused key with a different body fails the fingerprint comparison and gets 409 IDEMPOTENCY_KEY_CONFLICT, never the earlier, unrelated result. Keys are scoped per principal and endpoint, so two travelers' keys cannot collide.

Counted per client as fingerprint conflicts and partial-index violations; these are client bugs to fix, not traffic to absorb.

Invariant: at most one payment per hold, and a key always maps to exactly one request.

The cancellation committed CONFIRMED → CANCELLED with the per-night releases and the refund event. The replayed confirmation fails its first statement — HLD-1 is CONSUMED, not ACTIVE — and stops; it can neither resurrect the reservation nor move counters. The modification's guarded update expects state = 'CONFIRMED' AND version = 1 and matches 0 rows, so its saga compensates by releasing the night it had held. A second cancel with the same key returns the original result; with a new key its guarded update matches 0 rows and reports the current state, so nights are never released twice (Ch.16).

Rejected transitions are logged as stale, not shown to the traveler as errors.

Stale-transition counts by source — a sudden rise points at a misbehaving queue. Invariant: a cancellation and a stale replay can never both apply; cancel-after-modify works because a modified reservation stays CONFIRMED.

The refund record carries a stable reference derived from the cancellation. A transient error schedules a retry with exponential backoff, always with the same reference, so the provider cannot apply it twice; after a bounded number of attempts it escalates to manual review. The nights were released when the cancellation committed and do not wait for the refund, and a failing refund never reopens the cancellation. Under RP-SAVER the cancellation is identical, but the refund resolves to zero and no provider call is made.

Refund age and retry count surface a stuck refund before it becomes a support case.

Oldest pending refund. Invariant: inventory release and refund are independent operations, each idempotent.

Step 1 returns 409 naming 2026-11-16; steps 2–3 never run and 11-14 was never released, so the traveler keeps the original stay exactly as it was. A new range that touches the stop-sold 11-20 or breaks a minimum stay fails the same way with 422 RESTRICTION_VIOLATED: a modification is validated like a new booking. If HLD-3 expires during a slow step 2, or the reservation's version moved, step 3's guards match 0 rows and the saga compensates (Ch.16).

Each saga step is recorded, so a crashed saga is resumed or compensated by a worker; an HLD-3 left ACTIVE after its saga ended is simply expired by the Expiry Worker.

Modification outcomes by failing step; compensations executed. Invariant: a modification ends in either the old stay or the new one, never in between.

The hold's conditional UPDATE on 11-15 matches 0 rows → 409 INVENTORY_UNAVAILABLE naming the night. Within seconds the projector applies the hold.created event and later searches show the night as sold out (Ch.09).

This is the contract, not an incident; the response states its own staleness.

Projection lag p99 against the 60 s SLO, and the 409 rate on holds that follow an "available" search — both rise together when the projection falls behind. Invariant: a search result is never a guarantee; only a committed hold or confirmation is.

The stale negative is a missed sale bounded by the lag and corrects itself when the event is projected — revenue, not correctness. For the rate change, this design honours the quote until it expires: the hold records QTE-1, so the traveler pays the price shown. After expiry a booking against it gets 410 QUOTE_EXPIRED and re-quotes at the new price_version. Rates live in DailyRate, so the edit never touches an inventory row (Ch.17).

Both self-correct; nothing to repair.

Lag as above; quote expiries and re-quotes. Invariant: a quote is a time-boxed price promise, not an inventory claim.

The stop-sell inserted a Restriction row; it did not set sellable = 0 or touch held or confirmed. The hold transaction reads restrictions for every night of the stay and returns 422 RESTRICTION_VIOLATED naming 11-20; the client offers other dates. Existing reservations covering 11-20 are untouched, and a hold granted before the stop-sell is not revoked — restrictions gate new holds. Modelled as sellable = 0, the stop-sell would be rejected by the CHECK whenever units were already held or sold, overwritten by the next PMS capacity sync, and stripped of its reason (Ch.17).

Designed behaviour; the 422 carries the reason, so the traveler is not left with a generic failure.

422 rate by restriction type. Invariant: a stop-sell blocks new sales immediately and never breaks a settled reservation.

883 ≤ 884: rejected as stale and never written. 885 > 884: applied as InventoryAdjustment and restriction changes, and the cursor advances to 885 in the same transaction. The duplicate 885 is ≤ 885 and is a no-op. Every applied batch also writes outbox events, so the projection follows (Ch.17).

Nothing to recover; ordering is enforced on arrival.

Rejected-stale and replay counts per hotel, and time since the last applied cursor — a silent PMS is its own incident. Invariant: PMS updates apply only in cursor order; an old batch never overwrites newer state.

The connector translates the PMS figure into an adjustment of sellable — never an overwrite of held or confirmed. Because 4 < held + confirmed = 5, the adjustment is not applied (the CHECK would reject it anyway) and is recorded as a sync conflict for operations; the rest of the batch applies and the cursor advances, so later batches are not blocked. Platform reservations are never cancelled by a PMS report. Resolution — relocating a guest, or the hotel freeing a room — belongs to the hotel under its own overbooking practice. The platform itself does not overbook; if it should, that is an explicit, modelled allowance, not a side effect of sync.

Detected at apply time by the capacity guard; surfaced as an open conflict with both views attached.

Open sync conflicts per hotel and their age. Invariant: an external update never shrinks sellable below held + confirmed. The cross-channel oversell window is bounded by sync latency, not eliminated — it sits outside the platform's ledger guarantee.

Ascending lock order turns the burst into a queue rather than deadlocks. The first transaction whose condition holds takes the last unit; the rest match 0 rows and get a fast 409 naming 11-15 — the correct outcome of genuine scarcity. T1 and T3 stay a few milliseconds with no provider call inside. Admission control per (hotel, room type) — a short queue or lock in front of the database, or a waiting room for flash sales — sheds doomed attempts before they reach the row; it is an optimization that can be removed without affecting correctness. Sharding by hotel_id keeps HTL-204's lock queues and connections away from hotels on other shards; it does not split the hot row (Ch.18).

Lock wait and 409 rate spike for one key while aggregate QPS looks unremarkable. Nothing to repair: once the night sells out, the projection shows it within its lag and attempts stop.

Lock-wait p99 per hot key, allocation transaction duration (must stay in milliseconds), connection-pool saturation per shard — tracked per (hotel, room type, night), not only in aggregate. Invariant: no oversell and no deadlock under row-concentrated contention.

The Search Service fails fast and degrades — fewer results, an "availability approximate" banner, or a search error — and never falls through to the Booking DB primary. Holds and bookings never read the projection, so they continue at normal latency. When the read model returns, the projector resumes from its last relay offset and replays the backlog; a full rebuild from the ledger is a last resort after total data loss, not routine (Ch.09).

Read-model health checks and search error rate. Any rise in read load on the booking primary means degradation is not engaging.

Search availability and booking latency side by side: the second must not move. Invariant: a search outage never degrades booking correctness or latency, because the booking path never reads the projection.

Confirmed reservations are unaffected: RES-1 and EVT-1 committed together, so the event is delayed, never lost. What degrades is downstream: projection lag exceeds 60 s (more stale positives and negatives, Scenario 07), and confirmation emails and refund requests go out late. Consumers deduplicate by event_id, so the eventual drain produces no duplicate emails or refunds. The poison event is retried a bounded number of times and then dead-lettered so it cannot block the stream; the projection entry it would have updated stays stale until the next event for that row or the projector's drift check against the ledger repairs it.

Outbox lag (count and age of unpublished rows) and dead-letter depth alert first. The backlog drains by itself once the broker recovers; the dead-lettered event is fixed at its source (here, the PMS Connector's payload) and replayed.

Outbox age, projection lag, dead-letter depth. Invariant: a reservation's durability never depends on how quickly its event is published.

The shard's synchronous standby is promoted: committed holds and reservations survive (RPO ≈ 0) and the booking path is back within minutes (RTO). The old primary is fenced so it can never accept writes again; during promotion the booking path fails closed with 503 rather than writing anywhere else. A request in flight either committed — and was replicated synchronously — or did not; the client's retry with the same key finds the existing attempt or starts it fresh. Search keeps serving from the projection throughout (Ch.18).

Replication to the other region is asynchronous, so promoting it can lose the last seconds of commits (RPO > 0): a hold, an attempt or even a confirmation may be missing, and its nights look free in the promoted ledger and can be sold again. A retry that references a lost hold gets 404 or 410 and must re-hold. The provider still has every capture, keyed by attempt reference, and the PMS has the hotel's view. After promotion, reconciliation compares provider captures and PMS records with the surviving ledger: a capture with no reservation is re-confirmed if its nights are still free, otherwise refunded, with the hotel handling any guest who arrives with a lost confirmation.

Health checks and promotion alerts; replication lag, because cross-region lag is the RPO you will actually get; then an audit of the incident window against the provider's settlement export, confirming every captured payment has exactly one CONFIRMED or REFUNDED outcome.

Cross-region replication lag, reconciliation backlog after failover. Invariant: the ledger cannot be oversold while it has a single writer and the CHECK holds — for the ledger that survives. Across an asynchronous regional failover that is not absolute; the guarantee becomes "every captured payment converges to CONFIRMED or REFUNDED after reconciliation". Active-active writes to the same hotel's inventory from two regions are outside this design.