Repeatable Read
At REPEATABLE READ, PostgreSQL takes one snapshot for the whole transaction — at its first statement (precisely: the "first non-transaction-control statement", so not at BEGIN) — and every subsequent statement reads from that same frozen view. What you saw once, you'll see again: no non-repeatable reads, and (beyond what the SQL standard requires) no phantoms either. This is what the manual and the literature call snapshot isolation.
The price: your snapshot can go stale, and PostgreSQL will refuse to let you overwrite what you can't see.
One snapshot, no phantoms
A> BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGINThe snapshot is taken by the FIRST query, not by BEGIN.
A> SELECT id, balance FROM accounts ORDER BY id;
id | balance
----+---------
1 | 100
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 | 100
2 | 50
(2 rows)
A> COMMIT;
COMMITOnly a NEW transaction gets a new snapshot.
A> SELECT id, balance FROM accounts ORDER BY id;
id | balance
----+---------
1 | 999
2 | 50
3 | 300
(3 rows)Verified against PostgreSQL 18.4 · Run it yourself · Scenario source
The write conflict: SQLSTATE 40001
Reading a stale snapshot is safe. Writing through it is not: if a row you're updating was changed by a transaction that committed after your snapshot, PostgreSQL aborts you with could not serialize access due to concurrent update. And if the competing writer hasn't committed yet, you first wait on its lock — the verdict comes when it commits (fail) or rolls back (proceed):
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;
ROLLBACKIf 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;
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……and fails with 40001 the moment B commits. (Had B rolled back, A would have proceeded.)
B> COMMIT;
COMMIT
⏵ A's blocked statement fails:
ERROR: 40001: could not serialize access due to concurrent update
A> ROLLBACK;
ROLLBACKVerified against PostgreSQL 18.4 · Run it yourself · Scenario source
So the shape of this level: one snapshot per transaction, taken by the first statement rather than by BEGIN, and in PostgreSQL that snapshot also prevents phantoms — stronger than the SQL standard's REPEATABLE READ, which Table 13.1 marks "Allowed, but not in PG". Any UPDATE or DELETE of a concurrently-modified row raises 40001 (serialization_failure), which is not an error to log and swallow: the manual tells you to "retry the whole transaction from the beginning", and the patterns chapter hands you a ready-made retry helper.
One thing this level still won't give you: stale reads are reads all the same. Two REPEATABLE READ transactions can each decide something against their own snapshot that's jointly impossible — that's write skew, and the next level up is where it finally gets caught.