Skip to content
notes.vinhnq.com

Search

Database & ORMSenior22 min read

Airline booking: holding seats correctly when many people book at once

Review 15 cards
On this page

Airline booking database series: 1. Flights, seats and overbooking · 2. Holding seats under concurrency · 3. Bookings, ledger and history

Part 1 chose between two seat inventory models: one row per seat per flight (seat_availability, status AVAILABLE/HELD/BOOKED/BLOCKED, with a booking_id and a held_until) or one counter per flight per class (flight_inventory with capacity, booked, and a sell_limit generated from the overbooking rate). This part answers the question every system design interview about reservations gets to: when a few hundred people book the same flight at once, how do you avoid selling a seat twice, avoid deadlocks, avoid turning people away for no reason, and still stay fast? Read part 1 (the schema) and transactions and locking (isolation levels, row locks, MVCC) first.

At a glance:

flowchart TD
  X[Check-then-act] -->|oversells| BAD[Wrong]
  Q{Seat model?} -->|counter| U["Conditional UPDATE + row count"]
  Q -->|auto-assign seat| S["FOR UPDATE SKIP LOCKED"]
  Q -->|customer picks seat| O["ORDER BY seat_code FOR UPDATE"]
  U -.-> H["Hot row: cap ≈ 1/lock time"]
  S -.->|trap| RC["Sold out? Recount without locks"]
  EX[Hold expiry] --> J["Cleanup job + lazy condition"]
  LY["Layers: app, schema, reconciliation"]

1. Why “read, then write” oversells

Key idea: two requests read the same value, both pass the if, both write. You oversell, and @Transactional doesn’t save you.

// WRONG under concurrency
Inventory inv = repo.find(flightId, "ECONOMY");          // booked = 175, sell_limit = 176
if (inv.booked() + qty <= inv.sellLimit()) {             // two requests both see 175 + 1 <= 176
    repo.setBooked(flightId, "ECONOMY", inv.booked() + qty);   // both write 176
}

It’s worse than overselling: it’s a lost update (the later write silently overwrites the earlier one). Both write 176, so the numbers say 176 seats sold while 177 tickets exist.

There are three correct approaches; the rest of the post is about when to use which:

  1. Fold the check into the write (a conditional update), then count the affected rows.
  2. Lock before reading (SELECT ... FOR UPDATE), optionally with SKIP LOCKED when “any row will do”.
  3. Detect the conflict at write time (optimistic locking with a version column), which suits low contention.

2. Counters: conditional updates and the hot row

Key idea: for a counter, one conditional UPDATE is enough: 0 rows means sold out, 1 row means held. The check and the write live in one statement, so there’s no gap between them.

UPDATE flight_inventory
SET    booked = booked + :q
WHERE  flight_id = :flightId AND fare_class = :fareClass
  AND  booked + :q <= sell_limit
RETURNING booked;
-- 0 rows: nothing left to sell. 1 row: q seats are held.

Why it’s correct under concurrency:

  • PostgreSQL (READ COMMITTED): when the UPDATE hits a row someone else just modified, it waits for them to finish, then re-evaluates the WHERE against the new row version (the EvalPlanQual mechanism).
  • MySQL InnoDB: an UPDATE is always a “current read” (it reads the latest committed version and locks it), even at REPEATABLE READ.

Both mechanisms are covered in transactions and locking. The cart and checkout post uses the same pattern to decrement stock from Spring Data JPA (UPDATE ... WHERE stock >= :qty), along with the clearAutomatically trap of @Modifying.

2.1. The hot row: how much can a counter take

Key idea: every booking for the same flight and class queues on one row lock until commit, so throughput ≈ 1 / lock hold time.

  • Just one UPDATE and an immediate commit (a few hundred microseconds to 1–2 ms): hundreds to thousands of bookings a second.
  • Calling pricing, another service or sending an email inside the transaction, holding the lock for 200 ms: 5 bookings a second for the whole flight.
Test results for the counter with 32 clients

Every SQL statement in this post was run on PostgreSQL 16 against 6,480 flights and 1.17 million seat rows, with concurrent scenarios driven by pgbench and parallel psql sessions; the MySQL parts were run on MySQL 8.4.

On a laptop, 32 clients fired 640 booking attempts (1–3 seats each) at one counter with capacity = 168 and overbook_rate = 0.05. It finished with booked = 176 = sell_limit, the seats from successful attempts summed to exactly 176, and nothing went over. The tps number from such a tiny test means nothing for production; what it proves is correctness.

3. Auto-assigning seats: FOR UPDATE SKIP LOCKED

Key idea: when the customer just wants “any 2 seats”, SKIP LOCKED lets each transaction skip locked seats and take the next ones, instead of everyone queuing for the first two.

WITH picked AS (
  SELECT seat_code
  FROM   seat_availability
  WHERE  flight_id = :flightId AND fare_class = :fareClass AND status = 'AVAILABLE'
  ORDER  BY seat_code
  LIMIT  :n
  FOR UPDATE SKIP LOCKED
)
UPDATE seat_availability s
SET    status = 'HELD', booking_id = :bookingId, held_until = now() + interval '15 minutes'
FROM   picked
WHERE  s.flight_id = :flightId AND s.seat_code = picked.seat_code
RETURNING s.seat_code;
-- Fewer than n rows returned: ROLLBACK, never hold half a group.

The application has to count the result: fewer than n rows means roll back the whole transaction, because a party of two doesn’t want only one of them seated. In a test with 32 clients making 160 attempts of 2 seats each against a 12-seat business cabin: exactly 6 succeeded, 154 were cleanly rejected, and no seat was held twice.

EXPLAIN: why the partial index still shows a Filter

The query plan for seat picking:

Limit
  -> LockRows
       -> Index Scan using seat_avail_free_idx on seat_availability
            Index Cond: ((flight_id = 100) AND (fare_class = 'ECONOMY'))
            Filter: (status = 'AVAILABLE')

4. Customers picking seats: lock in a fixed order

Key idea: locking seats one by one in click order deadlocks when two customers pick the same pair in opposite orders. Lock all the seats in one statement, in a fixed order, then do the conditional UPDATE.

A deadlock: two transactions each hold one lock and wait for the other’s, so neither can move.

T1: UPDATE ... seat_code = '10A'   -- locks 10A
T2: UPDATE ... seat_code = '10B'   -- locks 10B
T1: UPDATE ... seat_code = '10B'   -- waits for T2
T2: UPDATE ... seat_code = '10A'   -- waits for T1 → ERROR: deadlock detected

Run for real on PostgreSQL 16: after about a second (the default deadlock_timeout), one transaction is aborted and the other wins. No data is lost, but the user sees a 500 unless there’s a retry. The fix:

BEGIN;
SELECT seat_code, status
FROM   seat_availability
WHERE  flight_id = :flightId AND seat_code = ANY(:seats)
ORDER  BY seat_code
FOR UPDATE;                                   -- every transaction locks in the same order

UPDATE seat_availability
SET    status = 'HELD', booking_id = :bookingId, held_until = now() + interval '15 minutes'
WHERE  flight_id = :flightId AND seat_code = ANY(:seats) AND status = 'AVAILABLE';
-- updated rows must equal the number of seats requested; otherwise ROLLBACK and say "a seat was just taken"
COMMIT;

Tested with two customers picking {10C,10D} and {10D,10C} at once: no more deadlock. The second transaction waits, FOR UPDATE returns the latest status HELD, the UPDATE touches 0 rows, and the application rolls back and asks the customer to pick again.

5. Hold expiry

Key idea: a HELD seat has a held_until. Release it with a periodic job, or treat expired holds as free right in the read; in practice, do both.

-- Option 1: a periodic job releases seats (every minute)
UPDATE seat_availability
SET    status = 'AVAILABLE', booking_id = NULL, held_until = NULL
WHERE  status = 'HELD' AND held_until < now();

-- Option 2: treat expired holds as free right in the read
SELECT seat_code FROM seat_availability
WHERE  flight_id = :flightId
  AND (status = 'AVAILABLE' OR (status = 'HELD' AND held_until < now()));

6. Calling it from Spring Boot 3

Key idea: the repository runs the two statements from section 4 (lock in order, then the conditional UPDATE) and compares row counts; the service throws so @Transactional rolls back and the controller returns 409 Conflict.

  • Sort and de-duplicate the seat list in Java before locking.
  • Passing an array to ANY(:s) relies on the PostgreSQL driver binding String[] as an array parameter; on MySQL use IN (:s) with a List.
  • The Java only illustrates the calls and wasn’t run (the SQL inside it was run directly on PostgreSQL).
Full code: SeatHoldRepository and SeatHoldService
@Repository
@RequiredArgsConstructor
class SeatHoldRepository {
    private final JdbcClient jdbc;   // Spring Framework 6.1+

    /** Holds exactly the seats the customer chose; false if any seat was taken by someone else. */
    boolean holdChosenSeats(long flightId, List<String> seats, long bookingId) {
        String[] sorted = seats.stream().sorted().distinct().toArray(String[]::new);
        jdbc.sql("""
                SELECT seat_code FROM seat_availability
                WHERE flight_id = :f AND seat_code = ANY(:s)
                ORDER BY seat_code FOR UPDATE""")
            .param("f", flightId).param("s", sorted)
            .query(String.class).list();
        int held = jdbc.sql("""
                UPDATE seat_availability
                SET status = 'HELD', booking_id = :b, held_until = now() + interval '15 minutes'
                WHERE flight_id = :f AND seat_code = ANY(:s) AND status = 'AVAILABLE'""")
            .param("b", bookingId).param("f", flightId).param("s", sorted)
            .update();
        return held == sorted.length;
    }
}

@Service
@RequiredArgsConstructor
class SeatHoldService {
    private final SeatHoldRepository seats;

    @Transactional
    public void hold(long flightId, List<String> chosen, long bookingId) {
        if (!seats.holdChosenSeats(flightId, chosen, bookingId)) {
            // throw so @Transactional rolls back; the controller maps it to 409 Conflict
            throw new SeatTakenException(flightId, chosen);
        }
    }
}

These SQL calls belong behind a repository in the booking context, while the rule “hold for at most 10 minutes, release the seats on expiry” belongs to the booking aggregate. How this very problem splits into bounded contexts (Booking, Flight, Payment) and a package tree is in DDD and package structure.

7. Differences on MySQL 8

Key idea: MySQL 8.4 has SKIP LOCKED and its conditional updates are just as safe, but it has no partial indexes and won’t pick and update the same table in one statement, so you split it into two.

Comparison table: PostgreSQL 16 vs MySQL 8.4
Point PostgreSQL 16 MySQL 8.4
SKIP LOCKED, NOWAIT Yes Yes (since 8.0.1), tested
Partial index WHERE status = 'AVAILABLE' Yes No; use a composite index (flight_id, status, fare_class, seat_code)
Pick and update in one statement (CTE with FOR UPDATE SKIP LOCKED + UPDATE) Yes A subquery can’t read the table being updated (error 1093); the simple, tested way: SELECT ... FOR UPDATE SKIP LOCKED then UPDATE ... WHERE seat_code IN (...) in the same transaction
CHECK constraints Yes Enforced since 8.0.16 (silently ignored before); tested, booked <= sell_limit is rejected
STORED generated columns Yes Yes
Conditional UPDATE under concurrency Re-evaluates WHERE on the new version Current read, also safe; watch out for gap locks (locks on the gaps between index values) when the WHERE is a range
Indexes on foreign keys Not created automatically InnoDB creates one if missing

8. Which technique, and the layers of defence

Key idea: default to a conditional update; use a pessimistic lock when the logic is too complex for a WHERE; use SKIP LOCKED when “any row will do”. And always have three layers of defence: application, schema, reconciliation.

If you hold in Redis, Redis still can’t be the source of truth: replication is asynchronous, so a DECR that already returned “OK” can vanish on failover, and depending on RDB/AOF settings a restart loses a few more seconds of data. The database has the final say and Redis only absorbs load; what Redis loses in each case is in Redis: persistence, replication, Sentinel and Cluster.

No single layer is enough, because there’s always a batch job, a hand-written fix-up script or a new code path that bypasses the service:

Layer Mechanism Catches
Application Conditional update or ordered locking, row-count check Almost every concurrent case
Schema Primary key (flight_id, seat_code), CHECK tying status to booking_id, CHECK (booked <= sell_limit), a partial unique index on active tickets (part 3) Bugs in code, manual scripts
Operations Periodic reconciliation queries + alerts Drift between tables that constraints can’t express

When what you hold is a time range (a room by the night, a meeting room by the slot), UNIQUE can’t express “no overlap”. PostgreSQL enforces it at the schema layer with a GiST exclusion constraint (EXCLUDE USING gist (room_id WITH =, during WITH &&)); see Index types beyond the B-tree.

An example reconciliation query

Find seats marked BOOKED with no active ticket (the same idea works for a counter that disagrees with the tickets issued):

SELECT sa.flight_id, sa.seat_code
FROM   seat_availability sa
LEFT JOIN ticket t ON t.flight_id = sa.flight_id AND t.seat_code = sa.seat_code
                  AND t.status IN ('ISSUED', 'USED')
WHERE  sa.status = 'BOOKED' AND t.ticket_id IS NULL
  AND  sa.flight_id IN (SELECT flight_id FROM flight WHERE departure_time > now());

These options are measured side by side (naive, FOR UPDATE, conditional update, version, a Redis lock with and without a fencing token) on a 100-seat flight with 400 concurrent requests in Optimistic or pessimistic locking.

Exercises

Exercise 1: Choosing seats safely

Task: write a “hold the seats the customer chose” service that never deadlocks, never holds half a selection, and reports the right error when a seat was just taken by someone else.

Solution to Exercise 1

See sections 4 and 6: sort and de-duplicate the seat list; SELECT ... ORDER BY seat_code FOR UPDATE; conditional UPDATE on status = 'AVAILABLE'; compare row counts; on mismatch, throw so the transaction rolls back and return 409 Conflict. A senior would add: one retry for deadlock or serialization errors; a lock_timeout of a few seconds; a metric of 409s per flight to spot hot flights.

Exercise 2: A release job running on several instances

Task: the service runs 4 pods, each with an @Scheduled job that releases expired holds every minute. Sometimes tens of thousands of seats expire at once (an abandoned promotion). Write the SQL so pods don’t block each other, each run holds locks briefly, and no seat is released twice.

Solution to Exercise 2

Work in batches, picking each batch with SKIP LOCKED so the pods share the work:

WITH batch AS (
  SELECT flight_id, seat_code
  FROM   seat_availability
  WHERE  status = 'HELD' AND held_until < now()
  ORDER  BY held_until
  LIMIT  1000
  FOR UPDATE SKIP LOCKED
)
UPDATE seat_availability s
SET    status = 'AVAILABLE', booking_id = NULL, held_until = NULL
FROM   batch
WHERE  s.flight_id = batch.flight_id AND s.seat_code = batch.seat_code
RETURNING s.flight_id, s.seat_code;

Loop until it returns 0 rows, one transaction per batch. Two pods running at once take different batches; the status = 'HELD' condition is re-checked after locking, so no seat is released twice. The partial index (held_until) WHERE status = 'HELD' keeps batch selection cheap.

A senior would add: with a counter instead of per-seat rows, releasing is UPDATE flight_inventory SET booked = booked - :q, and it must be tied to moving the booking to EXPIRED in the same transaction (a conditional update on the booking status) so it’s never subtracted twice; and never release a booking that has a payment in progress (the payment gateway post).

Exercise 3: “Sold out” or “try again”

Task: auto-assignment with SKIP LOCKED comes back short. Write the query that tells a real sell-out from a temporary hold.

Solution to Exercise 3
SELECT count(*) FILTER (WHERE status = 'AVAILABLE')                       AS free_now,
       count(*) FILTER (WHERE status = 'HELD' AND held_until > now())       AS held_temporarily
FROM   seat_availability
WHERE  flight_id = :flightId AND fare_class = :fareClass;

This query takes no locks (it reads a snapshot), so it never blocks.

  • free_now >= n: the seats are locked by transactions that haven’t finished; retry after a few hundred milliseconds with jitter.
  • free_now + held_temporarily >= n: seats may free up if someone abandons their hold; say “someone is holding these seats”.
  • Only when both are below n do you say sold out.

Remember

  • Explain why check-then-act oversells even inside @Transactional, and the three correct alternatives.
  • Write the conditional update for a counter and say why it’s correct on PostgreSQL and on MySQL.
  • Work out a hot row’s throughput ceiling from the lock hold time, and know what to move out of the transaction.
  • Know when to use SKIP LOCKED, when to lock in a fixed order, and how to avoid a false sell-out.
  • Name the three layers of defence against double-selling and how to release expired holds safely.

Interview questions

Junior

What is check-then-act and why is it wrong under concurrency?

Reading state, checking a condition in code, then writing based on the value you read; between the read and the write another request may have changed the data.

Two requests both pass the check and both write: you oversell and lose an update. Fix it by moving the condition into the write and checking the row count, or by locking the row before reading.

Mid

Write the SQL that holds inventory on a counter correctly under concurrency

UPDATE flight_inventory SET booked = booked + :q WHERE flight_id = :f AND fare_class = :c AND booked + :q <= sell_limit, then check the row count: 1 means held, 0 means sold out.

Don’t SELECT first and UPDATE afterwards (check-then-act). It’s correct because PostgreSQL re-evaluates the WHERE on the new version after waiting for the lock, and a MySQL UPDATE is a current read.

When do you use SKIP LOCKED, and what’s the trap?

When “any row will do”: auto-assigning seats, job queues with many workers. The trap: it can report sold out when seats will be free a moment later.

Each transaction skips locked rows instead of waiting, so they don’t queue on the first few rows. But it also skips rows locked by transactions that are about to roll back; double-check with a non-locking count. Don’t use it for “exactly seat 12A”.

Two customers pick the same two seats. How do you avoid a deadlock?

Lock all the seats you need in one statement in a fixed order (ORDER BY seat_code FOR UPDATE), then do a conditional UPDATE on status = 'AVAILABLE' and compare row counts.

Locking one by one in click order locks crosswise. Keep a retry for deadlock errors, because other code paths may lock in a different order.

Senior

A few hundred people book the same Tết holiday flight at once. How does your design cope?

The load concentrates on one flight, so the problem is contention, not total throughput: keep the hold transaction tiny and set a lock_timeout.

  • The transaction holds only the conditional UPDATE and INSERTs; pricing and external calls happen before it, side effects go via an outbox.
  • Per-seat rows: contention spreads over seats; auto-assignment uses SKIP LOCKED.
  • Counter: the ceiling is about one over the lock hold time; if measurements show that’s not enough, hold in Redis with a TTL first and write to the DB afterwards, or put a virtual waiting room in front of the booking page.
  • Measure with a load test aimed at one flight, not spread evenly.
Which layers do you use to guarantee a seat is never sold to two people?

Three layers: application (conditional UPDATE, row-count check), schema (constraints), operations (reconciliation and alerts). Never trust a single one.

  • Schema: the primary key (flight_id, seat_code) on seat inventory, a CHECK tying status to booking_id, and a partial unique index on (flight_id, seat_code) for active tickets.
  • Operations: periodic reconciliation queries looking for seats with two tickets or tickets that disagree with seat status.
  • Why: some batch job or manual script will bypass the code.
When is a periodic job good enough for hold expiry, and when do you need second-level precision?

A job every minute is fine when a seat being “held” for up to an extra minute doesn’t matter; when a flight is selling out or holds are short, add a lazy condition to the hold statement.

The job is simple and keeps indexes tidy. The lazy “HELD but held_until < now() counts as free” condition stops anyone being turned away wrongly. Either way, before releasing a booking that’s waiting for payment, check that no payment is in flight, or a customer will pay for a seat that’s already been sold to someone else.

Why is a conditional UPDATE correct at READ COMMITTED without taking a lock first?

Because the UPDATE locks the row it modifies, and the check and the write happen in one statement against the latest version, so there’s no gap between reading and writing.

  • PostgreSQL: if another transaction is modifying the row, the UPDATE waits, then re-evaluates the WHERE against the new version (EvalPlanQual); if the condition no longer holds, the row is skipped and the count is 0.
  • MySQL InnoDB: an UPDATE always reads the latest committed version (a current read) and locks it.
  • At PostgreSQL’s SERIALIZABLE or REPEATABLE READ, instead of re-evaluating, the transaction gets a serialization error and must retry.

Useful phrases for interviews

  • “Check-then-act in application code oversells, even inside a transaction, because a plain SELECT doesn’t lock anything.”
  • “I fold the check into the write with a conditional update and look at the affected row count.”
  • “With a single counter per flight, the throughput ceiling is roughly one over the lock hold time, so the transaction has to be tiny.”
  • “For auto-assignment I use SKIP LOCKED, and if it comes back short I run a non-locking count before telling the user it’s sold out.”
  • “When a customer picks several seats, I lock them in one statement in sorted order to avoid deadlocks.”
  • “I still keep a retry for deadlock and serialization errors, because some other code path might lock in a different order.”
  • “Expired holds are released by a batched job using SKIP LOCKED, so several pods can share the work.”
  • “I don’t rely on one layer: the application, the schema constraints and a reconciliation query each catch different failures.”

Notes based on the Advanced Backend course (Ronin Engineer), rewritten in my own words.