Reservations, Holds and Double Booking

The race. Booking code often reads like this: look up the seat, see it is free, then mark it booked. Between the look and the mark, another request can do the same, so both pass the check. It is the same bug as counter += 1 from two threads, and it only shows up under load, usually on the most popular seat or the last room.

Make the check and the write one step. There are three standard ways. A unique constraint lets the database refuse the second row: a unique index on (show_id, seat_id) means two bookings for the same seat cannot both commit. A conditional UPDATE does the check inside the write: UPDATE seat SET status = 'held' WHERE id = ? AND status = 'free', then read the affected row count: 1 means you won, 0 means someone beat you. A row lock (SELECT ... FOR UPDATE) makes later requests wait until the first finishes.

Optimistic or pessimistic. A row lock is pessimistic: assume a clash and wait your turn. A version column is optimistic: read the row and its version, then UPDATE ... WHERE id = ? AND version = ? and bump the version. If another request changed the row, zero rows match and you retry or give up. Optimistic suits rare clashes; locks suit a hot item that everyone wants.

A hold is a booking with a deadline. Nobody pays in the same instant they pick a seat, so the seat is held for a few minutes (held_until). The hold is confirmed at payment, or it lapses. Treat a hold as expired by comparing held_until with the current time when you read or write, so correctness never depends on a cleanup job running on time. A background sweeper then only tidies old rows.

import sqlite3

db = sqlite3.connect(':memory:')
db.execute('CREATE TABLE seat (id INTEGER PRIMARY KEY, status TEXT, held_by TEXT, held_until INTEGER)')
db.execute("INSERT INTO seat VALUES (1, 'free', NULL, NULL)")

def hold(seat_id, user, now, ttl=300):
    # one statement: the check and the write cannot be separated
    cur = db.execute(
        "UPDATE seat SET status = 'held', held_by = ?, held_until = ? "
        "WHERE id = ? AND (status = 'free' OR (status = 'held' AND held_until <= ?))",
        (user, now + ttl, seat_id, now))
    return cur.rowcount == 1

def confirm(seat_id, user, now):
    cur = db.execute(
        "UPDATE seat SET status = 'booked' WHERE id = ? AND held_by = ? AND held_until > ?",
        (seat_id, user, now))
    return cur.rowcount == 1

print(hold(1, 'ana', now=1000))      # True   ana wins the seat until 1300
print(hold(1, 'ben', now=1010))      # False  the hold is still live
print(confirm(1, 'ana', now=1200))   # True   paid in time
print(hold(1, 'ben', now=1400))      # False  booked seats never come back

Time ranges are harder than seats. A unique index cannot say that two bookings of the same room must not overlap. Three options: split time into fixed slots (30 minutes) and put a unique constraint on (room_id, slot); use a database that supports an exclusion constraint on ranges (PostgreSQL's EXCLUDE USING gist with the range overlap operator); or lock the room's row, check for overlap inside the transaction, then insert. The check-and-insert must run in one transaction under that lock.

Cancelled rows must not block a rebooking. If cancelled bookings stay in the table, a plain unique index would refuse the next person. Use a partial unique index that covers only active rows (WHERE status <> 'cancelled'), or move cancelled rows to a history table.

Counts instead of seats. For general admission, do not read the sold count and write it back. Use one statement: UPDATE show SET sold = sold + 1 WHERE id = ? AND sold < capacity, and check the row count. Selling a little past capacity on purpose (overbooking, as airlines do) is a business rule, so make it an explicit number, not an accident of a race.

Retries must not double book. A user who taps twice, or a client that retries after a timeout, sends the same request again. Give each booking attempt an idempotency key and store it with a unique constraint so the second request returns the first result. See the idempotency chapters for the full pattern.