Phantom rows

Meeting room 1 has one booking so far: Carol at 9:00. Transaction A is checking whether 10:00 is free, and will look again later before it finishes.

A counts the bookings at 10:00 and gets zero.

Meanwhile B inserts a booking for Bob at 10:00 and commits.

Hiding the phantom isn’t the same as being safe. Now Alice and Bob both want 10:00. Each transaction counts, sees zero, and inserts a booking. Both commit, and room 1 has two meetings at once.

This is write skew again, under REPEATABLE READ. The difference is that the rows involved didn’t exist when the checks ran.

SERIALIZABLE catches it. Postgres remembers that Alice’s transaction searched for bookings at 10:00, not only which rows came back. When Bob’s insert lands inside that search, it records a dependency. With arrows in both directions, Bob’s commit fails.

You can stop here and you’ll know what a phantom is and why it’s dangerous. The next four steps are how Postgres locks a search, and what to use instead.

Postgres records those searches as SIRead locks, also called predicate locks. They never block anyone; they only take notes. How much they cover depends on how the query ran.

With an index on the slot column, the lock covers the index pages the search touched: in effect, the range 10:00. With a sequential scan, it covers the whole table.

That makes indexes part of correctness under SERIALIZABLE, not just speed. Without an index, every search locks the entire table, and any insert anywhere conflicts with it.

Bookings for different rooms on different days start failing with serialization errors. Same code, same data, many more retries. How the index is laid out is in Why indexes are fast.

Another fix is to create a row that both transactions must lock. Each starts by locking room 1 itself with SELECT … FROM rooms WHERE id = 1 FOR UPDATE. Bob waits, then counts again under READ COMMITTED and finds Alice’s booking.

Switch to REPEATABLE READ and it breaks: Bob’s snapshot was taken when his first statement started, before the wait, so he still counts zero. At that level the lock only helps if the transaction also updates the room row, which turns Bob’s wait into an error.

The strongest answer doesn’t depend on the level at all. For fixed slots, a unique index on (room, slot) makes the second insert fail.

For time ranges, Postgres has exclusion constraints: EXCLUDE USING gist (room WITH =, during WITH &&) rejects any two rows whose ranges overlap in the same room. It needs the btree_gist extension. The database then enforces the rule for every writer, including the one you haven’t written yet.