Movie Ticket Booking OOD: Seat Overbooking Is the Trap—Fix It with Locking

{width=700px style="max-width:100%;height:auto;"}

The core problem

In a movie ticket booking system the trickiest bug is concurrent seat overbooking. When multiple users try to reserve the same seat at the same time, a naive "check availability + reserve" flow can allow two clients to both think the seat is available and both to succeed.

You must make the "check availability + reserve" operation atomic.

Model the domain explicitly

Treat each seat (for a showtime) as having a state machine with three states:

• AVAILABLE — the seat can be taken
• HELD — temporarily reserved for a short window while the user pays (with an expiry)
• BOOKED — final confirmed booking after successful payment

Typical flow:

1. BookingService places a short HOLD (HEL D) with an expiry timestamp.
2. PaymentService completes payment and flips the seat from HELD -> BOOKED.
3. A background job or TTL releases HELD seats back to AVAILABLE when their hold expires.

If two requests race, only one should be allowed to place the HOLD.

Implementation approaches

Two robust approaches that enforce atomicity at the data layer:

1) Optimistic locking (version field)

• Add a version integer column to the seat record (or reservation row).
• Read seat (state + version). Try an update that transitions AVAILABLE -> HELD only if version matches and state is AVAILABLE.
• If update affects 0 rows, you lost the race — return a conflict and ask the user to reselect.

Example SQL (pseudo):

-- Attempt to place a hold
UPDATE seats
SET state = 'HELD', hold_id = :holdId, hold_expires_at = :expiry, version = version + 1
WHERE showtime_id = :showtimeId
  AND seat_id = :seatId
  AND state = 'AVAILABLE'
  AND version = :readVersion;

-- check rows_affected == 1

Or, more commonly without re-reading version explicitly:

UPDATE seats
SET state = 'HELD', hold_id = :holdId, hold_expires_at = :expiry
WHERE showtime_id = :showtimeId
  AND seat_id = :seatId
  AND state = 'AVAILABLE';

-- if rows_affected == 1 => success; else => conflict

2) DB constraint / transactional update (single atomic UPDATE)

• Rely on the database to do the check-and-set in one statement inside a transaction. Example:

BEGIN;
UPDATE seats
SET state = 'HELD', hold_id = :holdId, hold_expires_at = :expiry
WHERE showtime_id = :showtimeId
  AND seat_id = :seatId
  AND state = 'AVAILABLE';
-- If rows_affected == 1, COMMIT; else ROLLBACK and return conflict.
COMMIT;

Both approaches depend on checking the affected-rows count returned by the DB. Zero rows => someone else raced and you must tell the user to reselect.

Notes on constraints: you can also model reservations in a separate table and enforce uniqueness on (showtime_id, seat_id, status) or use an exclusive lock on a row, but the simplest and most portable is the single conditional UPDATE described above.

Confirming a booking

When payment succeeds, flip HELD -> BOOKED atomically and defensively:

UPDATE seats
SET state = 'BOOKED', payment_id = :paymentId
WHERE showtime_id = :showtimeId
  AND seat_id = :seatId
  AND state = 'HELD'
  AND hold_id = :holdId
  AND hold_expires_at > NOW();

-- if rows_affected == 1 => success; else => conflict (hold expired or stolen)

Make this idempotent (safe to call multiple times) and validate the hold_id/payment_id so you don't accidentally book someone else's held seat.

Hold expiry and cleanup

• Store a hold_expires_at timestamp with the HELD state.
• A background job or DB TTL process should release expired HELD seats back to AVAILABLE.
• You might also use a priority queue or Redis sorted set for low-latency expiry processing, but the source of truth must remain the DB so the atomic UPDATE semantics hold.

UX & error handling

• If either the hold placement or the final booking UPDATE affects 0 rows, return a conflict to the client and prompt the user to reselect seats.
• Prefer short hold windows (e.g., 5–15 minutes) to reduce chance of contention and to improve seat availability.
• Show clear messaging: "Seat no longer available; please pick another seat." Avoid ambiguous errors.

Additional recommendations

• Do the atomic check-and-set in the DB layer — not in application memory or caches — since only the DB can provide correct concurrency semantics across multiple app servers.
• Consider optimistic locking when you need to detect concurrent modifications across multiple fields or when you already use a versioning pattern.
• Consider pessimistic locks (SELECT ... FOR UPDATE) only when you must serialize complex multi-row operations; this can reduce throughput.
• Ensure your payment workflow is idempotent and resilient to retries.

Summary

Seat overbooking is prevented by making the availability check and the reservation a single atomic operation at the database level. Use conditional UPDATEs (or optimistic locking with a version column) to ensure only one concurrent request can move a seat from AVAILABLE -> HELD (and later HELD -> BOOKED). If the DB reports 0 rows affected, handle it as a conflict and ask the user to reselect.