Postgres transaction isolation in practice: Read Committed, Repeatable Read, Serializable, and the retry loop you need
What each Postgres isolation level actually guarantees, what error 40001 means, and a small retry loop that makes stronger levels safe to use.

Most Postgres applications never choose an isolation level. They run on the default, Read Committed, and it works until the day two requests touch the same rows at the same moment. Then a balance is off by one transfer, or a report adds up to a number nobody can explain.
This post explains what the three levels you can actually use do, with small examples, and ends with the one piece of code that makes the stronger levels practical.
What isolation is for
Transactions run at the same time. Isolation decides how much of that concurrency each transaction is allowed to notice. The less it notices, the easier the code is to reason about, and the more often the database may have to say "try again".
Postgres offers four names in SQL but three behaviours. Read Uncommitted is accepted as syntax and behaves like Read Committed 1. So the real choice is between Read Committed, Repeatable Read and Serializable.
The anomalies, in plain words
- Nonrepeatable read: you read a row twice in one transaction and get two different answers, because someone committed in between.
- Phantom read: you run the same query twice and the set of matching rows changes.
- Serialization anomaly: every transaction is correct on its own, and they all commit, yet the result could not have happened if they had run one after another.
Here is what the Postgres documentation says each level allows:
| Level | Nonrepeatable read | Phantom read | Serialization anomaly |
|---|---|---|---|
| Read Committed (default) | Possible | Possible | Possible |
| Repeatable Read | No | No (in Postgres) | Possible |
| Serializable | No | No | No |
None of the levels allow dirty reads, so you never see uncommitted data.
Read Committed: a new snapshot per statement
Each statement sees the data committed before that statement began. Two statements in the same transaction can therefore disagree:
BEGIN;
SELECT balance FROM accounts WHERE id = 7; -- 500
-- another session commits: UPDATE accounts SET balance = 400 WHERE id = 7;
SELECT balance FROM accounts WHERE id = 7; -- 400
COMMIT;
For single-row work this is fine. An UPDATE that waits for a lock re-checks the row once the other transaction commits, so UPDATE accounts SET balance = balance - 100 WHERE id = 7 does the right thing even with concurrent writers.
It gets awkward with multi-step logic: read a value, decide in application code, write back. Between the read and the write, the world may have changed. The documentation also warns that a single UPDATE or DELETE with a complicated condition can see an inconsistent mix of old and new rows.
Repeatable Read: one snapshot per transaction
Here the snapshot is taken at the first statement and kept. Repeating a SELECT gives the same answer, however much other sessions commit.
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 7; -- 500
-- another session commits a change to 400
SELECT balance FROM accounts WHERE id = 7; -- still 500
COMMIT;
That is ideal for reports and exports that must be internally consistent: totals that match the line items because they all come from the same moment.
The price is that writes can conflict. If you try to modify a row that another transaction changed after your snapshot began, Postgres stops you:
ERROR: could not serialize access due to concurrent update
Read-only transactions never hit this. Updating ones must be ready to retry.
Serializable: the "as if one at a time" guarantee
Serializable adds detection of dependencies between transactions. The guarantee in the documentation is that any set of concurrently committed Serializable transactions has the same effect as running them one at a time 2.
The classic case is write skew. Two on-call doctors each check that at least one other doctor is on duty, then each takes themselves off. Each transaction was reasonable on its own; together they leave nobody on call. Repeatable Read lets both commit. Serializable aborts one:
ERROR: could not serialize access due to read/write dependencies among transactions
Serializable does not do this by blocking. It tracks what each transaction read using predicate locks, which the documentation says cannot cause blocking or deadlocks. Instead of waiting, a transaction is cancelled when the database sees a dangerous pattern.
Note
Both failures above carry SQLSTATE
40001. That one code is what your application should look for.
The retry loop
The rule from the documentation is short: when you get a serialization failure, abort the transaction and retry the whole thing from the start. Not the failing statement, the whole transaction, including the reads that led to your decision.
import time
import psycopg2
from psycopg2 import errors
def run_serializable(conn, work, max_tries=5):
for attempt in range(max_tries):
try:
with conn: # commit or roll back
with conn.cursor() as cur:
cur.execute("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE")
return work(cur)
except errors.SerializationFailure: # SQLSTATE 40001
time.sleep(0.05 * 2 ** attempt) # short, growing pause
raise RuntimeError("gave up after repeated serialization failures")
Three details matter. work must be safe to run twice, so it should not send an email or call an outside API inside the transaction. The isolation level must be set before the first query. And the pause should grow, ideally with some randomness, so competing transactions do not collide again in lockstep. If you have read about retrying API calls, the same ideas apply; see Idempotency keys: making retries safe in any API.
Keeping Serializable cheap
- Mark transactions
READ ONLYwhen they only read. - Keep them short. Long transactions overlap with more others and fail more often.
- Set
idle_in_transaction_session_timeoutso a forgotten open transaction cannot hold things up. - Expect more failures when queries do sequential scans, because those take coarser predicate locks. Good indexes help here too.
- Do not trust data read in a transaction that later aborted. Retry until it succeeds.
One surprise: even under Serializable, you can still see unique constraint violations from overlapping transactions that insert the same key. Treat those as retryable too, or check for the key first.
Choosing a level
A reasonable starting point:
- Leave ordinary request handling on Read Committed, and use
UPDATE ... SET x = x + 1style statements orSELECT ... FOR UPDATEwhere you must read then write. Locks and their deadlock rules are covered in the Postgres explicit locking docs; the main rule is to take locks in the same order everywhere. - Use Repeatable Read for reports and exports that need one consistent view.
- Use Serializable for the few operations where invariants span several rows, and wrap them in the retry loop. It is often simpler than hand-built locking, because the database does the checking.
If you also change schemas on a live system, the lock behaviour in Schema changes without downtime in Postgres is the natural next read.


