Duplicate Seat Booking at the Ardwick Theatre
The Ardwick Theatre has 620 seats and sells tickets through two box-office terminals and a website. Its database has a Performance table, a Seat table and a Booking table. When a customer chooses a seat, the application runs SELECT to check that no booking row exists, takes payment, then runs INSERT to create the booking. Last Saturday, two customers both paid for seat F14 at the 19:30 show. The box office had to move one customer and give a £40 voucher. The database log shows the two SELECT statements ran 0.8 seconds apart, but the second INSERT waited 2.1 seconds for a lock and then succeeded. The theatre also wants to introduce 'pay what you can' tickets on Mondays, with a variable price and a limited allocation of 50 seats per performance. The manager proposes adding a price column to the Seat table and a sold flag to the Performance table.
1. Explain how the duplicate booking occurred and propose a database-level change that prevents it, referring to transactions, isolation and constraints.
Read the worked answer
This is a check-then-act race condition. Both customers ran SELECT before either booking had been committed, so both saw seat F14 as free. The second INSERT waited for a lock, but a lock wait alone does not prevent a duplicate. After the first transaction committed, the second insert proceeded because the schema had no rule saying that a performance and seat pair must be unique. The 2.1-second wait shows serialisation, but not the right kind.
The core fix is a unique constraint or unique index on the booking allocation table, for example UNIQUE(performance_id, seat_id). The database will then reject the second insert even if the application check is wrong. The application should catch that duplicate-key error and offer another seat or refund the payment.
The check and insert should also be part of one transaction with suitable isolation. Options are to use SELECT ... FOR UPDATE on the seat row, use a serialisable isolation level, or use an atomic conditional insert such as INSERT ... SELECT ... WHERE NOT EXISTS. Payment should be authorised before the seat hold but captured only after the booking insert succeeds, or the seat should be held with an expiry before payment. The website and box office must use the same booking service, not separate checks against the same table.
2. The manager proposes storing `price` in `Seat` and `sold` in `Performance`. Critically evaluate this design and suggest a better schema for performances, seats and ticket types.
Read the worked answer
Both proposed columns are badly placed. A price belongs to a performance and a ticket type, not to the physical seat. The same seat may cost more on a Saturday, less at a matinee, or be sold as a concession. A single price value in Seat cannot represent those cases, and updating it for one performance would affect others. A sold flag in Performance is even less useful: one flag cannot show which of 620 seats are sold. It would also have to be reset and could become inconsistent with the booking rows.
A better design separates the physical auditorium from the sale. For example:
Performance(performance_id, show_id, start_datetime, auditorium_id)Seat(seat_id, auditorium_id, row_label, seat_number, zone_id)TicketType(ticket_type_id, performance_id, name, fixed_price, minimum_price, allocation)Booking(booking_id, performance_id, customer_id, status, created_at)Ticket(ticket_id, booking_id, performance_id, seat_id, ticket_type_id, price_charged, status)
Add UNIQUE(performance_id, seat_id) on the seat allocation, whether in Ticket or a separate SeatHold table. For pay-what-you-can, price_charged can vary per ticket while TicketType.minimum_price sets a floor and allocation limits the offer to 50 seats. The allocation should be enforced transactionally, for example by locking the allocation row or using a counter with a check constraint, so two customers cannot both take the fiftieth discounted seat.
3. Explain how the system should handle a payment failure after a seat has been held, and why the website and box office must use the same transaction rules.
Read the worked answer
A seat should first be placed in a HELD state with an expiry time, for example 10 minutes, using the unique constraint on performance and seat. Payment should be authorised but not necessarily captured at that point. If authorisation fails, the hold is cancelled and the seat becomes available immediately. If the customer abandons the page, a scheduled job expires the hold and releases the seat.
If payment succeeds but the database commit fails, the system must reconcile with the payment provider. It should use an idempotency key so a retry does not charge twice, and it should either confirm the booking or void/refund the payment. A payment should never be captured without a confirmed seat allocation. Logs should record the hold, payment reference and final status so support staff can resolve disputes.
The website and box office must use the same booking service, the same unique constraint and the same isolation rules. If the website holds a seat with application logic while the box office uses a separate sold flag, one channel can sell a seat that the other has only checked. A single transactional service prevents that. The two channels may have different user interfaces, but they must not have different definitions of when a seat is reserved.