How PostgreSQL Repeatable Read Isolation Works

In PostgreSQL, a transaction using REPEATABLE READ sees one stable view of committed data across its queries. If another session commits a change while the transaction is open, later reads in that transaction still show the earlier data. This keeps later queries from seeing other sessions’ changes committed after the transaction’s first query.

A transaction is a group of database operations that PostgreSQL treats as one unit. An isolation level controls which concurrent changes those operations can see. PostgreSQL establishes a REPEATABLE READ transaction’s snapshot, or view of the database, when the transaction runs its first query or data-modification statement. Running BEGIN alone does not establish that snapshot, as described in the PostgreSQL documentation for transaction isolation.

Compare two reads across two sessions

Suppose inventory contains this row:

item_id | quantity
--------+---------
1       | 10

Run the test once at READ COMMITTED, then reset quantity to 10 and repeat it at REPEATABLE READ. In Session A, choose one of the BEGIN commands:

-- Session A
BEGIN ISOLATION LEVEL READ COMMITTED;
-- For the second test, use this instead:
-- BEGIN ISOLATION LEVEL REPEATABLE READ;

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

While Session A’s transaction remains open, Session B changes and commits the row:

-- Session B
BEGIN;

UPDATE inventory
SET quantity = 11
WHERE item_id = 1;

COMMIT;

Now run the same SELECT again in Session A. The result depends on the isolation level:

At READ COMMITTED, PostgreSQL gives each statement a fresh view that includes transactions committed before that statement began. Session A therefore sees 11 on its second read. At REPEATABLE READ, Session A keeps the snapshot from its first query, so it continues to see 10. After the test, finish Session A with COMMIT;. PostgreSQL documents these different snapshot rules in its isolation-level behavior.

What the snapshot shows

Session A’s snapshot includes changes that committed before PostgreSQL established the snapshot. It excludes other sessions’ uncommitted changes and changes they commit afterward. Session A’s own earlier writes remain visible to its later queries, even before Session A commits.

PostgreSQL describes its REPEATABLE READ implementation as snapshot isolation. It also prevents phantom reads, in which a later query finds new rows that match the same search condition. However, a stable snapshot does not guarantee that concurrent transactions produce the same result as running one at a time.

Sequences, including values used by serial counters, follow a different rule. PostgreSQL makes sequence changes visible immediately to other transactions, and it does not undo those changes if the transaction that made them aborts.

Handle write conflicts and stronger guarantees

A transaction can fail under REPEATABLE READ if it tries to update or lock a row that another transaction changed after its snapshot was established. PostgreSQL reports a serialization failure with SQLSTATE 40001. When that happens, the code that runs the transaction should abort and retry the whole transaction, including the reads and decisions that led to its writes. PostgreSQL does not retry the transaction automatically. Its serialization failure guidance explains that the code must retry it.

For rules that require concurrent transactions to behave as if they ran one at a time, use SERIALIZABLE. PostgreSQL can detect dangerous concurrent patterns at that level and abort a transaction to prevent an outcome that cannot match any serial ordering. REPEATABLE READ fits work that needs consistent reads within a transaction; SERIALIZABLE fits work whose correctness depends on that stronger guarantee.

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