Start practising

SYSTEM DESIGN · PATTERNS · 4 OF 7

Dealing with Contention: Many Users, One Seat, No Double Booking

What to do when many requests race for the same thing: the last seat, the last item in stock, a wallet balance. Why read-check-write oversells, then the standard moves: atomic conditional updates, pessimistic locks, optimistic versioning, holds with expiry, distributed locks with fencing, and single-writer queues for flash sales, with the trade-offs and interview phrasing.

  1. 1
  2. 2
  3. 3
  4. 4
  5. 5
  6. 6
  7. 7
1 / 15

Press play to hear the narration, or step through at your own pace.

Notes

The signals

The signal is a scarce resource that many people can change at the same time. Phrases like no double booking, limited stock, the last item. Seats, hotel rooms, doctor slots, auction bids, account balances, a single-use coupon. And the extreme form: a flash sale, where a million people chase a thousand items in one minute.

Signals in the question
  • "no double booking"
  • limited stock, last item
  • seats, rooms, time slots
  • auction bids, order books
  • wallet or account balance
  • unique usernames, coupons
  • flash sale: 1M users,
  • 1,000 items, 1 minute

The naive design

The naive code reads the seat, checks it is free, then writes booked. Run two of those at once and both reads see free before either write lands. Both writes succeed, both users get a confirmation, and one seat is sold twice. Adding more app servers only makes the window easier to hit. The check and the write must become one indivisible step.

The race
A: SELECT status → free
B: SELECT status → free
A: UPDATE status = booked
B: UPDATE status = booked

both get a confirmation
one seat, two tickets

more servers make it worse,
not better

Move 1: atomic update

Move one, and often all you need: make the check part of the write. Update the seat to booked only where its status is still free, then look at how many rows changed. One row means you won; zero means someone beat you. The database does that atomically on a single row. For stock, decrement only where the count is above zero. Back it with a unique constraint as a final safety net.

Conditional write
UPDATE seats
  SET status = 'booked', user = ?
  WHERE id = '14C'
    AND status = 'free';
-- 1 row: you got it
-- 0 rows: someone beat you

stock: SET n = n - 1
       WHERE id = ? AND n > 0
plus UNIQUE (show_id, seat_id)

Move 2: pessimistic lock

When the decision needs several reads and writes, use a pessimistic lock. Inside a transaction, select the row for update. Anyone else trying to lock or update that row waits until you commit. Then check your business rules, update the seat, and insert the booking. Keep the transaction short, and when you lock several rows, always lock them in the same order so two transactions cannot deadlock. Concepts episode eighteen covers isolation levels.

SELECT ... FOR UPDATE
BEGIN;
SELECT * FROM seats
  WHERE id = '14C' FOR UPDATE;
-- B now waits here
check rules, price, limits
UPDATE seats SET ...;
INSERT INTO bookings ...;
COMMIT;  -- B wakes, sees booked

lock rows in a fixed order
to avoid deadlocks

Move 3: optimistic

Move three: optimistic concurrency. Every row carries a version number. You read it, do your work without holding any lock, and write back only if the version is still the one you read, bumping it as you go. Zero rows updated means someone changed it first, so reread and retry. It is excellent when conflicts are rare, like editing your own profile, and terrible when a thousand people fight over one row.

Move 3: optimistic
read:  balance = 100, version = 7
compute new balance = 60
UPDATE accounts
  SET balance = 60, version = 8
  WHERE id = ? AND version = 7;
-- 0 rows → reread and retry

no locks held while thinking
great when conflicts are rare
terrible when they are common

Choosing

How to choose. If the whole decision fits in one row, the atomic conditional update wins: no locks held, no retries. For multi-step logic on a contended row, pessimistic locking keeps it simple. When conflicts are rare and the work is slow, use optimistic versions. On a truly hot row, locks form long queues and optimistic retries turn into a storm, which is where the next moves come in.

Which one?
             atomic  pessim  optim
one row      best    ok      ok
multi-step   -       best    ok
rare clash   best    ok      best
hot row      ok      queues  retries
                      up      storm
holds locks  no      yes     no

start with the atomic update

Move 4: holds

Move four: holds. Checkout takes minutes, and you cannot keep a database lock open while someone types a card number. Instead, a conditional update marks the seat held by user A until ten minutes from now. Payment flips it from held to booked only if A still holds it. Expired holds are treated as free in the query itself, and a background sweeper tidies them. This is how ticketing and hotel sites work.

Reservations with expiry
  • free → held → booked
  • hold: conditional update
  • status = held,
  • held_until = now + 10 min
  • pay → booked (if still
  • held by you)
  • expired holds count as free
  • in the WHERE clause
  • a sweeper tidies them up

Move 5: distributed lock

Move five: a distributed lock, for when the contended thing is not a single database row, like a file, a third-party API call, or a scheduled job. Set a key only if it does not exist, with an expiry so a crashed holder cannot block forever. The danger is a holder that pauses past its lease while a second one starts. Fencing tokens fix that: the storage rejects any write carrying an older token. See Concepts episode thirty-one.

Distributed lock
SET lock:key owner NX PX 30000
→ only one holder, auto-expires

danger: holder pauses (GC),
lease expires, second holder
starts, both write

fencing token: increasing id,
  e.g. INCR or etcd revision
storage rejects token 32
after it has seen 33

Move 6: single writer

Move six: remove concurrency altogether. Partition requests by item ID so every request for item seven lands in the same queue partition, consumed by exactly one worker. It handles them in order, one at a time, with no locks and no races. Stock exchange matching engines work like this. The cost is that the result is asynchronous, so the client waits for a push or polls for the outcome.

Serialise by key
  • partition requests by item_id
  • (Kafka partition per key)
  • one consumer owns each item
  • it processes them in order:
  • no locks, no races
  • used by order-matching
  • engines and flash sales
  • cost: async; client polls
  • or waits for a push

Extreme contention

At the extreme, a flash sale, a million users want a thousand items. Do not let them all reach the database. A virtual waiting room at the edge admits a few thousand a second. An atomic decrement in Redis acts as the gate, handling around a hundred thousand operations a second; anyone who drives it below zero is told sold out. Winners get a hold and confirm in the database. If even that key is too hot, split the stock into buckets.

Flash sale: 1M users, 1k items
  • virtual waiting room at the
  • edge: admit 5,000/s
  • gate in Redis:
  • DECR stock:item7 → if < 0,
  • sold out (atomic, ~100k/s)
  • winners get a hold token,
  • then pay and confirm in DB
  • split stock into 10 buckets
  • if one key is too hot
  • rate limit per user

Trade-offs

The trade-offs. Atomic updates are simplest but only cover single-row decisions. Locks are easy to reason about but cause waits and deadlocks. Optimistic versions never wait but storm under heavy conflict. Holds give a good checkout experience but need expiry. Queues remove races but make the flow asynchronous. And when the contended data spans two services, no lock covers both; that is the multi-step processes pattern.

Trade-offs
  • atomic update: simplest, but
  • only for one-row decisions
  • locks: easy to reason about,
  • but waits and deadlocks
  • optimistic: no waiting, but
  • retry storms when hot
  • holds: good UX, need expiry
  • queues: no races, but async
  • across services: no shared
  • lock → saga (pattern 5)

Where it shows up

Where you will need it: movie booking is the seat-hold problem. The flash sale is extreme contention with a waiting room. E-commerce decrements stock at checkout. Payments and digital wallets must never let a balance go below zero. The stock exchange runs a single writer per order book. Even the URL shortener needs a unique constraint so two people cannot claim the same custom alias.

Problems that use it
  • Movie booking: seat holds
  • Flash sale: waiting room +
  • atomic gate
  • E-commerce: stock decrement
  • Payment, digital wallet:
  • balances, idempotency
  • Stock exchange: single-
  • writer order book
  • URL shortener: custom alias
  • uniqueness

Say it like this

The phrasing. A read then write would sell the seat twice, so I book with one conditional update where the status is still free, backed by a unique constraint. Checkout places a ten-minute hold so payment does not hold a lock. For the hottest items, requests go through a per-item queue with a single writer. Naming the race first shows you saw the bug before designing the fix.

In the interview
  • "Read-then-write would sell
  • the seat twice. I book with
  • one conditional UPDATE
  • WHERE status is free, plus a
  • unique constraint. Checkout
  • places a 10 min hold. For
  • the hottest items, requests
  • go through a per-item
  • queue with one writer."

Interview questions

How do you prevent two users from booking the same seat?

Make the check and the write one atomic step, for example UPDATE seats SET status = booked WHERE id = ? AND status = free, and treat zero rows affected as a lost race. A unique constraint on (show, seat) in the bookings table is the final guard.

What is the difference between pessimistic and optimistic locking?

Pessimistic locking takes a lock such as SELECT FOR UPDATE before changing data, so others wait. Optimistic locking takes no lock, writes only if a version number is unchanged, and retries on conflict, which suits workloads where conflicts are rare.

Why not hold a database lock while the user enters payment details?

Payment can take minutes and the user may leave, so a held lock would block everyone and exhaust connections. Instead, record a hold with an expiry time and confirm it only if it is still yours.

What is a fencing token?

A monotonically increasing number issued with each lock grant. The protected storage rejects requests carrying a token lower than one it has already seen, so a client whose lease expired during a pause cannot overwrite newer work.

How would you handle a flash sale with a million users and a thousand items?

Admit users gradually through a waiting room, gate purchases with an atomic decrement in Redis so only the first thousand succeed, give winners a short hold, and confirm the order in the database with idempotent writes and per-user rate limits.

How do you avoid deadlocks when locking several rows?

Always acquire locks in a consistent order, such as by primary key, keep transactions short, and set lock timeouts so the database aborts one transaction rather than waiting forever.

When would you serialise writes through a queue instead of using locks?

When one key is so hot that lock waits or optimistic retries dominate, or when strict ordering matters, as in an order book. Partitioning by key gives each key a single consumer that applies changes in order without locks.