# Two fans click the same seat at the same moment. Who wins? URL: https://systemdesignsimple.com/two-fans-one-seat/ Published: 2026-11-02 Category: System Design Series: System Design, simply, Part 2: Race conditions and strong consistency Publisher: System Design Simple (https://systemdesignsimple.com) ## Short answer Check-then-act leaves a gap in which two transactions both see a row as free. Close it with one conditional UPDATE and branch on the row count: PostgreSQL re-checks the WHERE on the new row version. Use FOR UPDATE when the rule needs app logic, Serializable for rules across rows, a unique constraint as the net, holds that expire on read, and idempotency keys on payment. ## Key facts - **Core bug:** Check-then-act: a decision made on a value that changes before the write - **Default fix:** UPDATE ... WHERE status = 'free' RETURNING; 1 row wins, 0 rows lost - **Read Committed:** A blocked UPDATE re-evaluates its WHERE on the committed new row version - **Repeatable Read:** Concurrent update of the same row fails with SQLSTATE 40001; write skew still possible - **Serializable:** SSI with SIRead predicate locks; catches write skew, needs retry on 40001 - **Holds:** hold_expires_at compared with the database's now(); expired counts as free on read - **Consistency:** Seat ownership must be linearizable on the primary; seat maps may be eventual When two transactions read a row, decide on what they read and then write, the outcome depends on timing, and something that must be exclusive (a seat, a balance, a stock count) can be handed out twice. This article covers what PostgreSQL's isolation levels, row locks and conditional writes do about it, and which consistency model a booking system needs. Interview question: how do you guarantee one seat is sold exactly once when thousands of requests hit it at once? ## Prerequisites **Transactions.** Statements that commit or roll back as one unit. Atomicity (all or nothing) is not isolation (what you see of transactions running at the same time); most database race conditions live in the isolation part, where every transaction commits cleanly and the combined result is still wrong. **Concurrency.** Several app servers with connection pools run requests in parallel against one primary. "At the same moment" means two transactions' lifetimes overlap. Admission control, as in [scaling a ticket sale with load balancing and a virtual waiting room](https://systemdesignsimple.com/50k-fans-5k-seats/), reduces the load but never removes the overlap. **SQLSTATE codes.** `40001` serialization failure and `40P01` deadlock mean "retry the whole transaction"; `55P03` is lock not available; `23505` is a unique violation. ## Definitions > Definition: **Race condition**: a correctness bug whose result depends on the timing or interleaving of concurrent operations. > Definition: **Data race**: two threads access one memory location without synchronisation and at least one writes; a memory-model term (undefined behaviour in C and C++). SQL check-then-act is a race condition with no data race: every statement is synchronised by the database, yet the program is wrong. > Definition: **Check-then-act**: read a condition, act on it in a later step. **Read-modify-write**: read a value, compute a new one in the app, write it back. > Definition: **Lost update**: two transactions write values derived from the same read of a row; the second silently overwrites the first. > Definition: **Write skew**: two transactions read overlapping rows, each checks a rule that holds, each writes a different row, and together they break the rule. > Definition: **Compare-and-set (CAS)**: write only if the current value still equals the expected one, in one indivisible step, and report whether it happened. A conditional `UPDATE ... WHERE` with a row count is CAS on a row. > Definition: **Idempotency key**: a client-chosen unique id for one logical operation, so repeating the request has the effect of sending it once. ## How it works: the gap and the atomic update The naive claim is two statements: `SELECT status`, then, if the app saw `free`, `UPDATE`. The time between the SELECT's snapshot and the UPDATE taking its row lock is the **race window**; any transaction that reads inside it makes the same decision on the same stale fact. [Image: Two code steps: 1, SELECT owner FROM seats WHERE seat = A12, free?; 2, UPDATE seats SET owner = you; with the words two steps | Check-then-act: the decision and the write are two statements with a window between them.] ```text time (ms) session 1 session 2 0 SELECT status ... -> 'free' 5 SELECT status ... -> 'free' 20 UPDATE ... SET holder = 'fan-a' 21 COMMIT 25 UPDATE ... SET holder = 'fan-b' 26 COMMIT final row: holder = 'fan-b'; both sessions told their user "yours" ``` Nothing failed: session 2's unconditional UPDATE overwrote session 1, a lost update. Wrapping both statements in `BEGIN ... COMMIT` changes nothing at the default level, because a plain SELECT takes no lock and the UPDATE does not compare against what was read. [Image: Two side-by-side time lanes: fan A checks and gets free, fan B checks and gets free, then both buy and both show SOLD, stamped SOLD TWICE | Both reads land inside the window, so both writes succeed.] The fix moves the check into the write: ```sql UPDATE seats SET status = 'held', holder = 'fan-a' WHERE event_id = 1 AND seat = 'A12' AND status = 'free' RETURNING seat; ``` This is safe because of a documented Read Committed rule. The UPDATE finds candidate rows with its statement snapshot. If a candidate was updated by a transaction still in progress, it waits on that row's lock. If the first transaction commits, PostgreSQL takes the *updated* version of the row and re-evaluates the WHERE clause against it; `status = 'free'` is now false, the row is skipped and the command reports `UPDATE 0`. If the first rolled back, the second proceeds on the original row. Locally (PostgreSQL 14), session 2 blocked 1.5 s, then returned zero rows. Branch on the result: one row means you won, zero means you lost. Code that ignores the count still has the original bug. [Image: Code box with UPDATE seats SET owner = A WHERE seat = A12 AND owner IS NULL, and results fan A 1 row with a tick, fan B 0 rows, labelled atomic update | CAS on a row: the first update changes 1 row, the second re-checks and changes 0.] Two caveats. The re-check covers the target row only: the command sees concurrent changes to the rows it updates but not to other rows, so a WHERE with a subquery on another table can pass on stale data. And `SET stock = stock - 1 WHERE stock > 0` is safe (recomputed on the new version), while `SET stock = 7`, with 7 computed in the app from an earlier read, is a lost update. ### MVCC in one section PostgreSQL uses **multiversion concurrency control**: an UPDATE writes a new row version (tuple) and marks the old one ended. Each tuple header carries `xmin`, the transaction that created it, and `xmax`, the one that deleted, updated or locked it. A **snapshot** records which transactions count as committed; a tuple is visible if its creator is committed in the snapshot and its ender is not. So readers never block writers and writers never block readers: a reader picks the version its snapshot sees. Two writers on one row do block, because the second must learn whether the first committed. Row locks live in the tuple header, not in shared memory, so there is no limit on rows locked, and individual row locks do not appear in `pg_locks` (a waiter shows as waiting on the holder's transaction id). Read Committed takes a new snapshot per statement; Repeatable Read and Serializable keep the first one for the whole transaction. ## Isolation levels and the anomalies they permit PostgreSQL's table differs from the SQL standard in two places: Read Uncommitted behaves as Read Committed (no dirty reads), and Repeatable Read also forbids phantoms. Lost update and write skew are not in the standard's table; the columns below follow the documented behaviour, each reproduced locally. | Level in PostgreSQL | Dirty read | Non-repeatable read | Phantom | Lost update | Write skew | | --- | --- | --- | --- | --- | --- | | Read Uncommitted (runs as Read Committed) | No | Yes | Yes | Yes, app read-modify-write | Yes | | Read Committed (default) | No | Yes | Yes | Yes, app read-modify-write | Yes | | Repeatable Read (snapshot isolation) | No | No | No | No: 40001 | Yes | | Serializable (SSI) | No | No | No | No: 40001 | No: 40001 | A **dirty read** sees uncommitted data; a **non-repeatable read** gets different committed values for one row; a **phantom** gets a different set of rows for one search. **Read Committed.** Each statement sees everything committed before it began. Single-statement CAS is safe through the re-evaluation; two statements in one transaction can see two different committed states. **Repeatable Read.** One snapshot for the transaction. If it updates or locks a row that a concurrent transaction updated and committed after the snapshot, there is no re-evaluation: it fails with `could not serialize access due to concurrent update`. "First updater wins" turns naive check-then-act into an error instead of a double sale; locally, both sessions read `free`, the first committed, the second got the error. **Write skew survives it.** Rule: at most 4 seats per fan; the fan holds 3. Two sessions each count 3 and each claim a *different* seat. No row was written by both, so first-updater-wins never fires and the fan ends with 5 seats. Snapshot isolation gives each transaction a consistent view, but nothing checks that the two writes together still obey the rule. **Serializable** is **Serializable Snapshot Isolation (SSI)**: snapshot isolation plus dependency tracking. Reads take `SIRead` predicate locks that block nobody; they record what was read (tuple, page or relation, by plan). When the dependencies form a "dangerous structure" (two consecutive read/write conflicts) that no serial order could produce, one transaction fails with `could not serialize access due to read/write dependencies among transactions`. The same test locally: the second session was cancelled and the fan kept 4. The contract: a transaction correct alone is correct in any mix, or it does not commit. Costs: retries, predicate-lock memory and false positives, more on sequential scans (relation-level locks), so index the queries. A 40001 can arrive on any statement, including `COMMIT`. Retry the whole transaction from its first read, with backoff and a cap. ## Row locks, SKIP LOCKED, NOWAIT and deadlocks When the rule needs app logic, lock on read. `SELECT ... FOR UPDATE` behaves like an UPDATE when it meets a locked row: it waits, re-evaluates its condition on the new version and returns that version or nothing. This is **pessimistic** concurrency: lock, decide, write, commit. There are four row lock strengths. `FOR UPDATE` conflicts with all. An ordinary UPDATE that changes no key column takes `FOR NO KEY UPDATE`, which still allows `FOR KEY SHARE`, the lock a foreign-key check takes, so inserting a payment that references a seat does not wait on an update of that seat's status. - **Wait** (default): block until the holder ends, with no limit unless you `SET LOCAL lock_timeout`, which fails with 55P03. - **`NOWAIT`**: fail at once with `could not obtain lock on row` (55P03), for "someone is checking out this seat" without making the user wait. - **`SKIP LOCKED`**: skip rows others have locked. With `ORDER BY ... LIMIT n` it hands out "any n free seats" or "the next n jobs" without queueing; locally, session 1 took A1 and A10 and session 2 got A11 and A12 at once. The view is deliberately inconsistent: right for queues and best-available, wrong for reports. **Deadlocks.** Session 1 locks A1 and wants A2; session 2 locks A2 and wants A1. After waiting `deadlock_timeout` (default 1 s) PostgreSQL checks for a cycle and aborts one, unpredictably, with 40P01. Lock in a fixed order (`WHERE seat = ANY($1) ORDER BY seat FOR UPDATE`) and keep the retry loop. The cost of pessimism: the lock lives as long as the transaction. Hold it across a 2 s payment call and each buyer behind you waits 2 s more. ## Versions, constraints, holds and idempotency ### Optimistic concurrency When read and write are minutes apart (an edit form), read a `version` and write `WHERE id = $1 AND version = $2`, setting `version = version + 1`. Zero rows means someone wrote in between: re-read and retry or show a conflict. Cheap when conflicts are rare, a retry storm when common. For a hot seat, the state condition `status = 'free'` is better: losers fail once and have nothing to retry. ### Unique constraints A unique index binds every writer: the API, an admin script, a backfill, next year's refactor. With `UNIQUE (event_id, seat)` on `tickets`, two tickets for one seat are impossible. Concurrent inserts of one key serialise too: the second waits, then fails with 23505 (or proceeds if the first rolled back); `ON CONFLICT DO NOTHING` turns that into a row count. It can only enforce key uniqueness, so it is a net beneath the claim and the claim still does the work. ### Holds, expiry and clocks Checkout takes minutes, so the click claims a **hold**: `status = 'held'`, `holder`, `hold_expires_at = now() + interval '10 minutes'`. Expiry is *derived on read*: the claim accepts "free, or held with `hold_expires_at <= now()`", and every display query uses the same predicate. A stalled cleanup job then harms nothing. [Image: Countdown ring at 00:23 for A12 on hold, with two outcomes: paid in time leads to SOLD, timeout leads to FOR SALE, labelled reservation with a timeout | A hold is a state with an expiry; the expiry is a comparison in the query, not a job.] Compare against the database's clock, in the same statement. App servers' clocks differ and step under NTP; one host writing a timestamp and another judging it is a race. `now()` is fixed at transaction start, `statement_timestamp()` at statement start, `clock_timestamp()` moves even within a statement. Use `now()` in short claims, `statement_timestamp()` in a long transaction. ### Idempotency on payment Requests arrive at least once: a double tap, a retry after a lost response, a webhook delivered twice. Exactly-once effects are at-least-once delivery plus deduplication. One key per checkout, sent with every attempt, inserted as a primary key with `ON CONFLICT DO NOTHING RETURNING`: a row back means first attempt, none means a repeat, so return the stored result. Pass the same key to the provider (Stripe accepts an `Idempotency-Key` header) so the charge happens once. The confirm is another CAS, held by this fan and unexpired becomes sold; zero rows means the hold lapsed during payment, so refund instead of forcing the sale. ### Why distributed locks are rarely needed A Redis or ZooKeeper lock on "seat:A12" is a second system guarding a row whose database can already decide atomically, and it is a **lease**. A holder pauses (a stop-the-world garbage collection, a VM migration, a slow network), the lease expires, another process takes it, and the first wakes up still believing it holds the lock. Both write. Pauses are unbounded, so timeouts cannot exclude this. The remedy in the distributed-locking analysis under Further reading is a **fencing token**: a strictly increasing number per grant, carried by every write, with the storage rejecting tokens older than one it has seen. At that point the storage makes the decision with a conditional write, which you could have used directly. Use such locks for efficiency only. ## Consistency models Isolation levels describe one database; consistency models describe what clients observe of a replicated system (Jepsen's definitions): **Linearizability**: single objects, real time. Each operation appears to take effect at one instant between its start and completion, so after a write completes every later read sees it. This is "sold is sold". **Serializability**: transactions over many objects equal *some* serial order, with no real-time promise; your transaction may be ordered before one that finished earlier. **Strict serializability** adds the real-time order. **Eventual consistency**: replicas converge once writes stop; meanwhile reads may be old and readers may disagree. [Image: Two phones for seat A12: fan A's shows Yours with a tick, fan B's shows Taken. Pick another, labelled strong consistency | Ownership must look the same to every reader the moment it is decided.] So claims, confirms and ownership reads go to the primary. A streaming replica applies WAL after a delay, so checking there is check-then-act with a window of milliseconds to seconds. `synchronous_commit = remote_apply` makes the primary wait until a synchronous standby has applied a commit before acknowledging it, but the decision should still be a CAS on the primary. Display data (map colours, "about 120 left") may come from a replica or cache: if stale, the claim returns 0 rows and the user picks again. A feed is the opposite case, almost all eventual, as in [designing a feed with fan-out on write or on read](https://systemdesignsimple.com/design-instagram-feed/). [Image: Two rule cards: a like count, almost right is fine; a seat or money, must be exact | Choose the model per data item: counts may be eventual, ownership and money may not.] ## Worked example 50,000 buyers, 5,000 seats, sale at 10:00; fan A and fan B click seat A12 together. How often does the naive claim collide, and what does each fix cost? Assumed numbers are round guesses, not measurements. ```text Buyers per seat (average) 50,000 / 5,000 = 10 Race window, check to write 20 ms (assumed) = 0.02 s Hot seat: 30 clicks in the first 3 s (assumed) Click rate on that seat 30 / 3 s = 10 /s Expected other clicks in a window 10 x 0.02 = 0.2 P(at least one), Poisson 1 - e^(-0.2) = 1 - 0.819 = 0.18 100 hot front-row seats 100 x 30 = 3,000 clicks Clicks overlapping another 3,000 x 0.18 = about 540 Conditional UPDATE, lock held about 1 ms (assumed) Worst wait for the 30th click 30 x 1 ms = about 30 ms FOR UPDATE held across a 2 s payment call (assumed) Worst wait for the 30th click 29 x 2 s = 58 s Abandoned checkouts: 30% (assumed) 5,000 x 0.30 = 1,500 seats No expiry: unsold for good 1,500 / 5,000 = 30% of the hall Expiry only by a 60 s cleanup job: extra time held, worst case = 60 s, mean 30 s Expiry derived on read: extra time held = 0 s ``` A faster server shrinks the 18%, never to zero; the CAS removes the window. ## Code Tested on PostgreSQL 14; the behaviour is unchanged in current versions. Open two `psql` sessions on a scratch database. Run the reset before each demo. ```sql CREATE TABLE seats ( event_id integer NOT NULL, seat text NOT NULL, status text NOT NULL DEFAULT 'free' CHECK (status IN ('free', 'held', 'sold')), holder text, hold_expires_at timestamptz, version integer NOT NULL DEFAULT 0, PRIMARY KEY (event_id, seat) ); INSERT INTO seats (event_id, seat) SELECT 1, 'A' || n FROM generate_series(1, 20) AS n; -- Safety net: one ticket per seat, whatever the code does. CREATE TABLE tickets ( event_id integer NOT NULL, seat text NOT NULL, fan text NOT NULL, UNIQUE (event_id, seat) ); -- One row per checkout, keyed by the client's idempotency key. CREATE TABLE payments ( idempotency_key text PRIMARY KEY, event_id integer NOT NULL, seat text NOT NULL, fan text NOT NULL, amount_cents integer NOT NULL CHECK (amount_cents > 0), status text NOT NULL DEFAULT 'started', created_at timestamptz NOT NULL DEFAULT now(), FOREIGN KEY (event_id, seat) REFERENCES seats (event_id, seat) ); -- Reset between demos. UPDATE seats SET status = 'free', holder = NULL, hold_expires_at = NULL, version = 0; ``` **The claim.** Session 1: `BEGIN;` and the statement, left open. Session 2: the same with `'fan-b'`. Observe: session 2 blocks; when session 1 commits, it returns no rows, `UPDATE 0`. ```sql UPDATE seats SET status = 'held', holder = 'fan-a', hold_expires_at = now() + interval '10 minutes', version = version + 1 WHERE event_id = 1 AND seat = 'A12' AND (status = 'free' OR (status = 'held' AND hold_expires_at <= now())) RETURNING seat, holder, hold_expires_at; ``` **Repeatable Read.** Both sessions run the first two lines; session 1 runs the UPDATE and commits; then session 2 runs its UPDATE. Observe: `could not serialize access due to concurrent update`; A12 keeps `fan-a`. ```sql BEGIN ISOLATION LEVEL REPEATABLE READ; SELECT status FROM seats WHERE event_id = 1 AND seat = 'A12'; UPDATE seats SET status = 'sold', holder = 'fan-a' -- 'fan-b' in session 2 WHERE event_id = 1 AND seat = 'A12'; COMMIT; ``` **Write skew.** Give fan-a three seats, then both sessions run BEGIN and the count (3 each); session 1 claims A4 and commits; session 2 claims A5 and commits. Observe: at `REPEATABLE READ` the fan ends with 5; at `SERIALIZABLE`, as written, session 2 fails with `read/write dependencies among transactions` and the fan keeps 4. ```sql UPDATE seats SET status = 'held', holder = 'fan-a', hold_expires_at = now() + interval '10 minutes' WHERE event_id = 1 AND seat IN ('A1', 'A2', 'A3'); BEGIN ISOLATION LEVEL SERIALIZABLE; SELECT count(*) AS held_by_fan FROM seats WHERE event_id = 1 AND holder = 'fan-a' AND (status = 'sold' OR hold_expires_at > now()); -- app: continue only if held_by_fan < 4 UPDATE seats SET status = 'held', holder = 'fan-a', hold_expires_at = now() + interval '10 minutes' WHERE event_id = 1 AND seat = 'A4' AND status = 'free'; -- 'A5' in session 2 COMMIT; ``` **Best available and failing fast.** Session 1 runs the first block and stays open; session 2 runs it and gets the next two seats at once, then commits and runs the last two statements. Observe: both fail with 55P03, since session 1 still holds A1. ```sql BEGIN; SELECT seat FROM seats WHERE event_id = 1 AND status = 'free' ORDER BY seat LIMIT 2 FOR UPDATE SKIP LOCKED; -- ... UPDATE the returned seats to held, then COMMIT SELECT seat FROM seats WHERE event_id = 1 AND seat = 'A1' FOR UPDATE NOWAIT; -- ERROR: could not obtain lock on row in relation "seats" BEGIN; SET LOCAL lock_timeout = '200ms'; UPDATE seats SET version = version + 1 WHERE event_id = 1 AND seat = 'A1'; -- ERROR: canceling statement due to lock timeout ROLLBACK; ``` **Optimistic version.** The second statement carries the stale version and changes 0 rows. ```sql SELECT status, version FROM seats WHERE event_id = 1 AND seat = 'A13'; -- version 0 UPDATE seats SET holder = 'fan-a', version = version + 1 WHERE event_id = 1 AND seat = 'A13' AND version = 0 RETURNING version; -- 1 row UPDATE seats SET holder = 'fan-b', version = version + 1 WHERE event_id = 1 AND seat = 'A13' AND version = 0 RETURNING version; -- 0 rows ``` **Pay and confirm.** Run the claim for fan-a first. Observe: the INSERT returns the key once and nothing on a repeat; the confirm sells the seat and writes the ticket in one statement; a second ticket for A12 fails with a unique violation. ```sql INSERT INTO payments (idempotency_key, event_id, seat, fan, amount_cents) VALUES ('chk-7f3a9c', 1, 'A12', 'fan-a', 8000) ON CONFLICT (idempotency_key) DO NOTHING RETURNING idempotency_key; -- no row back: a repeat; SELECT status FROM payments WHERE idempotency_key = ... -- after the provider confirms the charge (outside any transaction): BEGIN; WITH sold AS ( UPDATE seats SET status = 'sold', hold_expires_at = NULL, version = version + 1 WHERE event_id = 1 AND seat = 'A12' AND status = 'held' AND holder = 'fan-a' AND hold_expires_at > now() RETURNING event_id, seat, holder ) INSERT INTO tickets (event_id, seat, fan) SELECT event_id, seat, holder FROM sold RETURNING seat, fan; -- 1 row: mark the payment confirmed and COMMIT. -- 0 rows: the hold lapsed; ROLLBACK and refund. UPDATE payments SET status = 'confirmed' WHERE idempotency_key = 'chk-7f3a9c'; COMMIT; INSERT INTO tickets (event_id, seat, fan) VALUES (1, 'A12', 'fan-b'); -- ERROR: duplicate key value violates unique constraint "tickets_event_id_seat_key" -- What the seat map shows: expiry derived on read. SELECT seat, CASE WHEN status = 'sold' THEN 'sold' WHEN status = 'held' AND hold_expires_at > now() THEN 'held' ELSE 'free' END AS shown_as FROM seats WHERE event_id = 1 ORDER BY seat; -- Housekeeping only; correctness never depends on it. UPDATE seats SET status = 'free', holder = NULL, hold_expires_at = NULL WHERE status = 'held' AND hold_expires_at <= now(); ``` ## Failure modes and edge cases **Ignored row counts.** This is the form most often seen in production. Detect it with a scheduled invariant query (seats sold, tickets issued and payments confirmed per event must agree) and alert on any difference. **Hot rows.** Every buyer of the front-row seat queues on one lock, so the queue, not CPU, sets latency. Watch `pg_stat_activity` for `wait_event_type = 'Lock'` and enable `log_lock_waits` (waits longer than `deadlock_timeout`). Keep the claim one short statement. **Retry storms.** Under heavy contention retries can outnumber work; use jittered backoff with a cap. **Long transactions.** An ORM session spanning a request, or a payment call inside a transaction, holds locks and blocks vacuum. Set `idle_in_transaction_session_timeout` and `statement_timeout`. **Poolers.** In transaction pooling a session `SET` leaks to the next client; use `SET LOCAL`. **Paid after expiry.** Charged at 10:09:58, confirmed at 10:10:01, 0 rows. Correct: refund or offer another seat, and count these. **Failover.** With asynchronous replication a crash can lose acknowledged commits, so the promoted replica may show a sold seat as free. Whether that is acceptable is a non-functional requirement to settle early, as in [writing functional and non-functional requirements and SLOs](https://systemdesignsimple.com/ride-app-requirements/). ## Trade-offs | Option | Good for | Costs you | Use when | | --- | --- | --- | --- | | Conditional UPDATE (CAS) | Claiming one row: seat, coupon, stock | Rule must fit one WHERE on the target row | Default for "only one may win" | | SELECT ... FOR UPDATE | App logic over locked rows | Lock held for the transaction; deadlocks | Short transaction, fixed lock order | | FOR UPDATE SKIP LOCKED | Best available, job queues | Inconsistent view | Any one of many rows will do | | Optimistic version | Long read-to-write gaps | Retries under contention | Edit forms, rare conflicts | | Repeatable Read | Consistent reads; same-row lost updates | 40001 retries; write skew remains | Reports, single-row read-modify-write | | Serializable (SSI) | Rules across rows | Retries, false positives, lock memory | Invariants no row or constraint can hold | | Unique constraint | Every code path | 23505 to handle | Always, beneath the main mechanism | | Distributed lock (lease) | Work outside one database | Extra system; leases expire under pauses | Efficiency only, or with fencing | ## Common mistakes - **Mistake:** believing `BEGIN ... COMMIT` around SELECT and UPDATE prevents the race. **Instead:** put the condition in the UPDATE, lock with `FOR UPDATE`, or raise the level with retries. - **Mistake:** raising the isolation level with no retry loop. **Instead:** retry the whole transaction on 40001 and 40P01, including failures at COMMIT. - **Mistake:** assuming Repeatable Read protects multi-row rules. **Instead:** per-fan limits are write skew; use Serializable, a parent-row lock or a constraint. - **Mistake:** calling a payment API while holding a row lock. **Instead:** hold in a short transaction, pay outside it, confirm with a second CAS. - **Mistake:** judging expiry by the app's clock or only by a cleanup job. **Instead:** compare with the database's `now()` in the claim and every read. - **Mistake:** adding a Redis lock "to be safe" and trusting its TTL. **Instead:** let the database decide; if a lock is unavoidable, check a fencing token in storage. ## In an interview **Clarify:** assigned seats or general admission, checkout length, per-buyer limits. **Assumptions:** one PostgreSQL primary for seat state, a waiting room, 10-minute holds. **Structure:** map (eventual), claim (CAS), hold, pay (idempotent), confirm (CAS), ticket (unique). **Deep dive:** the lost update, Read Committed re-evaluation, the row count, SKIP LOCKED. **Trade-offs:** pessimistic versus optimistic, Repeatable Read versus Serializable, no distributed lock. **Wrap up:** invariant monitoring, retries, replicas and failover. > **A 60-second answer**Reading the seat and then writing it is check-then-act, so two transactions can both see it free. I close the window with one conditional UPDATE: held where free or the hold expired by the database's now(), with RETURNING, branching on the row count. At Read Committed a concurrent UPDATE waits on the row lock, re-evaluates the WHERE on the committed version and gets zero rows. Best available uses FOR UPDATE SKIP LOCKED. A four-per-buyer limit is write skew, so that path runs Serializable with retries on 40001. Payment carries an idempotency key; the confirm is a second CAS inserting into tickets with a unique (event, seat) constraint; a lapsed hold means refund. Claims and ownership reads go to the primary; the map can be eventual. ## Practice **Q (Beginner): After a conditional UPDATE, how does the app know it won?** The affected row count or the RETURNING rows: one row means it changed the seat, zero means someone else did. **Q (Intermediate): Give a race condition with no data race.** SELECT then UPDATE from two sessions: each statement is synchronised by the database, yet the interleaving sells a seat twice. **Q (Intermediate): At Read Committed, session 2's UPDATE ... WHERE status = 'free' waits on session 1, which commits status = 'held'. What happens?** The WHERE is re-evaluated on the updated version, fails, and session 2 reports UPDATE 0. Had session 1 rolled back, session 2 would update the original row. **Q (Intermediate): Why is SET stock = stock - 1 safe but SET stock = 7, computed in the app, not?** The expression is recomputed on the newest version after the wait; the literal overwrites a concurrent change. **Q (Advanced): Why does Repeatable Read stop a double sale but not a fifth seat under a four-seat limit?** First-updater-wins fires only on the same row. Each transaction writes a different seat: write skew, permitted by snapshot isolation and caught by SSI. **Q (Advanced): Transactions lock seats in click order. What breaks?** Opposite orders deadlock; one is aborted with 40P01 after deadlock_timeout. Lock in canonical order and retry. **Q (Advanced): Why is a lease lock unsafe alone?** A paused holder outlives its lease while another acquires it. A fencing token checked by storage rejects the stale write. **Q (Advanced): Linearizable or serializable: which does "sold is sold" need?** Linearizability of the ownership record. Serializability alone permits an order inconsistent with real time; claims and reads on one primary give the needed behaviour. ## Cheat sheet | Term or formula | Meaning | | --- | --- | | Race condition / data race | Timing-dependent bug / unsynchronised memory access with a write | | Check-then-act, read-modify-write | Decide or compute on a read, write later | | Lost update / write skew | Overwrite from a stale read / rule broken by writes to different rows | | CAS, conditional UPDATE | Write only if the expected state holds; read the row count | | Read Committed re-check | Blocked UPDATE re-evaluates WHERE on the new version | | Repeatable Read | One snapshot; same-row concurrent update raises 40001 | | Serializable (SSI) | SIRead locks detect dangerous structures; 40001 | | MVCC | New tuple per update, xmin/xmax, snapshot visibility | | FOR UPDATE / NOWAIT / SKIP LOCKED | Lock and wait / fail (55P03) / skip locked rows | | Deadlock | Lock cycle, checked after deadlock_timeout (1 s), 40P01 | | Optimistic concurrency | WHERE version = expected, SET version = version + 1 | | Hold | Expired (hold_expires_at <= now()) counts as free on read | | now() / statement_timestamp() / clock_timestamp() | Transaction start / statement start / wall clock | | Idempotency key, fencing token | Dedup repeats / reject stale lock holders | | Linearizable / serializable / eventual | Real-time single object / some serial order / converges later | | P(collision) per click | 1 - e^(-rate x window) | ## Further reading - [PostgreSQL: Transaction Isolation](https://www.postgresql.org/docs/current/transaction-iso.html): the anomaly table, the re-evaluation rule, SSI. - [PostgreSQL: Explicit Locking](https://www.postgresql.org/docs/current/explicit-locking.html): row lock strengths, deadlock detection. - [PostgreSQL: SELECT, the locking clause](https://www.postgresql.org/docs/current/sql-select.html): FOR UPDATE, NOWAIT, SKIP LOCKED and their caveats. - [PostgreSQL: Introduction to MVCC](https://www.postgresql.org/docs/current/mvcc-intro.html): why readers and writers do not block each other. - [How to do distributed locking](https://martin.kleppmann.com/2016/02/08/how-to-do-distributed-locking.html): leases, process pauses and fencing tokens. - [Jepsen: Consistency Models](https://jepsen.io/consistency): precise definitions from strict serializable to eventual. This is a simplified, general explanation of common patterns (a typical design), not a description of any one company's private systems. Numbers marked assumed are round examples for learning, not measurements. ## Frequently asked questions **What is the difference between a race condition and a data race?** A data race is a memory-model term: two threads touch the same memory location without synchronisation and at least one writes. A race condition is any bug whose result depends on timing. SQL check-then-act has no data race but is still a race condition. **Why does UPDATE ... WHERE status = 'free' not sell a seat twice at Read Committed?** The second UPDATE blocks on the row lock. When the first commits, PostgreSQL re-evaluates the WHERE clause against the updated row version, finds status no longer free and skips the row, so it reports UPDATE 0. **Does Repeatable Read prevent lost updates in PostgreSQL?** Yes for updates of the same row: if a concurrent transaction committed an update to a row you then try to update, you get could not serialize access due to concurrent update (SQLSTATE 40001) and must retry. It does not prevent write skew across different rows. **When should I use SELECT ... FOR UPDATE SKIP LOCKED?** When any one of many rows will do, such as best available seat or a job queue. Each transaction locks rows nobody else has locked and skips the rest, so workers do not queue behind each other. **Do I need a distributed lock for seat booking?** Rarely. The seat row lives in one database that can decide atomically. A lease-based lock can expire while a paused process still believes it holds it, so the storage must check a fencing token anyway. **Which clock should decide whether a hold has expired?** The database's now(), in the same statement that claims or confirms. App servers' clocks drift and differ, and comparing a timestamp written by one machine with a clock on another reintroduces a race. ## Sources - [PostgreSQL documentation: Transaction Isolation](https://www.postgresql.org/docs/current/transaction-iso.html) - [PostgreSQL documentation: Explicit Locking](https://www.postgresql.org/docs/current/explicit-locking.html) - [PostgreSQL documentation: SELECT, the locking clause](https://www.postgresql.org/docs/current/sql-select.html) - [PostgreSQL documentation: Introduction to MVCC](https://www.postgresql.org/docs/current/mvcc-intro.html) - [How to do distributed locking (Martin Kleppmann)](https://martin.kleppmann.com/2016/02/08/how-to-do-distributed-locking.html) - [Jepsen: Consistency Models](https://jepsen.io/consistency)