Database transactions, locks and never selling the same room twice
Updated Nov 10, 2026 · 4 min read
Most double bookings, double spends and oversold sales come from the same bug. Code reads something, decides, then writes, and another request does the same thing in between. Databases have several tools to stop it. Knowing which one fits is a large part of designing anything that sells a limited thing, whether that is hotel rooms, concert seats or the last item in stock.
The race in check-then-insert
Here is the shape of the bug. Two guests want the last Deluxe room on Dec 31. Both requests run the code below at nearly the same moment.
left = SELECT rooms_left FROM inventory WHERE hotel = 7 AND night = '2026-12-31'
if left > 0:
INSERT INTO bookings ...Both reads return 1, because neither insert has happened yet. Both checks pass and both inserts succeed. Reading again just before the insert only makes the gap smaller. Nothing in this code makes the read and the write one step.
What a transaction does and doesn’t stop
Wrapping the code in a transaction feels like the fix, but a transaction promises all or nothing, not one at a time. What other transactions can see and do in the meantime depends on the isolation level, and the defaults are weak.
| Level | What it promises | What still gets through |
|---|---|---|
| Read committed (Postgres default) | Each statement sees only committed data | Two transactions reading the same count and both acting on it |
| Repeatable read (MySQL default) | The whole transaction reads from one snapshot | Two inserts of different rows that together break a rule |
| Serializable | The result matches some one-at-a-time order | Nothing, but conflicting transactions are aborted and must retry |
Serializable is correct, but under heavy contention most transactions abort and retry, and every query in the database pays for the extra checking. Most teams keep the default level and protect the few places that need it.
Let the database enforce the rule
The strongest fixes put the rule where the data lives. If each booking names a specific room, a unique constraint on room and night makes the second insert fail outright, however the requests interleave. If you sell from a count, a conditional update checks and changes the count in one statement.
UPDATE inventory
SET rooms_left = rooms_left - 1
WHERE hotel = 7 AND night = '2026-12-31' AND rooms_left > 0
-- 1 row changed means you got the room. 0 rows means sold out.The database locks the row for the moment it updates it. The second request waits, then finds zero left and changes nothing. Your code checks how many rows changed and answers the guest.
Pessimistic and optimistic locking
When the logic is too complex for one statement, you choose how to handle the rest of the transaction. Pessimistic locking reads with SELECT … FOR UPDATE, which locks the row until you commit, so other writers wait their turn. Optimistic locking takes no lock. Each row carries a version number, and the update only applies if the version is still the one you read.
UPDATE inventory SET rooms_left = 4, version = 42
WHERE id = 913 AND version = 41
-- 0 rows changed means someone got there first. Read again and retry.| Optimistic (version check) | Pessimistic (row lock) | |
|---|---|---|
| Bets that | Conflicts are rare | Conflicts are likely |
| On a conflict | The loser redoes its work | The second writer waits |
| Good for | Many rows, few writers each | A few hot rows everyone wants |
| Watch out for | Retry storms on hot rows | Long transactions holding locks, and deadlocks |
A hotel on an ordinary Tuesday suits optimistic locking. The same hotel on New Year’s Eve, with hundreds of guests a second on one row, suits a lock, because waiting a few milliseconds beats failing nine times out of ten. Keep locked work short, and never hold a lock while you call a payment provider.
Holds that expire
Paying takes a minute or two, so you can’t keep a transaction open while the guest types a card number. Instead, the conditional update takes the room and writes a hold with an expiry time, then commits. If payment succeeds, a second update turns the hold into a booking, but only if the hold is still there. A cleanup job returns expired holds to the count.
- Take one room off the count and write a hold that expires in 10 minutes.
- Charge the card with the payment provider.
- Confirm with an update that only matches a live hold.
- If the hold had expired and the room is gone, refund the charge.
- Send the confirmation last, since it can’t be taken back.
Keep searches off the primary
Travel sites see hundreds of searches for every booking. Searches can show availability a few seconds old, so read replicas answer them, and the primary is left for holds and bookings. The rule that matters is that the decision to sell a room is always made on the primary, never from a replica or a cache.