· Development

Why two SELECTs in one transaction can disagree: PostgreSQL READ COMMITTED and snapshots

Under PostgreSQL's default READ COMMITTED level, two SELECTs in one transaction can see different data. Learn how statement snapshots, row rechecks, and transaction retries work.

DevelopmentDatabasesSQL

Run the same SELECT twice inside one transaction, get two different answers, and it looks like a bug. Under READ COMMITTED, PostgreSQL’s default isolation level, the documentation says this is expected. This article follows the docs for PostgreSQL 18, the newest stable release as of October 7, 2026. The same explanation appears in the docs for 14 and for 19, which is still in beta.

An example with two sessions

Say account 1 holds 500 and two sessions run in this order:

-- Session A
BEGIN;
SELECT balance FROM accounts WHERE id = 1;  -- 500

-- Session B (run without BEGIN)
UPDATE accounts SET balance = 400 WHERE id = 1;

-- Session A
SELECT balance FROM accounts WHERE id = 1;  -- 400
COMMIT;

A statement run without BEGIN is a transaction of its own in autocommit mode, committed when the statement finishes if it succeeds. Session B’s change commits between A’s two SELECTs, so A’s second SELECT sees 400.

A new snapshot for every statement

PostgreSQL uses multiversion concurrency control (MVCC), so each SQL statement sees a snapshot of the data as of some point in time. Under READ COMMITTED, a SELECT without FOR UPDATE/FOR SHARE sees only data committed before that query began. It sees neither uncommitted data nor changes that other transactions commit while it runs. It does see earlier changes made in its own transaction, even before they are committed.

The reference point is the start of the query, not the start of the transaction. That’s why the docs’ table marks READ COMMITTED as allowing nonrepeatable reads and phantom reads, where the set of rows matching a condition changes. The example above is a nonrepeatable read.

The standard and PostgreSQL

According to the PostgreSQL docs, the SQL standard defines the three levels below SERIALIZABLE by the phenomena that must not occur at each, and it sets only minimum protections, so a stronger implementation is allowed. The standard’s default level is SERIALIZABLE; PostgreSQL’s default is READ COMMITTED. You can ask for READ UNCOMMITTED, but it behaves like READ COMMITTED, so dirty reads don’t happen. Other databases may implement levels with the same names differently, and everything here applies only to PostgreSQL.

UPDATE rechecks the latest row

UPDATE, DELETE and SELECT FOR UPDATE/FOR SHARE also look only for target rows committed as of the command’s start. If another transaction is already changing a row they find, they wait for it to finish. If that transaction commits, a deleted row is skipped; for an updated row the WHERE clause is evaluated again on the new version, and the command goes ahead only if the row still matches.

If this UPDATE had to wait for another transaction that was updating the same row, and that transaction committed, SET balance = balance - 100 is evaluated against the newly committed row version. If instead the application read 500 earlier, subtracted 100 itself and writes the constant SET balance = 400, it can overwrite that newly committed value as long as the WHERE clause still matches. To protect a row against concurrent updates, the docs point to SELECT FOR UPDATE, SELECT FOR SHARE or an appropriate LOCK TABLE mode. SELECT FOR UPDATE holds other transactions off only until the transaction holding the lock commits or rolls back.

Re-evaluation can also give surprising results. In the docs’ example, a table website has two rows with hits of 9 and 10. One transaction runs UPDATE website SET hits = hits + 1 and hasn’t committed yet when another session runs DELETE FROM website WHERE hits = 10. Nothing gets deleted. The row that was 9 didn’t match when the rows were first found, and the row that was 10 had already become 11 by the time the lock was obtained.

When the whole transaction needs the same data

REPEATABLE READ takes its snapshot at the start of the first statement in the transaction that isn’t a transaction-control statement, and keeps it to the end. Repeated SELECTs therefore see the same data. The isolation level has to be set before the first query or data-modifying statement.

BEGIN ISOLATION LEVEL REPEATABLE READ;

In exchange, you need to be ready to retry. If a row this transaction tries to update or lock was changed or deleted by another transaction that committed after this one began, the transaction is rolled back with could not serialize access due to concurrent update. Serialization errors can carry different messages, but their SQLSTATE is always 40001, so checking the code is safer. The docs say to rerun the whole transaction, including the logic that decides which SQL to send and with which values, and that is why PostgreSQL has no automatic retry. Read-only transactions never hit this conflict.

Another query whose result defies intuition, this time without any concurrency, is covered in NOT IN and NULL.

Sources

Links and content checked on October 7, 2026.

Back to the blog