How PostgreSQL READ COMMITTED Handles Concurrent Updates

In PostgreSQL, READ COMMITTED is the default setting that controls which committed changes a transaction can see. It gives each SQL statement a fresh snapshot: a view of the rows committed before that statement began. As a result, two statements in the same transaction can return different results when another transaction commits an update between them. The PostgreSQL documentation describes this per-statement visibility rule.

An open transaction does not guarantee that separate statements will see the same changes from other transactions. Knowing when PostgreSQL takes each snapshot helps you decide whether code that reads a value and then acts on it needs extra protection.

A transaction can read the same row twice and get different values

Suppose inventory contains a row with id = 1 and quantity = 10. Session A reads that row twice in one transaction, while Session B updates it and commits between the reads:

Session A

BEGIN;

SELECT quantity FROM inventory WHERE id = 1;
-- Returns 10

Session B

BEGIN;

UPDATE inventory
SET quantity = 9
WHERE id = 1;

COMMIT;

Session A

SELECT quantity FROM inventory WHERE id = 1;
-- Returns 9

COMMIT;

Session A’s first SELECT uses a snapshot taken before Session B committed, so it returns 10. Its second SELECT takes a new snapshot after the commit, so it returns 9.

Each SELECT sees a consistent view for the duration of that statement. It does not see another transaction’s uncommitted changes, or changes that commit while the statement is running. A statement does see changes made earlier by its own transaction, even before that transaction commits.

Concurrent updates can change which rows a command affects

If an UPDATE or DELETE targets a row that another transaction has changed, PostgreSQL waits for that transaction to commit or roll back. If it commits, PostgreSQL checks the command’s WHERE condition against the new version of the row. The command affects the row only if it still matches that condition. PostgreSQL follows the same general rule for SELECT FOR UPDATE and SELECT FOR SHARE, which ask PostgreSQL to lock the rows they select. These rules are part of PostgreSQL’s READ COMMITTED command behavior.

This matters when a command uses a condition that depends on multiple rows or on values another transaction is changing. The command can act on an updated version of a target row while other rows it considers still reflect the snapshot from the command’s start. For complex conditions, that can produce results that seem inconsistent unless you account for the wait and recheck.

Choose a transaction strategy for the logic you need

If code reads a value and makes a decision in a later statement, another transaction can change the value before that decision. For example, a service that checks a quantity and then sends a separate update cannot assume the earlier read still describes the row when the update runs. Put the required rule in the update condition, or lock the row if the later action depends on its current value.

If several statements need to see the same database snapshot, PostgreSQL’s REPEATABLE READ isolation level gives them a snapshot taken at the start of the transaction’s first statement that is not a transaction-control command. Choose between this and READ COMMITTED based on whether the code needs a fresh view for each statement or the same view across the transaction.

What I'm building

Delegate tasks. Get software.

Give Vroni a GitHub issue, bug report, spec, or rough idea. It reads the repo, plans the change, writes code, runs checks, and works toward a review-ready pull request.

Take a look at vroni.com