Designing for Scale · End-to-End Design Review II
Protect ticket inventory through bounded admission, short transactions, durable holds and idempotent payment reconciliation with a SQLite state-machine fixture.
Original publication date unrecordedUpdated
Lesson preparation & details
Level: advanced
By the end, you should be able to
- Separate admission, reservation and ownership
- Compare conditional updates and row locks
- Guard expiry and late payment with state and version
- Test duplicate callbacks and stale cleanup safely
Bring with you
Sources checked: . A source check does not change the publication or update date.
Editorial review: · What review means
In this article · 17 sections
The problem this post solves
A ticket sale can concentrate demand into seconds. A 24-hour average is a poor admission target when millions of clients start at a scheduled time. The challenge is not a universal claim that relational databases cannot survive a spike. It is protecting finite tested capacity while preserving the invariant no seat has more than one confirmed owner across retries, timeouts, expiry and payment uncertainty.
This capstone separates admission, reservation and payment. A cache counter is not inventory authority, a queue receipt is not a ticket, and a five-minute reservation is not a database lock held for five minutes.
The Prompt
“Design a ticketing system that allows users to view events and purchase tickets. It must handle massive spikes for popular concerts.” Clarify reserved seating versus general admission, tickets/order, accessibility requirements, anti-bot/fairness policy, payment methods, hold duration, refund policy and what checkout acknowledgement means.
Bounding the problem
Read path: event details, venue maps and artist images are cacheable under explicit policy. Availability displayed to browsers may be stale if the UI clearly treats it as advisory; checkout must use authoritative ownership. Personalized pages, prices and authorization require their own cache rules.
Write path: assume 100,000 seats and five million attempted purchases over ten seconds. That is 500,000 attempts/s, not a measured Redis or database capacity. If each admitted reservation consumes 5 ms of one serial worker slot, its ideal rate is 200/s; a 60-second completion budget permits at most 12,000 such operations per slot before other work. The actual safe limit must come from the real transaction and failure workload.
The safety property is at most 100,000 sold seats, not exactly 100,000. Failed payments, accessibility holds, blocked fraud or insufficient valid demand may leave seats unsold. Liveness and fairness—eligible users eventually receive a decision—are separate objectives. For multi-seat orders, enforce an atomic all-or-none reservation or explicitly define partial purchase behavior.
The thundering-herd model: narrow offered work at every expensive boundary. A waiting room can smooth arrival, but it needs a bounded population, signed/validated admission tokens, fairness rules, expiry and observable status. A queue moves the wait; it does not make its duration acceptable automatically.
Architecture: Surviving the Spike
Figure text equivalent — labels and relationships
- Node “Clients and retries” (square).
- Node “Edge static cache and abuse controls” (square).
- Node “Admission quotas and waiting room” (square).
- Node “Optional Redis advisory capacity” (square).
- Node “Bounded checkout queue” (square).
- Node “Reservation workers” (square).
- Node “Authoritative seats holds and outbox” (cylinder).
- Node “Idempotent payment request” (square).
- Node “Authenticated callback and reconciliation” (square).
- Node “Status API” (square).
- “Clients and retries” leads to “Edge static cache and abuse controls”. Line: normal; ends: arrow point.
- “Edge static cache and abuse controls” leads to “Admission quotas and waiting room”. Line: normal; ends: arrow point.
- “Admission quotas and waiting room” leads to “Optional Redis advisory capacity”. Line: normal; ends: arrow point.
- “Optional Redis advisory capacity” leads to “Bounded checkout queue”. Line: normal; ends: arrow point.
- “Bounded checkout queue” leads to “Reservation workers”. Line: normal; ends: arrow point.
- “Reservation workers” leads to “Authoritative seats holds and outbox”. Line: normal; ends: arrow point.
- “Authoritative seats holds and outbox” leads to “Idempotent payment request”. Line: normal; ends: arrow point.
- “Idempotent payment request” leads to “Authenticated callback and reconciliation”. Line: normal; ends: arrow point.
- “Authenticated callback and reconciliation” leads to “Authoritative seats holds and outbox”. Line: normal; ends: arrow point.
- “Authoritative seats holds and outbox” leads to “Status API”. Line: normal; ends: arrow point.
- “Status API” leads to “Clients and retries”. Line: normal; ends: arrow point.
The original diagram's “only 100k requests proceed” was stronger than a cache-backed distributed flow can generally guarantee. Retries, cancellations, counter resets and failover can create more or fewer admissions. The database enforces ownership even when the approximate funnel is wrong.
1. The Edge (Static Content)
Cache versioned venue assets and public event information; prewarm only when useful and size origin misses. A CDN does not absorb every request: cache misses, dynamic availability and authenticated checkout still reach controlled backends. Set cache policy deliberately and avoid caching personalized checkout responses in a shared cache. Apply request-size limits, bot controls and bounded retries before expensive account or inventory work.
2. The Redis Funnel (Inventory Cache)
An atomic script on a correctly scoped Redis key can check/decrement an advisory count as one operation at that server. It cannot atomically commit a relational reservation plus a queue send plus payment. A network timeout after decrement creates an ambiguous result; blindly decrementing again on retry can leak admission capacity. Use stable admission IDs and a recoverable record/expiry policy.
A count of zero should mean “not currently admitting” unless the authoritative inventory confirms sold out. Reconcile from durable holds/sales; include cancellation and expiry events idempotently. A stale high counter permits false positives that the database rejects; a stale low counter denies potential buyers and harms liveness. Measure both, including failover. No single claimed Redis operations/s figure establishes safe capacity for this whole path.
Redis is optional: a waiting-room/admission service, per-event serialized queue or another bounded mechanism can serve the role. Do not add a second authority whose counter must be perfectly synchronized with the first to prevent overselling.
3. The Queue
A queue acknowledgement says the attempt is accepted for processing under a stated deadline, not that a seat is reserved. Store an attempt ID and queryable status. Bound queue age and size to the user wait objective; if arrivals exceed completion capacity indefinitely, reject or defer explicitly. SQS standard queues can deliver duplicate messages, so workers must use the stable attempt identity and an idempotent reservation transaction.
A crash after database commit but before queue acknowledgement must not reserve another seat on redelivery. Persist the attempt-to-reservation mapping in the transaction. A dead-letter record needs operational ownership and user-visible resolution; “queued safely” is not a final outcome.
4. The Database (The Source of Truth)
Use a short transaction to establish a hold. One approach locks the selected seat rows with SELECT ... FOR UPDATE, checks current state and records the reservation. Another uses a conditional UPDATE ... WHERE state = 'available' and checks affected rows, combined with uniqueness/check constraints and an appropriate isolation level. In PostgreSQL Read Committed, a concurrent updater may wait and then re-evaluate the condition against the updated row; this is not “no database locks.”
For multiple seats, lock in a consistent order to reduce deadlocks, enforce the group in one transaction and retry aborted transactions from the beginning with a bounded policy. Serializable isolation can abort conflicting work; the application must handle retries. Pessimistic locking is useful under some contention patterns but is not mandatory for correct limited inventory. Optimistic compare-and-set can be correct if conflicts are bounded and failed attempts do not trigger an unbounded retry storm.
Commit the hold before contacting the payment provider. Record reservation ID, owner, state, expiry, version/fencing token, amount/currency and a stable payment operation ID. Release database locks at transaction end. Then send payment outside the database transaction, using the provider's supported idempotency contract. A network timeout does not prove payment failure; query/reconcile the same operation rather than inventing a new charge.
A verified callback records the provider event idempotently and attempts a guarded state transition. Success confirms only the still-owned, unexpired hold under the chosen policy. A late success after reassignment becomes refund/reconciliation work, never authority to steal the new owner's seat. A transactional outbox can persist confirmation/refund actions with the state change; a dispatcher retries effects without losing the audit trail.
Optimistic vs Pessimistic Locking for Inventory
| Choice | Benefit | Cost and boundary |
|---|---|---|
| Conditional update/version check | Short transaction; stale attempts fail explicitly | Still takes database locks during writes; conflicts require bounded retry or rejection |
| Explicit row locks | A transaction can inspect/update several related rows under locks | Waits, deadlocks and connection occupancy remain; use timeouts and consistent ordering |
| Serialize by event/seat key | Reduces competing writers and can simplify ordering | Hot-key throughput and failover/fencing become central |
Choose with invariants, contention measurements and recovery tests. “Optimistic always catastrophically fails” and “pessimistic guarantees no thrashing” are both misleading.
Tracing the Constraints
False admission and stale inventory
Reviewer: “Redis said yes, but the chosen seat was already held.”
Answer: The database rejects the reservation without charging. Return a clear conflict or offer alternatives under a bounded policy. Correct the advisory count through idempotent reconciliation. The false-positive rate is a measured product/operational metric, not permission to violate ownership. A cache outage may trigger a stricter admission mode; it must not cause unbounded direct database traffic.
Reservation timeouts and fencing
Reviewer: “The user closed their browser.”
Answer: A durable hold has an expiry, for example five minutes as a scenario. Use an authoritative clock policy and record the expiry when the hold is created. Cleanup changes only the expected state/version/owner. It does not release a long-lived SQL lock; that lock ended when the hold transaction committed. A callback and cleanup racing at the expiry boundary need an explicit rule—for this example, payment must be accepted strictly before expiry.
Reviewer: “Payment succeeds one second after expiry and reassignment.”
Answer: Record the verified payment result, reject confirmation of the old hold, enqueue a refund/reconciliation action with an idempotent identity and notify the user. Do not overwrite current ownership or drop the payment record because the callback was inconvenient. The provider may have charged money even though the seat is no longer available.
Executable SQLite reservation model
The following complete program uses an in-memory SQLite database and synthetic payment events. It demonstrates conditional ownership, expiry, duplicate callback handling and an outbox entry for a late payment. SQLite serializes writes differently from PostgreSQL; this fixture tests the state machine, not PostgreSQL concurrency, a payment service or five million clients.
import sqlite3
class Tickets:
def __init__(self):
self.db = sqlite3.connect(":memory:")
self.db.executescript("""
CREATE TABLE seat(id INTEGER PRIMARY KEY, state TEXT NOT NULL,
hold_id TEXT, owner TEXT, version INTEGER NOT NULL,
expires INTEGER);
CREATE TABLE attempt(id TEXT PRIMARY KEY, seat_id INTEGER NOT NULL,
owner TEXT NOT NULL, version INTEGER NOT NULL);
CREATE TABLE payment(event_id TEXT PRIMARY KEY, hold_id TEXT NOT NULL,
outcome TEXT NOT NULL);
CREATE TABLE outbox(key TEXT PRIMARY KEY, action TEXT NOT NULL);
INSERT INTO seat VALUES(1, 'available', NULL, NULL, 0, NULL);
""")
def reserve(self, hold_id, owner, now, ttl):
if not hold_id or not owner or type(ttl) is not int or ttl <= 0:
raise ValueError("identity and positive integer TTL required")
with self.db:
old = self.db.execute("SELECT owner,version FROM attempt WHERE id=?", (hold_id,)).fetchone()
if old:
if old[0] != owner:
raise ValueError("idempotency identity conflict")
return old[1] # Same attempt receipt, not a claim it is still live.
cur = self.db.execute("""UPDATE seat SET state='held', hold_id=?, owner=?,
version=version+1, expires=? WHERE id=1 AND state='available'""",
(hold_id, owner, now+ttl))
if cur.rowcount != 1:
return None
version = self.db.execute("SELECT version FROM seat WHERE id=1").fetchone()[0]
self.db.execute("INSERT INTO attempt VALUES(?,1,?,?)", (hold_id, owner, version))
return version
def expire(self, hold_id, version, now):
with self.db:
cur = self.db.execute("""UPDATE seat SET state='available',hold_id=NULL,owner=NULL,
version=version+1,expires=NULL WHERE id=1 AND state='held'
AND hold_id=? AND version=? AND expires<=?""", (hold_id, version, now))
return cur.rowcount == 1
def paid(self, event_id, hold_id, version, now):
with self.db:
old = self.db.execute("SELECT hold_id,outcome FROM payment WHERE event_id=?", (event_id,)).fetchone()
if old:
if old[0] != hold_id:
raise ValueError("payment event identity conflict")
return old[1]
cur = self.db.execute("""UPDATE seat SET state='sold',version=version+1
WHERE id=1 AND state='held' AND hold_id=? AND version=? AND expires>?""",
(hold_id, version, now))
# A distinct provider event for an already confirmed hold is not a second sale.
sold = self.db.execute("SELECT state,hold_id FROM seat WHERE id=1").fetchone()
outcome = "confirmed" if cur.rowcount == 1 or sold == ("sold", hold_id) else "refund"
self.db.execute("INSERT INTO payment VALUES(?,?,?)", (event_id, hold_id, outcome))
self.db.execute("INSERT OR IGNORE INTO outbox VALUES(?,?)", (outcome+":"+hold_id, outcome))
return outcome
def state(self):
return self.db.execute("SELECT state,owner,version FROM seat WHERE id=1").fetchone()
t = Tickets()
a = t.reserve("a", "alice", 0, 300)
assert a == 1 and t.reserve("a", "alice", 1, 300) == a
assert t.reserve("b", "bob", 1, 300) is None
assert not t.expire("a", a, 299)
assert t.expire("a", a, 300)
b = t.reserve("b", "bob", 300, 300)
assert b == 3 and not t.expire("a", a, 301)
assert t.paid("late-a", "a", a, 301) == "refund"
assert t.state() == ("held", "bob", 3)
assert t.paid("paid-b", "b", b, 302) == "confirmed"
assert t.paid("paid-b", "b", b, 303) == "confirmed"
assert t.paid("another-b", "b", b, 304) == "confirmed"
assert not t.expire("b", b, 600)
assert t.state() == ("sold", "bob", 4)
assert t.db.execute("SELECT COUNT(*) FROM outbox").fetchone()[0] == 2
print("one owner; stale cleanup rejected; late payment refunded; duplicate callback idempotent")
t.db.close()The model deliberately fixes one seat and treats paid as an already authenticated, amount-checked, successful payment event for the one stable operation associated with that hold. Production must verify signature/account/amount/currency, bind payment operation IDs, distinguish separate duplicate charges from duplicate notifications, and track refund completion/retries. A second actual charge is not made harmless by returning confirmed; it needs its own financial reconciliation. Never expose this method as an unauthenticated callback endpoint.
The attempt table retains an immutable receipt. Replaying an expired attempt returns that same receipt, not a new hold; clients query current status to learn expiry. This distinction prevents “retry” from silently changing the purchase identity.
Failure analysis and capacity review
- Crash before reservation commit: no acknowledged hold; retry the same attempt safely.
- Crash after commit before queue acknowledgement: redelivery finds the stored attempt, not another seat.
- Payment timeout: outcome unknown; reconcile the same provider operation. Do not release and recharge blindly.
- Callback after expiry: guard fails; retain financial record and refund/reconcile.
- Cleanup after confirmation: state/version guard fails; sold seat remains sold.
- Counter drift: adjust admission, never authoritative seat ownership; reconcile duplicate updates idempotently.
- Queue backlog: bound wait age; pauses and cancellation need status semantics and eventual cleanup.
- Database deadlock/serialization failure: rollback and retry the complete transaction within a deadline, not only the last statement.
Instrument admitted/rejected attempts, reservation conflicts, hold age, queue age, payment unknowns, refund backlog, duplicate events and reconciliation discrepancies. A high sales throughput with growing unknown-payment backlog is not a healthy system. Practice failure recovery at the same time as happy-path capacity.
Review drill and worked answers
- Five million attempts, 100,000 seats: must the system process five million inventory transactions? No. Use bounded admission and an advisory view to reject/defer cheaply; keep the seat invariant in the durable authority.
- Is a five-minute hold a five-minute row lock? No. Commit a durable state/expiry record in a short transaction, then release the SQL lock before remote payment work.
- Why can compare-and-set be correct under contention? Exactly one matching update succeeds for the current state/version. Correctness is distinct from throughput; bound unsuccessful retries and measure lock waits.
- A late success arrives after Bob owns Alice's old seat. Do not confirm Alice. Record the financial event and refund/reconcile under policy while retaining Bob's guarded ownership.
- Redis returns zero after a failover, but the database has seats. This is an admission-liveness problem. Reconcile or switch to a safer bounded admission mode, not an overselling justification.
- What proves completion? Reservation and payment states reconcile, user status is final, outbox effects are delivered/idempotent, and no unknown charge or orphaned hold is hidden by an aggregate counter.
What to carry forward
Defend the funnel with a wait budget, not a throughput slogan. Enforce ownership in short durable transactions, keep remote payment outside locks, use stable operation identities and guard every expiry/confirmation with state and version. The local SQLite program tests these transitions; no payment API, Redis/SQS service, PostgreSQL cluster or load test was executed.
Sources and verification boundary
Primary documentation was reviewed against saved raw snapshots on 8 October 2026. Those source-access timestamps describe evidence provenance, not the article's original publication date. Tagged/versioned sources bind API claims to those versions; mutable sources are access-date snapshots. Local programs and design scenarios are separate from HTTP evidence.
- Google SRE: Load Balancing with Client Side Throttling — SRE Book, 2016.
- PostgreSQL: Documentation: 16: 13.2. Transaction Isolation — PostgreSQL 16.
- PostgreSQL: Documentation: 16: 13.3. Explicit Locking — PostgreSQL 16.
- Transaction — SQLite documentation; access-date snapshot.
- Amazon SQS at-least-once delivery - Amazon Simple Queue Service — Amazon SQS; access-date snapshot.
- Idempotent requests | Stripe API Reference — Stripe documentation; access-date snapshot.
No paid service, model download, cloud write, network security probe or deployed service was used. Scoped source compilation and offline static rendering are not global application, browser-interaction or release acceptance.
Pause / Recall / Apply
Can you explain it without the page?
Close the example. Reconstruct the core idea, then change one assumption. Mark complete when you’re ready; you can always undo it.
Stored in this browser only. No account, no sync. Clearing browser data removes your record.