Repeatable Read

The PostgreSQL 18 Repeatable Read manual documents a snapshot established by the first non-transaction-control statement, not BEGIN. Later queries exclude subsequent commits by other transactions but include earlier changes made by their own transaction. PostgreSQL prevents non-repeatable reads and phantoms from concurrent commits at this level. Own writes can still change your query results; this is not an immutable view of your own work.

One snapshot, no phantoms

B commits 101 after A's BEGIN but before A's first SELECT, which sees 101. A's later SELECT excludes B's committed 999 and new carol row, but includes A's own update to bob. After A commits, its next SELECT sees the committed changes.

A

B

SELECT→ alice 101, bob 50 (snapshot frozen here)

UPDATE alice = 999 (committed)

INSERT carol (committed)

SELECT→ 101, 50 ← no 999, no carol

COMMIT

SELECT→ 999 and carol ← new txn, new snapshot

A> BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN

B> UPDATE accounts SET balance = 101 WHERE id = 1; -- commits after BEGIN but before A's first query
UPDATE 1

The snapshot is taken by the FIRST query, not by BEGIN.

A> SELECT id, balance FROM accounts ORDER BY id;
 id | balance 
----+---------
  1 |     101 
  2 |      50 
(2 rows)

B changes an existing row AND inserts a new one — both committed instantly.

B> UPDATE accounts SET balance = 999 WHERE id = 1;
UPDATE 1

B> INSERT INTO accounts VALUES (3, 'carol', 300);
INSERT 0 1

A> SELECT id, balance FROM accounts ORDER BY id; -- not 999 — no non-repeatable read; and no carol — no phantom
 id | balance 
----+---------
  1 |     101 
  2 |      50 
(2 rows)

A sees its own UPDATE even though B's later changes remain outside the snapshot.

A> UPDATE accounts SET balance = 60 WHERE id = 2;
UPDATE 1

A> SELECT id, balance FROM accounts ORDER BY id;
 id | balance 
----+---------
  1 |     101 
  2 |      60 
(2 rows)

A> COMMIT;
COMMIT

Only a NEW transaction gets a new snapshot.

A> SELECT id, balance FROM accounts ORDER BY id;
 id | balance 
----+---------
  1 |     999 
  2 |      60 
  3 |     300 
(3 rows)

Verified against PostgreSQL 18.6 · Run it yourself · Scenario source

The write conflict: SQLSTATE 40001

A's UPDATE targets a row that B changed after A's snapshot. The Scenario asserts 40001 both after B has committed and after A waits for B to commit. In its final schedule, B rolls back instead, and A's waiting UPDATE succeeds.

A

B

SELECT (snapshot taken)

UPDATE balance = 300 (uncommitted)

UPDATE balance = 999→ ⏳ waits

COMMIT

⏵ UPDATE balance = 999← 40001 could not serialize access due to concurrent update

A> BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN

A> SELECT balance FROM accounts WHERE id = 1; -- snapshot taken
 balance 
---------
     100 
(1 row)

B commits a change. A can keep READING its stale snapshot — but writing that row is refused.

B> UPDATE accounts SET balance = 150 WHERE id = 1;
UPDATE 1

A> SELECT balance FROM accounts WHERE id = 1;
 balance 
---------
     100 
(1 row)

A> UPDATE accounts SET balance = 200 WHERE id = 1; -- serialization_failure — retry the whole transaction
ERROR:  40001: could not serialize access due to concurrent update

A> ROLLBACK;
ROLLBACK

If the competing write is NOT yet committed, the outcome is decided later: A first waits on the row lock…

A> BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN

A> SELECT balance FROM accounts WHERE id = 1; -- snapshot taken
 balance 
---------
     150 
(1 row)

B> BEGIN ISOLATION LEVEL READ COMMITTED;
BEGIN

B> UPDATE accounts SET balance = 300 WHERE id = 1;
UPDATE 1

A> UPDATE accounts SET balance = 999 WHERE id = 1;
⏳ A is waiting for a lock…

A's waiting UPDATE fails with 40001 after B commits. The next schedule checks the different outcome when B rolls back.

B> COMMIT;
COMMIT

⏵ A's blocked statement fails:
ERROR:  40001: could not serialize access due to concurrent update

A> ROLLBACK;
ROLLBACK

A> BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN

A> SELECT balance FROM accounts WHERE id = 1;
 balance 
---------
     300 
(1 row)

B> BEGIN ISOLATION LEVEL READ COMMITTED;
BEGIN

B> UPDATE accounts SET balance = 400 WHERE id = 1;
UPDATE 1

A> UPDATE accounts SET balance = 999 WHERE id = 1;
⏳ A is waiting for a lock…

B> ROLLBACK;
ROLLBACK

⏵ A resumes:
UPDATE 1

A> COMMIT;
COMMIT

B> SELECT balance FROM accounts WHERE id = 1;
 balance 
---------
     999 
(1 row)

Verified against PostgreSQL 18.6 · Run it yourself · Scenario source

The manual's updating and locking rules cover UPDATE, DELETE, MERGE, SELECT FOR UPDATE, and SELECT FOR SHARE: an actual post-snapshot update or deletion of a target by a committed concurrent transaction causes this conflict. Merely holding a lock, or changing an unrelated row, does not establish it. A row absent from the snapshot is not automatically an update target.

On 40001, abandon the failed attempt and retry the whole transaction from the beginning, including its reads and decisions. A retry can encounter another conflict; it is not a promise of immediate success. See the retry pattern.

The manual calls this implementation snapshot isolation and notes that its stable view can still be inconsistent with every serial ordering. The write-skew and read-only schedules demonstrate that limit.

Reading and locking one row

Try the reading practice before reading this section if you want to predict the result.

In this PostgreSQL 18.6 schedule A retains 100 after B commits 150. A's SELECT FOR UPDATE fails with 40001. After rollback, A's fresh attempt locks 150 and writes 200. A sees its own uncommitted 200, while B's plain read sees 150 until A commits.

A

B

Plain SELECT

UPDATE; autocommit

Plain SELECT

Locking SELECT← 40001 could not serialize access due to concurrent update

Locking SELECT in fresh attempt

Plain SELECT after own UPDATE

A> BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN

A> SELECT balance FROM accounts WHERE id = 1;
 balance 
---------
     100 
(1 row)

B> UPDATE accounts SET balance = 150 WHERE id = 1;
UPDATE 1

A> SELECT balance FROM accounts WHERE id = 1;
 balance 
---------
     100 
(1 row)

A> SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
ERROR:  40001: could not serialize access due to concurrent update

A> ROLLBACK;
ROLLBACK

A> BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN

A> SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
 balance 
---------
     150 
(1 row)

A> UPDATE accounts SET balance = balance + 50 WHERE id = 1;
UPDATE 1

A> SELECT balance FROM accounts WHERE id = 1;
 balance 
---------
     200 
(1 row)

B> SELECT balance FROM accounts WHERE id = 1;
 balance 
---------
     150 
(1 row)

A> COMMIT;
COMMIT

B> SELECT balance FROM accounts WHERE id = 1;
 balance 
---------
     200 
(1 row)

Verified against PostgreSQL 18.6 · Run it yourself · Scenario source

PostgreSQL tuple history versus the version visible to A. The upper view is after B's commit; the lower view is A's fresh attempt before its commit. This is a simplified teaching model, not the complete tuple-visibility algorithm or a measured heap layout. Focus the image region and use arrow keys to read any overflow.

Text equivalent: after B commits, the older tuple containing 100 can remain alongside B's tuple containing 150. A's retained snapshot admits 100, not the later committed version. Locking that changed target fails at REPEATABLE READ. A rolls back; a fresh snapshot admits 150. A's next UPDATE creates its own version containing 200, visible to A before commit but not to B's plain read. After commit, B's fresh read sees 200.

Evidence boundary: the transcript asserts these SQL values and the locking error. The row-version lesson separately inspects two heap tuples in its own schedule. PostgreSQL 18's system-column contract states: “each update of a row creates a new row version for the same logical row”. That storage contract explains the drawing; this new schedule does not inspect its physical tuples. Commit/abort status, command order, tuple flags, and other cases also affect visibility, as the snapshot lesson explains. Do not treat this picture as an xid-only algorithm. Compare InnoDB's undo reconstruction.

Further reading

• PostgreSQL 18: isolation table
• The same lesson on MySQL