Two fans click the same seat at the same moment. Who wins?

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, 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

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.

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.

The fix moves the check into the write:

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

Trade-offs

Common mistakes

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.

Practice

Cheat sheet

Further reading

• PostgreSQL: Transaction Isolation: the anomaly table, the re-evaluation rule, SSI.
• PostgreSQL: Explicit Locking: row lock strengths, deadlock detection.
• PostgreSQL: SELECT, the locking clause: FOR UPDATE, NOWAIT, SKIP LOCKED and their caveats.
• PostgreSQL: Introduction to MVCC: why readers and writers do not block each other.
• How to do distributed locking: leases, process pauses and fencing tokens.
• Jepsen: Consistency Models: 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.