A phantom changes the set, not a row
Your WHERE clause suddenly matches a row another transaction inserted. Nothing you already read was modified.
Is a snapshot enough?
Bad: Trust the snapshot
PostgreSQL REPEATABLE READ keeps the recount at 0, yet both bookings commit.
Good: Read vs write phantom
A snapshot fixes what you see, not what you write from it.
Check-then-insert, the dangerous phantom
- Check a rule with a predicate read
- Decide from the result
- Insert a row that changes the answer
- Run two at once. Both pass; the rule breaks.
So lock it. Run SELECT … FOR UPDATE on the overlapping bookings first.
The catch
It locks the rows it returns. It returned none, so in PostgreSQL nothing is locked and both still insert.
Every fix gives them something real to collide on
Cheapest first. A constraint, then slot rows, then a parent-row lock, and SERIALIZABLE with retries last.
81,000
slot rows for 50 rooms, 90 days ahead
Half-hour slots from 9:00 to 18:00 is 50 × 18 × 90. Trivial to store, and a second booker finds the slot already taken.
Sharded? Make the conflict a single key.
Two shards can each confirm “no overlap here”. A conditional put on one slot key fails the second writer instead.
Remember
You can’t lock a row that doesn’t exist yet. Make the conflict real with a constraint, slot rows, a parent lock or SERIALIZABLE.