SevyDevy

Module 5 · When Copies Disagree · Lesson 20 of 35

How to prevent double booking: two people, one seat

13 minFree System design course · Beginner to intermediateLesson 5.1By Saket Bhatnagar

Researched and drafted with AI, reviewed and edited by Saket Bhatnagar.

In short

  • How to prevent double booking? Make the check and the write one step: change the seat only if it is still free, then read how many rows changed.
  • Two requests can both read “free” before either writes “sold”. A faster server narrows that gap but never closes it.
  • A short seat hold covers the minutes of paying; a one-booking-per-seat rule catches anything that slips through.
Soon

Video lesson · coming soon

How to prevent double booking: two people, one seat

Lala, your AI mentor, will walk you through this lesson in a short animated video. Until then, everything is here to read and try.

  1. 01 One seat, two phones
  2. 02 Two hands, one last vada pav
  3. 03 Both read free, both write sold
  4. 04 UPDATE 1 and UPDATE 0
  5. 05 A ten-minute hold
  6. 06 Sketch your booking path

Saturday evening, gate 3 of a Pune cricket ground. Two people hold phones that both say seat A12. One is Ira, Meera Kulkarni’s niece. The other is a stranger, just as sure.

By Sunday the count is 37 seats sold twice, all in one ₹299 league-match sale. Meera refunds 37 × ₹299 = ₹11,063 and sends goodwill coupons. “What do I tell users?” she asks.

Arjun Nair built the new Book now button for this sale. It skips the seat hold to save a tap. “But the code checks that A12 is free on the line above,” he says. “Maybe we need a faster server.”

Zoya Mirza lines up the two requests by time. With data in many places now, they arrived 3 ms apart. The check was there. The code took 3 ms. So how can two people own A12?

How to prevent double booking in a ticket app?

You prevent double booking by making the check and the write one step, so no second request can slip in between.

  • Check, then write, leaves a gap. Two requests can both read “free” inside it.
  • Speed makes the gap smaller, never zero.
  • One command closes the gap: “sell A12 only if it is still free”.
  • Read the answer. One row changed means yours; zero means someone got there first.

Two taps, one seat

Old way: both taps read “free” before either writes “sold”, so A12 sells twice. With the fix, one command sells only if the seat is still free, and the second tap is told “taken”.

Diagram connections:

  • Two taps on A12 to Check: is A12 free? (Old way)
  • Check: is A12 free? to Write: A12 sold (Both read free)
  • Write: A12 sold to Two tickets, one seat (Both write)
  • Two taps on A12 to One step: sell if free (With the fix)
  • One step: sell if free to First tap: 1 row (First request)
  • One step: sell if free to Second tap: 0 rows (3 ms later)
  • Two taps on A12: Ira first, a stranger 3 ms later.
  • Check: is A12 free?: Both requests read the row before either writes.
  • Write: A12 sold: Both writes go through.
  • Two tickets, one seat: Two booking ids point at A12.
  • One step: sell if free: Check and write in one command on the primary.
  • First tap: 1 row: Ira’s command changed the row.
  • Second tap: 0 rows: Nothing changed: no ticket, no charge.
  • A bus app says “Sorry, this seat was just taken.” A one-step update found no free seat and refused before taking money.
  • A concert app turns your seat yellow with a 9:58 timer. A seat hold keeps it yours while you pay.
  • Two friends tap the last movie seat; both see “Confirmed”, and one later gets a refund email. Check and write were two steps.

What is a race condition in databases?

A race condition is a bug where the result depends on which request reaches the data first.

Book now asks “is A12 free?” in one command, then “mark A12 sold” in a second.

Try it yourselfReplay Ira’s tap and the stranger’sSteps 1 to 4 replay the race; steps 5 to 7 add Zoya’s fixes. Watch the console.

Which fix works: a unique rule, a conditional update, a lock or a hold?

A conditional update, as in step 5, is the usual first choice; a unique rule is the safety net under it.

The other way is to make others wait: open a transaction and claim the seat row first.

At the stall, the unique rule is a board saying “one vada pav per number”. A lock is Sudhir’s hand on the vada pav while he serves. The hold is the vada pav kept aside.

Four ways to make check and write one step
FixHow it worksCostBest for
Unique ruleRefuses a second active booking for one seatOne failed insert to handleSafety net under every fix
Conditional updateUPDATE ... WHERE status = 'free'Almost noneThe first move at a big sale
LockSELECT ... FOR UPDATE; others waitA long lock blocks everyoneShort changes to several rows
Seat holdSet only if empty, ends in 10 minutesCan run out mid-paymentThe minutes of paying
Try it yourselfOne-step, lock and holdGuess what each way buys you or what it costs, then flip the card to check.

How do you write the one-step update in PostgreSQL?

Put the expected status in the WHERE clause, send it to the primary, and read the row count. Here is Zoya’s sale step for Ira.

sql
UPDATE seats
SET status = 'sold',
    booking_id = 'BK-20417'
WHERE match_id = 57
  AND seat_code = 'A12'
  AND status = 'free';
-- answer: UPDATE 1 (yours) or UPDATE 0 (taken)

What just happened? In PostgreSQL’s default isolation level, read committed, a second UPDATE on the same row waits for the first. If the first committed, it checks the WHERE again on the new row, sees sold, and changes nothing. UPDATE 1 is “you got the vada pav”; UPDATE 0 is “sorry, gone”.

Why does 3 ms matter? Say this league-match sale peaked at about 2,000 requests a second (an assumption). Then any 3 ms holds about 6 requests (2,000 × 0.003 = 6).

  • Lock: SELECT ... FOR UPDATE makes other writers of that row wait until your transaction ends. Plain reads are not blocked.
  • Stricter levels: at repeatable read or serializable, the second writer gets an error, not UPDATE 0. Catch that error (SQLSTATE 40001) and retry.
  • People call the lock way pessimistic, because it expects a clash and waits, and the conditional update optimistic, because it tries and checks the count.
  • Release a hold in one step, only if it still holds your user id; a plain delete after expiry can delete someone else’s hold.
sql
CREATE UNIQUE INDEX bookings_active_seat
ON bookings (match_id, seat_code)
WHERE status <> 'cancelled';

What just happened? This partial unique index allows one row per match and seat, counting only bookings that are not cancelled.

To test it, a few hundred times, reset A12 to free and fire two requests at once. Exactly one must get UPDATE 1 each time. A common LLD question, “design a movie booking app”, turns on this point.

Try it yourself

  1. Write the update

    The seats table has match_id, seat_code, status and booking_id. Write the one-step update that sells B07 of match 42 as booking BK-20533.

  2. Predict and decide

    What does the database answer the first time, and for a second session? What does the user see?

  3. Check your answer

    Add AND status = 'free' to the WHERE. First run: UPDATE 1, so confirm. Second: UPDATE 0, so “Seat taken, pick another”. You are right if your WHERE checks free and the app reads the count.

So Arjun’s check was real, but it sat one command above the write. Both looked at the vada pav before either took it, and a faster server would only have shrunk the gap.

Zoya sends Book now through the one-step hold, makes the sale a conditional update on the primary, and adds the unique rule. The order is: hold in the session store, payment, then lesson 14’s transaction with the conditional update. If the hold runs out mid-payment and the update finds 0 rows, the payment is refunded. Meera can tell users the seat was sold in two steps, and now in one.

On the primary the fix works. But the seat map, read from replicas, still shows A12 free for about two seconds. Is that wrong, or just late?

Check yourself

Check yourself

Two taps for A12 arrive 3 ms apart and both read “free”. What really caused the double booking?

Pick an answer to see why it is right or wrong.

Check yourself

Which way of booking a seat is safe at a big sale?

Pick an answer to see why it is right or wrong.

Check yourself

Why does SeatJaldi put a 10-minute expiry on a seat hold?

Pick an answer to see why it is right or wrong.

Remember these three

  1. Check-then-write is a race; make the check and the write one step.
  2. Sell the seat only if it is still free, in one command, and read the count: one row is yours, zero is taken.
  3. A hold covers the minutes of paying and ends by itself; a unique rule catches anything that slips through.

Questions people ask

How do you prevent double booking in a database?
Make the check and the write one step. A conditional update changes the seat only if it is still free, and you read how many rows changed. A unique rule is the safety net.
What is a race condition in databases?
A race condition is a bug where the result depends on which request reaches the data first. With check-then-write, two requests can both read “free” before either writes “sold”.
Does a transaction alone stop double booking?
Not at PostgreSQL’s default level, read committed, because two transactions can both read “free” first. You still need a conditional update, a lock or a unique rule, or a stricter level with retries.

Sources and further reading