Snapshots and Visibility

A snapshot is a small structure: the oldest transaction still running, the first id that had not been handed out yet, and the list of ids in progress between them. That is all of it, and it is the sole arbiter of what a query sees. Every version of a row on every page is judged against those three numbers and nothing else.

Questions that sound like different problems keep resolving to the same answer here. "Why did my report disagree with the dashboard by 400 orders" and "why did my second SELECT in the same transaction return a different row count" are both answered by saying when the snapshot was taken. Get that right and the isolation levels in the next topic stop being three names to memorize.

What a Snapshot Contains

The three fields have names that echo the row header without meaning the same thing. A snapshot's xmin is the lowest id still running when the snapshot was taken: everything below it has finished, one way or the other. Its xmax is the first id not yet assigned: nothing at or above it had even started. Between those bounds sits the in-progress list, the ids that were running at that instant and whose work therefore does not exist as far as this snapshot is concerned.

SELECT pg_current_snapshot();
--        pg_current_snapshot
-- 487355012:487355026:487355015,487355019
--     xmin   :   xmax    : in progress

SELECT pg_snapshot_xmin(pg_current_snapshot()) AS oldest_running,
       pg_snapshot_xmax(pg_current_snapshot()) AS next_unassigned;

The visibility rule is then mechanical. A row version is visible when the transaction in its xmin committed and is not one of the ids this snapshot considers still running, and when its xmax is either zero, or belongs to a transaction that aborted, or belongs to one this snapshot cannot see either. There is no global "current state of the table" for that rule to approximate. Two transactions looking at the same page at the same microsecond can therefore return different rows, both correctly, because they are holding different snapshots.

When the Snapshot Is Taken

Under Read Committed, the default, a fresh snapshot is taken at the start of every statement. Two identical SELECTs inside one transaction can legitimately return different results, because the second one sees everything that committed between them. That is not a wrinkle in the implementation; it is what the level is defined to do, and code that assumes otherwise is relying on timing.

BEGIN;  -- default: READ COMMITTED
SELECT count(*) FROM orders WHERE status = 'pending';
-- 1184

-- 40 other sessions commit checkouts during this pause

SELECT count(*) FROM orders WHERE status = 'pending';
-- 1219  <- same query, same transaction, new snapshot
COMMIT;

Under Repeatable Read and Serializable the snapshot is taken once, at the first statement that needs one, and reused for the whole transaction. That is what makes a multi-part report internally consistent, and what makes its last query read data as old as its first. Both counts would have come back 1184, and they would have stayed 1184 for as long as the transaction ran.

The Strawberries, Read Through the Rule

Apply the rule to the March incident and there is nothing left to explain. Checkout A took a snapshot for its SELECT and saw on_hand = 1. Checkout B took its own snapshot a few milliseconds later and also saw 1, because A's decrement did not exist yet and, once it did exist, belonged to a transaction B's snapshot listed as in progress. Neither session could have seen the other's work without a level of isolation that shows uncommitted data, and Postgres does not have one.

So the visibility rules did their job exactly. What failed is the shape of the code: a read, a decision taken in Python, and a write of a value computed from a number that was already stale when the decision was made. The database was asked twice, independently, and answered honestly both times. Every fix in the next topic works by removing that gap — either by collapsing the read and the write into one statement, by holding a lock across the gap, or by having the engine detect afterwards that the two transactions could not both have been right.

The Cluster-Wide Horizon

Snapshots have a second effect that has nothing to do with the query holding one. A row version can only be removed when no snapshot anywhere could still need it, so the oldest snapshot held by any backend sets the boundary for cleanup across the whole cluster. pg_stat_activity.backend_xmin reports each backend's own horizon, and the smallest value among them is the number vacuum has to respect.

This is the mechanism behind a scenario that sounds like superstition until you have seen it. An engineer types BEGIN; and a SELECT into a psql window on a laptop, goes to lunch, and dead rows stop being removable in the delivery_events table that session never touched. The last topic in this chapter is about finding that session and about the settings that stop it happening at all. Nothing about the horizon is per-table or per-database.

One Statement, One Snapshot

A single statement always executes against a single snapshot, however many rows it touches and however long it runs. That property is worth more than most isolation-level discussions, because it means a read-modify-write expressed as arithmetic inside one UPDATE has no gap for another transaction to slip into.

-- unsafe: the value is read, judged elsewhere, then written back
SELECT on_hand FROM inventory WHERE product_id = 4471;
UPDATE inventory SET on_hand = 0    WHERE product_id = 4471;

-- safe: the read, the test and the write are one statement
UPDATE inventory
   SET on_hand = on_hand - 1
 WHERE product_id = 4471
   AND warehouse_id = 2
   AND on_hand > 0;

The second form never computes the new value outside the database. It reads the current value, subtracts one, and refuses to act when the guard fails, all inside a statement that no other transaction can interleave with. It is the snapshot rule doing that work, not any special property of UPDATE. Counters, quotas, seat allocation and stock levels all stop racing the moment the arithmetic moves inside the statement.

Meeting a Concurrent Update Mid-Flight

One documented Read Committed behaviour is worth stating explicitly, because it surprises people who have understood everything else. When an UPDATE, DELETE or SELECT … FOR UPDATE finds a row that another transaction has already modified and not yet committed, it waits for that transaction to finish. If the other transaction rolled back, the statement proceeds against the row it originally found. If it committed, the statement does not use its own snapshot's version — it re-evaluates its WHERE clause against the new version, and only acts if that version still matches.

-- session A                        -- session B
BEGIN;
UPDATE inventory
   SET on_hand = on_hand - 1
 WHERE product_id = 4471
   AND on_hand > 0;                 BEGIN;
-- on_hand: 1 -> 0                     UPDATE inventory
                                       SET on_hand = on_hand - 1
                                     WHERE product_id = 4471
                                       AND on_hand > 0;
COMMIT;                             -- waits for A, then re-checks
                                    -- UPDATE 0   <- on_hand is 0, guard fails
                                    COMMIT;

Session B does not overwrite anything. It waits, sees that the row now holds zero, discovers that its own WHERE clause no longer matches, and reports that it changed no rows. The application learns the sale cannot happen from a row count rather than from a value it read earlier. The same rule has a sharp edge in the other direction: a statement can act on a row its snapshot never saw in that form, so a DELETE FROM inventory WHERE on_hand = 10 can miss a row that read 10 both before and after a concurrent increment, because the version it finally examined held 11.