Design a Movie Ticket Booking System

The entire design of a ticket booking system turns on one mechanism: the moment a customer commits to seats, the database must atomically check that those exact seats are still free and mark them taken, so that two concurrent requests for seat 55 cannot both observe it as free. Everything else — the class model, the search index, the notifications — is scaffolding around that single critical section.

This page keeps the standard Grokking class model but treats the seat-reservation race as the real subject: we trace the double-booking interleaving step by step, give a JDBC transaction that actually compiles and is actually correct, and then weigh the locking approach against the two alternatives a senior engineer would consider.

The domain model, in one pass

The objects only matter insofar as they let us name the row we are racing on. A Movie has many Shows; each Show runs in one CinemaHall at a startTime. A hall has fixed physical seats (CinemaHallSeat) — seat A12 exists whether or not anyone is watching. The seat that actually gets booked is the ShowSeat: the cross-product of one physical seat with one show. Seat A12 for the 7pm show and seat A12 for the 9:30pm show are two different ShowSeat rows with independent isReserved flags. A Booking ties a customer to a set of ShowSeats plus a Payment.

The key modelling decision: availability lives on ShowSeat, not on the physical seat. If you put isReserved on CinemaHallSeat you would reserve a seat across every show at once. The race we care about is therefore always over a small set of ShowSeat rows identified by (showId, showSeatId).

Sketched as fields (getters/setters elided; treat fields as private):

class Show       { int showId; Date startTime, endTime; CinemaHall hall; Movie movie; }
class CinemaHallSeat { int seatId; SeatType type; }          // physical, e.g. "A12"
class ShowSeat extends CinemaHallSeat {
    int    showSeatId;       // PK of the bookable row
    int    showId;           // which show
    boolean isReserved;      // THE contended field
    double price;
}
class Booking {
    String bookingNumber;
    BookingStatus status;    // PENDING -> CONFIRMED | CANCELED | ABANDONED
    Show show;
    List<ShowSeat> seats;
    Payment payment;
}

A booking moves PENDING → CONFIRMED only after both seats are locked and payment clears; an unpaid PENDING booking that times out becomes ABANDONED and its seats are released. That state machine is what makes the "hold the seat for 10 minutes while I pay" feature possible without leaking inventory forever.

The race, traced with real values

Two customers both want seat 55 of show 99 (a popular opening night). Both client requests arrive within the same millisecond and run the naive logic — read the seat, see it is free, then write it reserved — with no transaction or with a too-weak isolation level. The interleaving:

The defect is the gap between the read at t1/t2 and the write at t3/t4. Both transactions read isReserved = 0 before either writes, so both believe they won. This is a classic read–modify–write race (a "lost update"). Wrapping the two statements in a transaction at the default isolation level (READ COMMITTED in PostgreSQL/SQL Server, REPEATABLE READ in MySQL) does not save you on its own: a plain SELECT takes only a shared read lock (or, under MVCC, no lock at all — it just reads a snapshot), which does not block another transaction's later read. You need to make the read conflict with the other transaction's write.

Two correct fixes — and the claim to retire

The original page asserted: "within a transaction, if we read rows we get a write-lock on them." That is wrong and worth retiring deliberately, because it leads people to expect protection they do not have. A bare SELECT never takes a write/exclusive lock. Under SERIALIZABLE it takes shared and range (predicate) locks — enough to make the engine detect the conflict and abort one transaction, but the rows are not write-locked the instant you read them. There are two honest ways to close the race:

1. Pessimistic locking — SELECT ... FOR UPDATE. This explicitly takes an exclusive row lock at read time. T_bob's SELECT ... FOR UPDATE blocks at t2 until T_alice commits, then sees isReserved = 1 and bails. This is the most direct expression of intent and works at the default isolation level.
2. Serializable isolation. Set the level to SERIALIZABLE; the engine tracks range/predicate locks and, when it detects that the two transactions' read- and write-sets conflict, it aborts one with a serialization-failure error that the application must catch and retry.

Either works. The example below uses SELECT ... FOR UPDATE because it is the clearer mental model and does not require retry plumbing.

The SQL, with real ids (reserve seats 54, 55, 56 of show 99):

BEGIN;

-- Take an EXCLUSIVE lock on exactly the rows we want, but only if still free.
SELECT show_seat_id
FROM   show_seat
WHERE  show_id = 99
  AND  show_seat_id IN (54, 55, 56)
  AND  is_reserved = 0
FOR UPDATE;          -- <- this is what blocks the other booker

-- The application checks: did we get back exactly 3 rows?
-- If not, at least one seat is already taken -> ROLLBACK and tell the user.

UPDATE show_seat
SET    is_reserved = 1
WHERE  show_id = 99
  AND  show_seat_id IN (54, 55, 56)
  AND  is_reserved = 0;   -- guard repeated, so the UPDATE itself is safe

-- Application checks the affected-row count == 3 as a second belt.
INSERT INTO booking (...) VALUES (...);

COMMIT;

Why FOR UPDATE is load-bearing: without it the SELECT is a snapshot read and the two transactions never conflict on the read, so both reach the UPDATE and one silently overwrites the other (the lost update above). FOR UPDATE makes the second reader wait, turning a race into an ordered queue.

The same logic in JDBC — corrected and compilable

The original Java had several bugs: it created an unused Statement st; used SQL && (not valid SQL) instead of AND; passed an array to a single IN (?) placeholder (standard JDBC does not expand one parameter into an IN list); counted rows by scrolling a ResultSet with rs.next() then rs.last() (which skips the logic on the empty case and needs a scrollable result set); and called rollback() inside catch on a connection that might be null, risking a NullPointerException that masks the real error. Here is a version that compiles and is correct:

public boolean makeBooking(Booking booking) throws SQLException {
    List<ShowSeat> seats = booking.getSeats();
    int showId = booking.getShow().getShowId();

    // Build "?, ?, ?" with one placeholder per seat id.
    String placeholders = seats.stream()
        .map(s -> "?")
        .collect(java.util.stream.Collectors.joining(", "));

    String lockSql =
        "SELECT show_seat_id FROM show_seat " +
        "WHERE show_id = ? AND is_reserved = 0 " +
        "AND show_seat_id IN (" + placeholders + ") FOR UPDATE";

    String updateSql =
        "UPDATE show_seat SET is_reserved = 1 " +
        "WHERE show_id = ? AND is_reserved = 0 " +
        "AND show_seat_id IN (" + placeholders + ")";

    Connection conn = null;
    try {
        conn = getDBConnection();
        conn.setAutoCommit(false);   // start the transaction

        // 1. Lock the rows we still believe are free.
        try (PreparedStatement lock = conn.prepareStatement(lockSql)) {
            lock.setInt(1, showId);
            for (int i = 0; i < seats.size(); i++) {
                lock.setInt(i + 2, seats.get(i).getShowSeatId());
            }
            int locked = 0;
            try (ResultSet rs = lock.executeQuery()) {
                while (rs.next()) locked++;
            }
            // Fewer rows than requested => someone already took one. Abort.
            if (locked != seats.size()) {
                conn.rollback();
                return false;
            }
        }

        // 2. Flip them reserved. The WHERE guard makes this idempotent-safe.
        try (PreparedStatement upd = conn.prepareStatement(updateSql)) {
            upd.setInt(1, showId);
            for (int i = 0; i < seats.size(); i++) {
                upd.setInt(i + 2, seats.get(i).getShowSeatId());
            }
            int changed = upd.executeUpdate();
            if (changed != seats.size()) {   // belt-and-suspenders
                conn.rollback();
                return false;
            }
        }

        // 3. INSERT the Booking row here, then commit.
        conn.commit();
        return true;

    } catch (SQLException e) {
        if (conn != null) {
            try { conn.rollback(); } catch (SQLException ignore) { /* log */ }
        }
        throw e;   // don't swallow it as "return false"
    } finally {
        if (conn != null) {
            try { conn.setAutoCommit(true); conn.close(); } catch (SQLException ignore) {}
        }
    }
}

Why the naive version is wrong, in one line: a plain SELECT that reads isReserved = 0 does not stop a concurrent transaction from reading the same 0, so the read-then-write window lets both commit; only an exclusive lock (FOR UPDATE) or a conflict-detecting isolation level (SERIALIZABLE, with retry) makes one of them lose.

Pitfalls a working engineer hits

• Locking seats then waiting on a human (or a payment gateway). If you hold FOR UPDATE locks while the user spends 3 minutes typing card details, you serialize the whole hall and block everyone. The fix is the two-phase hold: do a short transaction that marks seats PENDING with a held_until timestamp (releasing the row lock immediately), let the user pay, then a second short transaction confirms. A background job reaps expired holds.
• Deadlock from inconsistent lock ordering. Alice locks seats (54, 55), Bob locks (55, 54); each holds one and waits for the other. Always sort seat ids before locking so every transaction acquires them in the same order — turns a deadlock into a clean wait.
• Swallowing the serialization-failure exception. If you choose SERIALIZABLE instead of FOR UPDATE, the engine aborts a loser with a retryable error (PostgreSQL 40001). Application code must catch it and retry the whole transaction; treating it as a generic failure shows users "booking failed" when a retry would have succeeded.
• Trusting an app-level cache of seat availability. A Redis "seats free" map can lie under concurrency. It is fine as a UI hint, but the authoritative check is the conditional UPDATE ... WHERE is_reserved = 0 and its affected-row count. Never book on the cache alone.
• Forgetting the row count is the real verdict. A successful UPDATE that changes fewer rows than requested is a partial failure, not a success — you booked some seats but not all. Always compare affectedRows == seats.size() and roll back otherwise.

When to use DB locking vs. the alternatives

The decision is about how often two people fight over the same seat and how long you must hold a seat before confirming.

• Pessimistic (SELECT ... FOR UPDATE) — choose when contention is high and the critical section is short. Opening night, front-row seats: many requests for the same rows. You gain a dead-simple correctness story (the DB serializes you). It costs throughput — bookers for the same show queue behind each other — and risks deadlock if you don't order locks. Choose THIS when the transaction is milliseconds long and stays inside the database.
• Optimistic (version column / conditional UPDATE ... WHERE is_reserved = 0, retry on miss) — choose when contention is low. A quiet Tuesday matinee: collisions are rare. You gain higher concurrency (no blocking; readers never wait), at the cost of retry logic and wasted work when a rare collision does abort a transaction. The conditional-update-with-row-count pattern is itself a lightweight optimistic check. Prefer this when most bookings touch distinct seats.
• Distributed lock / reservation service (Redis SETNX with TTL, or a dedicated seat-hold service) — choose when the hold must outlive a single DB transaction (the user needs minutes to pay) or when seat state is sharded across services. You gain a natural TTL-based hold and decoupling from the booking DB; you pay with a second source of truth that can drift, plus the operational weight of another stateful system. Prefer this when "hold for 10 minutes while I pay" is a product requirement and the booking volume is large enough that DB row locks would hurt.

Concretely: for a single-region cinema chain doing a few thousand bookings a day, the corrected JDBC above (pessimistic FOR UPDATE inside a short transaction, with a separate PENDING/held_until hold for the payment window) is the right default — correct, no extra infrastructure, and the contention is bounded by the size of one hall. Reach for Redis holds only when you outgrow that.

Takeaways

• The whole problem reduces to one critical section: atomically verify seats are free and mark them taken. Model availability on ShowSeat, not the physical seat, so the contended row is small and well-identified.
• A plain SELECT does not take a write lock — that claim is false. Use SELECT ... FOR UPDATE for an explicit exclusive lock, or SERIALIZABLE with a retry loop; verify by checking the affected-row count equals the seats requested.
• Never hold row locks across a human or a payment gateway. Split into a short hold transaction (PENDING + held_until) and a short confirm transaction, with a reaper for expired holds.
• Pick the concurrency strategy by contention and hold duration: pessimistic for hot short transactions, optimistic for low-collision workloads, a Redis/seat-service hold when the reservation must outlive a DB transaction.

Re-authored and deepened for this guide. Class model adapted from Grokking the Object Oriented Design Interview (Design Online Movie Ticket Booking System). Concurrency mechanics from the PostgreSQL documentation on Explicit Locking and Transaction Isolation (SELECT ... FOR UPDATE, serialization failures), the MySQL InnoDB Locking and Transaction Model reference, and Martin Kleppmann, "Designing Data-Intensive Applications," ch. 7 (lost-update and write-skew anomalies). The original page's claim that a read takes a write lock has been corrected.