Part 3 of 5 · 2 min read
Phantoms under snapshot isolation
A transaction's filtering query can still miss a row another transaction is concurrently inserting, since neither snapshot reflects the other's new row.
Think of two people, each independently counting how many guests are currently signed into an event. Each gets an accurate count from their own moment. Yet a brand new guest sign-in by someone else, happening between the two counts, still never shows up in either tally.
In plain words: snapshot isolation prevents a transaction from seeing a value already read change partway through, as covered on the previous page, but it does not fully prevent every phantom. If one transaction's filtering query runs, and then a second, concurrent transaction inserts a new row that would have matched that same filter, the first transaction's snapshot does not include that new row, since it did not exist yet in the first transaction's own snapshot. If the first transaction relies on that filtering query's result to decide whether some action is safe, it can still make an unsafe decision based on an incomplete count.
A worked example. A transaction checks SELECT COUNT(*) FROM reservations WHERE room_id = 101 AND date = '2026-06-01';, finds zero, and proceeds to insert a new reservation for that room and date; concurrently, a second transaction performs the exact same check, also finds zero in its own snapshot, and also inserts a reservation for the same room and date, resulting in two reservations for a room meant to hold only one.
The key lines.
-- Both transactions see zero existing reservations in their own snapshot
SELECT COUNT(*) FROM reservations WHERE room_id = 101 AND date = '2026-06-01';
-- Each then independently inserts, unaware of the other's concurrent insert
INSERT INTO reservations (room_id, date, customer_id) VALUES (101, '2026-06-01', 42);
Phantom under snapshot isolation
A phantom under snapshot isolation occurs when a transaction's filtering query does not, and cannot, reflect a row another transaction concurrently inserts, since neither transaction's snapshot includes a row created after that snapshot was taken.
Example: two people each independently counting signed-in guests, each missing a brand new sign-in happening between their counts, mirrors this kind of phantom.