Isolation levels as PostgreSQL implements them · PostgreSQL

PostgreSQL accepts all four SQL isolation levels and implements three. READ UNCOMMITTED is accepted and behaves as READ COMMITTED, because the storage design has no way to show you an uncommitted version: dirty reads are not merely disallowed, they are unimplementable.

The three real levels differ in exactly one respect — when the snapshot is taken, and what conflicts are checked at commit.

Level	Snapshot	Detects
`READ COMMITTED`	New one per statement	Nothing; last writer wins after waiting
`REPEATABLE READ`	One per transaction	Write-write conflicts
`SERIALIZABLE`	One per transaction	Write-write and read-write dependencies

Everything below is that table demonstrated.

Read committed

The default. Each statement gets a fresh snapshot, so a transaction can see different data on two executions of the same query.

Read-only / Safethe same query twice in one READ COMMITTED transaction— Captured on 18.6. Another session committed UPDATE iso SET v=99 WHERE id=1 between the two reads.

$ psql -U postgres -c "BEGIN ISOLATION LEVEL READ COMMITTED" -c "SELECT v FROM iso WHERE id=1" -c "SELECT pg_sleep(6)" -c "SELECT v FROM iso WHERE id=1" -c "COMMIT"

    step    | v
------------+----
first read | 10

           step              | v
-------------------------------+----
second read, same transaction | 99

This is a non-repeatable read, and under this level it is correct behaviour rather than an anomaly. The transaction asked twice and got two answers because two different things were true at the two moments.

Read-modify-write in read committed

The pattern that fails here is not the double read. It is this:

BEGIN;
SELECT balance FROM accounts WHERE id = 42;      -- application reads 100
-- application computes 100 - 30 = 70
UPDATE accounts SET balance = 70 WHERE id = 42;
COMMIT;

If another transaction commits a withdrawal between the SELECT and the UPDATE, this transaction overwrites it. No error is raised. Nothing in the logs records it. The money is simply gone.

Three correct fixes, in order of preference:

Do the arithmetic in the database: UPDATE accounts SET balance = balance - 30 WHERE id = 42. Read committed re-evaluates the row at the moment of the update, so this is safe.
Lock the row on read: SELECT … FOR UPDATE, which makes the second transaction wait.
Raise the isolation level and handle the retry.

The first is free and covers most cases. Reach for it before the others.

Repeatable read

One snapshot for the whole transaction. The same query returns the same answer every time, whatever anyone else commits.

Read-only / Safethe identical sequence under REPEATABLE READ— Captured on 18.6. The same concurrent UPDATE to 99 was committed between the two reads.

$ psql -U postgres -c "BEGIN ISOLATION LEVEL REPEATABLE READ" -c "SELECT v FROM iso WHERE id=1" -c "SELECT pg_sleep(6)" -c "SELECT v FROM iso WHERE id=1" -c "COMMIT"

    step    | v
------------+----
first read | 10

           step              | v
-------------------------------+----
second read, same transaction | 10

Now the interesting case. What if the transaction then tries to write the row it has an outdated view of?

Service impact possiblea write conflict under REPEATABLE READ— Real error from 18.6. Another session committed UPDATE iso SET v=50 WHERE id=1 between the read and the write.

$ psql -U postgres -c "BEGIN ISOLATION LEVEL REPEATABLE READ" -c "SELECT v FROM iso WHERE id=1" -c "UPDATE iso SET v = v + 1 WHERE id=1" -c "COMMIT"

ERROR:  could not serialize access due to concurrent update
ROLLBACK

It does not wait and then proceed. It refuses. The transaction’s whole guarantee is that it acts on a consistent view; writing v + 1 where v is a value it can no longer see would break that, so the only correct outcome is to abort.

Raising the isolation level is an application change, not a configuration change

REPEATABLE READ and SERIALIZABLE can both abort a transaction that did nothing wrong, at any point, purely because of what another session did. The database is not misbehaving; that is the guarantee working.

Which means every transaction at these levels needs retry logic. Not “should have” — needs. A cluster set to default_transaction_isolation = 'repeatable read' for an application that does not retry will produce user-visible errors under exactly the load conditions where you least want them.

A correct retry loop:

catches SQLSTATE class 40: 40001 (serialization_failure) and 40P01 (deadlock_detected);
retries the whole transaction from BEGIN, because the snapshot is what was wrong and re-issuing the failed statement alone changes nothing;
backs off between attempts, and caps them;
does not retry anything else. 23505 unique violation means the data is genuinely in conflict, and retrying it will fail identically forever.

Write skew: what repeatable read does not catch

Here is the case that justifies SERIALIZABLE existing.

A rota table. The business rule is that at least one person must be on duty. Two people, both on duty, both decide to sign off at the same moment. Each transaction checks the rule before acting.

BEGIN;
SELECT count(*) FROM on_call WHERE on_duty;   -- each reads 2, rule satisfied
UPDATE on_call SET on_duty = false WHERE name = <self>;
COMMIT;

Service impact possibleboth transactions under REPEATABLE READ— Captured on 18.6. Two concurrent sessions, alice and bob, each running the sequence above.

$ # two concurrent psql sessions, both at REPEATABLE READ

alice: COMMIT
bob:   COMMIT

name  | on_duty
-------+---------
alice | f
bob   | f

still_on_duty
---------------
           0

Both committed. Nobody is on duty. The business rule is broken and nothing anywhere reports an error.

And repeatable read was not at fault. Neither transaction read a value that changed. No row was written by both. There was no write-write conflict to detect — each wrote a different row. The conflict is between what one transaction read and what the other wrote, and repeatable read does not look at that.

This is write skew. It appears wherever a transaction reads a set of rows to check a condition and then writes a different row on the basis of it: inventory checks, capacity limits, uniqueness enforced in application code, approval quorums, double-booking.

Service impact possiblethe identical scenario under SERIALIZABLE— Real error from 18.6. Same two sessions, same sequence, isolation level changed.

$ # the same two concurrent sessions, both at SERIALIZABLE

alice: COMMIT

bob:
ERROR:  could not serialize access due to read/write dependencies among transactions
DETAIL:  Reason code: Canceled on identification as a pivot, during write.
HINT:  The transaction might succeed if retried.
ROLLBACK

name  | on_duty
-------+---------
alice | f
bob   | t

One commits, one is refused, one person remains on duty. SERIALIZABLE tracked that bob’s write conflicted with what alice had read, found the pair could not be ordered as any serial execution, and cancelled one.

The two failure messages are not the same

They arrive with the same SQLSTATE (40001) and are handled the same way, but they tell you different things about your workload.

Message	Means	Usually indicates
`could not serialize access due to concurrent update`	Two transactions wrote the same row	Row-level contention: a counter, a queue head, a hot record
`could not serialize access due to read/write dependencies among transactions`	A read-write cycle that has no serial equivalent	A read-check-write pattern: the write skew shape

The first is often fixable in the application without changing isolation — move the arithmetic into the UPDATE, or restructure so that different transactions touch different rows. The second usually means the rule being enforced genuinely needs SERIALIZABLE, or an explicit lock, or a database-level constraint.

Why SERIALIZABLE needs no extra locks, and what it does need

PostgreSQL implements SERIALIZABLE with Serializable Snapshot Isolation. Transactions still run on snapshots and still take no read locks, so a serializable read does not block anything — the property from lesson VII-01 survives intact.

What it adds is bookkeeping. The server records predicate locks (SIRead) describing what each serializable transaction read, and watches for the dependency pattern that makes a cycle possible: one transaction with both an incoming and an outgoing read-write dependency, the “pivot” named in the DETAIL line above. When it finds one, it cancels a participant.

Two operational consequences follow.

Memory, not locking, is the resource. Predicate locks live in shared memory sized by max_pred_locks_per_transaction. When a transaction reads too many rows the tracking is escalated — from rows to pages, and from pages to the whole relation. Escalation is not an error, but a coarser record means more false conflicts, so a serializable workload that reads large ranges will show a rising retry rate for reasons that have nothing to do with the rows it actually touched.

All participants must be serializable. Detection only works between transactions that are being tracked. A SERIALIZABLE transaction running alongside a READ COMMITTED one gets no protection from it. If you adopt SERIALIZABLE for a workload, it has to be for all the transactions that participate in the rule you are protecting, not just the one that seemed risky.

Choosing

Stay on READ COMMITTED unless something specific requires more. It is the default, it never aborts for isolation reasons, and most correctness problems in it are better solved by doing the arithmetic in the UPDATE or by SELECT … FOR UPDATE.

Use REPEATABLE READ for a transaction that must see one consistent point in time across many statements — a report, a consistency check, a multi-table export. Also note that pg_dump uses it, which is why a dump is a coherent snapshot rather than a smear across the dump’s duration.

Use SERIALIZABLE when a rule spans rows and cannot be expressed as a constraint: at least one on duty, no double-booking, capacity not exceeded. Then make the application retry, because it will be asked to.

What to take from this

Read committed takes a snapshot per statement; the other two take one per transaction.
Repeatable read detects write-write conflicts and raises could not serialize access due to concurrent update.
Write skew commits cleanly under repeatable read and produces broken invariants with no error.
Serializable detects read-write dependencies and raises could not serialize access due to read/write dependencies among transactions.
Both are SQLSTATE 40001. Retry the whole transaction from BEGIN, with backoff and a cap.

Cross-course references

Git, CI/CD & GitOps — Part CVI (Change management) covers getting an isolation-level change reviewed, because it changes application behaviour and belongs in a change record rather than in a configuration drift.
Observability for Production Sysadmins — Part LIX (Database observability) covers alerting on serialization failures, which are a normal cost of SERIALIZABLE and a defect only above a rate.