Every PostgreSQL developer knows the four isolation levels from the SQL standard: Read Uncommitted, Read Committed, Repeatable Read, Serializable. Fewer know that PostgreSQL actually implements only three of them. Requesting Read Uncommitted gets you Read Committed behavior — there is no way to observe dirty data, because dirty reads are fundamentally incompatible with how PostgreSQL stores row versions. And the level most teams never touch, Repeatable Read, quietly protects against more anomalies than the standard requires of it.
The gap between what the isolation level names suggest and what PostgreSQL actually does under the hood causes real production bugs: lost updates that only appear under load, “could not serialize access” errors that crash batch jobs at 3 AM, and duplicate key violations that seem logically impossible. This post walks through what each level actually guarantees in PostgreSQL, the anomalies that survive Read Committed, and how to pick the right level with a retry strategy that holds up in production.
The default: Read Committed and its per-statement snapshots
Read Committed is the default, and its defining trait is that every statement gets a fresh snapshot. A SELECT sees all data committed before the statement began — not before the transaction began. Two successive SELECTs in the same transaction can return different results if another transaction commits in between.
For modifying statements, the behavior is subtler and more consequential. When an UPDATE finds a target row that a concurrent transaction has already modified, it does not fail. It waits for that transaction to finish. If the first updater rolls back, the second proceeds against the original row. If it commits, PostgreSQL re-evaluates the WHERE clause against the updated row version, and if the row still matches, the update proceeds on top of the new version. This is known as Evaluated-Then-Acted (ETA) semantics, and it is the source of most lost-update bugs in Postgres applications.
Consider the classic read-modify-write race, where two sessions concurrently approve the same request rows:
-- Session A
BEGIN;
SELECT is_approved FROM requests WHERE id = 42; -- sees false
-- Session B runs the same SELECT and also sees false
UPDATE requests
SET is_approved = true,
approved_by = 'alice'
WHERE id = 42;
COMMIT;
-- Session B, meanwhile:
UPDATE requests
SET is_approved = true,
approved_by = 'bob'
WHERE id = 42;
COMMIT; -- succeeds, silently overwrites Alice
Bob’s transaction commits successfully. The final state says bob, and Alice’s approval vanished without any error anywhere. Under Read Committed, this is not a bug — it is the documented contract. PostgreSQL even gives you the tool to see it happen: set default_transaction_isolation = 'repeatable read' for a session, and the second update aborts with could not serialize access due to concurrent update instead.
The same per-statement snapshot semantics explain quirks with upserts. INSERT ... ON CONFLICT DO UPDATE in Read Committed will affect a conflicting row inserted by a transaction whose effects are not yet visible to the insert’s snapshot — one of the two outcomes, insert or update, is essentially guaranteed. But INSERT ... ON CONFLICT DO NOTHING can skip a row due to a concurrent insert it cannot see, and MERGE raises a uniqueness violation in the analogous situation instead of retrying its action list. If your deduplication logic depends on upserts being atomic, Read Committed is the level where the edge cases live.
Repeatable Read: snapshot isolation with sharper teeth than advertised
Repeatable Read takes one snapshot per transaction, at the first non-transaction-control statement. All statements in the transaction see the database as of that moment — successive queries see the same data, which is what the name promises. But PostgreSQL’s implementation goes beyond the standard’s requirement. The standard only demands that Repeatable Read prevent dirty and non-repeatable reads; PostgreSQL implements it as full snapshot isolation, which also prevents phantom reads and, crucially, most lost updates.
Replay the approval race under Repeatable Read. Session B’s UPDATE blocks on the row, waits for Session A to commit, and then aborts:
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT is_approved FROM requests WHERE id = 42; -- false
-- Session A commits its update while you're thinking
UPDATE requests
SET is_approved = true,
approved_by = 'bob'
WHERE id = 42;
-- ERROR: could not serialize access due to concurrent update
The transaction cannot modify or lock a row that changed after its snapshot was taken. The application must catch the 40001 SQLSTATE, roll back, and retry the entire transaction — the second attempt sees Alice’s committed change as part of its starting view, so there is no logical conflict.
Snapshot isolation is not bulletproof, though. It permits write skew: two transactions each read a set of rows, each makes a decision based on what it read, and each writes to a row the other did not touch. The classic example is the on-call rule — “at least one doctor must be on call.” Two doctors concurrently check the roster, both see two names, both take themselves off call, both commit, and now nobody is on call. Neither transaction modified a row the other wrote, so snapshot isolation sees no conflict. This is exactly the class of anomaly Serializable isolation exists to catch.
Serializable: SSI and the cost of true serializability
Serializable in PostgreSQL is Repeatable Read plus monitoring for serialization anomalies. The implementation is Serializable Snapshot Isolation (SSI): the database tracks read/write dependencies between concurrent transactions using lightweight predicate locks — visible in pg_locks with mode SIReadLock — and rolls back a transaction if committing it would produce a result inconsistent with any serial order. These predicate locks never block; they only record dependencies, so SSI adds no lock contention, only detection overhead and a higher chance of retry.
Run the on-call scenario under Serializable and one of the two transactions fails at commit:
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT COUNT(*) FROM doctors
WHERE on_call = true
FOR UPDATE; -- no longer needed; SSI tracks the read set
-- decides based on the count, updates own row
COMMIT;
-- one of the two concurrent transactions:
-- ERROR: could not serialize access due to read/write dependencies among transactions
Two operational realities are worth internalizing. First, under Serializable, SELECT FOR UPDATE and explicit table locks are usually unnecessary — the mechanism exists precisely so you do not have to pre-lock. Dropping them reduces both contention and spurious aborts. Second, a sequential scan always takes a relation-level predicate lock, which inflates false conflicts. Encouraging index scans — for example by lowering random_page_cost on SSD-backed systems — measurably reduces serialization failure rates on hot tables.
There is also a special escape hatch: SERIALIZABLE READ ONLY DEFERRABLE. A read-only transaction taking this form waits until it can acquire a snapshot guaranteed free of serialization anomalies, then runs without taking predicate locks at all. It is the only case where Serializable blocks where Repeatable Read would not — a worthwhile trade for reporting queries that must see a consistent cut.
Retry handling: the part everyone gets wrong
Repeatable Read and Serializable both require retry-on-serialization-failure discipline. The rule that matters: only updating transactions can hit serialization conflicts; read-only transactions never will (outside the deferrable case, which blocks instead of failing). A robust helper needs three properties — retry on SQLSTATE 40001 or deadlock (40P01), exponential backoff with jitter so competing clients do not retry in lockstep, and no retries on other error classes, which indicate real bugs rather than contention:
import random
import time
import psycopg
from psycopg import sql
SERIALIZATION_FAILURE = "40001"
DEADLOCK_DETECTED = "40P01"
RETRYABLE = {SERIALIZATION_FAILURE, DEADLOCK_DETECTED}
def run_in_txn(conn, body, max_attempts: int = 4):
"""Run `body(cursor)` inside a SERIALIZABLE transaction with retry.
`conn` must be a dedicated connection, not part of a pool that
silently reuses transactions across calls.
"""
for attempt in range(1, max_attempts + 1):
try:
with conn.transaction():
conn.execute("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE")
with conn.cursor() as cur:
return body(cur)
except psycopg.errors.SerializationFailure as exc:
if attempt == max_attempts:
raise
sleep = min(2 ** attempt * 0.05, 1.0)
time.sleep(sleep + random.uniform(0, 0.05))
# The retry loop must wrap the WHOLE business transaction.
def approve_request(conn, request_id: int, approver: str):
def body(cur):
cur.execute(
"SELECT is_approved FROM requests WHERE id = %s",
(request_id,),
)
row = cur.fetchone()
if row is None or row[0]:
return False # missing or already approved
cur.execute(
"""
UPDATE requests
SET is_approved = true,
approved_by = %s
WHERE id = %s
""",
(approver, request_id),
)
return True
return run_in_txn(conn, body)
Three details trip teams up here. First, the retry must restart the entire transaction, not just the failed statement — the snapshot itself was the problem. Second, any non-database side effects inside the transaction (HTTP calls, message publishing, cache invalidation) must happen only after commit, or a retry will duplicate them; the transaction body should touch nothing but the database. Third, keep transactions short. A retry loop does not rescue a transaction that holds snapshots across user think-time; it just multiplies the aborts.
There is a complementary tool when the contended resource is not a row: advisory locks. These are application-defined, 64-bit locks coordinated through a shared lock table. pg_advisory_lock(key) is session-level — it survives past transaction commit until explicitly unlocked — while pg_advisory_xact_lock(key) releases automatically at transaction end, which makes it the safer default in connection-pooled environments where a forgotten unlock would otherwise linger on a recycled session. One trap from the docs: expressions in a select list may be evaluated before LIMIT is applied, so SELECT pg_advisory_lock(id) FROM foo WHERE id > 12345 LIMIT 100 can acquire locks the application never meant to take. Wrap the limiting query in a subquery and lock its output instead.
Choosing a level in practice
A workable decision path:
- Read Committed for simple, single-statement operations — key-value reads, increments expressed as
SET balance = balance + 10, log appends. The lost-update hazard only exists for read-modify-write flows. - Repeatable Read for multi-statement reads that need a consistent view (reports, exports), and for read-modify-write flows guarded by a retry loop. It eliminates lost updates and phantoms at essentially no throughput cost.
- Serializable for the cases snapshot isolation cannot cover: invariants spanning multiple rows where concurrent transactions read one set and write another — balance constraints, allocation rules, the on-call pattern. Use it selectively; SSI works best when few transactions overlap on their read/write sets, and it degrades under broad multi-statement scans.
There is a standing debate about defaults. Some teams flip default_transaction_isolation = 'repeatable read' globally and treat serialization errors as expected traffic; the downside is that every consumer must handle 40001 from day one. The pragmatic middle ground is keeping Read Committed as the default and explicitly requesting Repeatable Read or Serializable in transaction factories — one choke point per service where concurrency policy is chosen deliberately rather than inherited by accident.
Wrapping up
PostgreSQL’s isolation levels are not the SQL standard’s levels. Read Uncommitted does not exist in any observable way, Repeatable Read is snapshot isolation with guarantees beyond its name, and Serializable is optimistic concurrency control that trades aborts for provable correctness. The failure modes that reach production — lost updates, skew, impossible duplicates — are almost always Read Committed edge cases that a deliberate level choice plus a retry discipline would have prevented. Pick the level per transaction, centralize that choice, and make retry handling part of the transaction contract rather than an afterthought.