Two guests, one last room, and both got it

transcript

Two guests click Book on the last deluxe room. Same weekend. Both get it. The hotel is oversold.

Guest A reads the night. One room left. Guest B reads it too. Still one left. Nobody's written yet. A adds one to reserved. B adds one too. Two guests. One room.

Here's the catch. Your database won't save you here. Read committed, the usual default, allows this race.

The bug lives in the gap between reading and writing. So put the check inside the update. Add one only if it still fits. The database checks it on the row as it locks it. So the second update matches zero rows. Sold out.

Guest A's update matched the row. Held for 10 minutes. Guest B sees just sold out. Annoying, but honest.

Three nights? Run the update for every night, in one transaction. Fewer than three rows updated? Roll it all back.

Don't check, then write. Put the check inside the write.

Full breakdown in the lecture.