Two kiosks, one seat: a booking race and a deadlock
A school concert booking system uses a single SQL table Seat(seat_no PRIMARY KEY, status, booking_ref). Three kiosks run this Python-style pseudocode for each sale:
row = SELECT status FROM Seat WHERE seat_no = ? if row.status == 'free': UPDATE Seat SET status = 'held', booking_ref = ? WHERE seat_no = ?
The database uses autocommit, so each statement commits separately. In the first hour, seat 47 was sold twice: kiosk A read 'free' at 10:00:01, kiosk B read 'free' at 10:00:01, and both updated. A second bug appears when a booking takes two seats: one kiosk locks seat 12 then 13, another locks 13 then 12, and both wait indefinitely. The database server has a 30-second lock timeout, but the kiosk screen freezes. 600 students are buying 400 seats.
1. Explain how seat 47 was sold twice, using an interleaving of the two kiosks, and rewrite the sale so that it is atomic.
Read the worked answer
The check and the update are two separate autocommitted statements, so there is no atomicity. One possible interleaving is: A SELECTs seat 47 and sees free; B SELECTs seat 47 and sees free; A UPDATEs seat 47 to held with A's booking_ref; B UPDATEs seat 47 to held with B's booking_ref. Both updates succeed because the WHERE clause checks only seat_no, not status. The PRIMARY KEY prevents two rows for seat 47, but it does not prevent two sales of the same row. Autocommit makes it worse: each statement releases its locks immediately, so no lock protects the sequence.
The fix is to make the state change itself the check:
UPDATE Seat SET status = 'held', booking_ref = ? WHERE seat_no = ? AND status = 'free';
Then inspect the number of affected rows. If it is 1, the seat was successfully held; if it is 0, another kiosk took it and the student must choose again. For a multi-seat booking, run all the conditional updates inside one transaction and commit only if every seat succeeds; otherwise roll back, so a partial booking is never left behind. SELECT ... FOR UPDATE inside a transaction is an alternative, but the conditional update is simpler and holds locks for less time.
2. Explain the deadlock between seats 12 and 13 and describe how you would change the booking logic and the client code to handle it.
Read the worked answer
Kiosk A holds the lock on seat 12 and requests seat 13; kiosk B holds the lock on seat 13 and requests seat 12. Neither can proceed, so each waits for the other: a deadlock. The 30-second timeout will cause the database to abort one transaction, but if the client does not catch the error, the kiosk appears to freeze and the student receives no message.
The main fix is consistent lock ordering: sort the requested seat numbers ascending before updating, so every kiosk locks 12 before 13. This removes the circular wait. Keep the transaction short, and avoid doing slow work such as printing or emailing inside it. Catch deadlock and timeout exceptions in the client, roll back, and retry a limited number of times with a short backoff; if retries fail, show 'those seats are busy, please choose again'. Log the event so repeated deadlocks are visible. A shorter lock timeout can improve the user experience, but it does not remove the need for ordering and retry handling.
3. Describe how you would test the revised booking code for concurrency, and explain why adding a UNIQUE constraint on booking_ref would not solve the double-sale problem.
Read the worked answer
Test with two separate database connections or threads. Use a barrier so that both read before either updates, then run the scenario 100 times and assert that exactly one booking succeeds for seat 47 and that the seat has only one booking_ref. Repeat with two-seat bookings taken in opposite orders (12 then 13, and 13 then 12) and check that no transaction hangs and that the loser receives a clear retry or failure message. Also test a multi-seat booking where the second seat is already taken, and confirm that the first seat is rolled back.
A UNIQUE constraint on booking_ref would not stop two kiosks selling the same seat: each has a different booking_ref, and the duplicated fact is the seat, not the reference. If one booking can cover several seats, a unique booking_ref would also wrongly prevent the same booking from appearing on multiple seat rows. The protection must come from the atomic conditional update on seat_no and status, plus correct transaction handling.