Isolation Levels: What Each One Still Lets Through

Postgres has four isolation level names and three behaviours — READ UNCOMMITTED is treated as READ COMMITTED. The level you pick does not decide whether two transactions collide. It decides what happens when they do: READ COMMITTED silently re-points your UPDATE at the row version the other session left behind, REPEATABLE READ raises an error instead, and SERIALIZABLE additionally aborts transactions that never touched the same row at all.

An isolation level is two rules. The first is when a statement takes its snapshot — the list of transactions whose work counts as committed for the purposes of this read. The second is what the executor does when a row it wanted to write has already been written by a transaction that committed after that snapshot was taken. Everything else in this lesson follows from those two rules.

The SQL standard names three read anomalies and one broader failure. A dirty read is seeing a row version written by a transaction that has not committed. A non-repeatable read is reading a row twice in one transaction and getting two different values because someone else committed in between. A phantom read is running the same WHERE clause twice and getting a different set of rows. A serialization anomaly is the general case: the committed result is one that no serial order of those transactions could have produced, even though every individual read was consistent.

Postgres never permits a dirty read at any level, because a version stamped with an in-progress transaction id — the XID, the 32-bit number Postgres assigns each writing transaction — simply fails the visibility test. Its REPEATABLE READ also excludes phantom reads, which is stronger than the standard requires. What it still permits, and what the documentation's own table calls a serialization anomaly, is the thing that breaks production invariants: write skew. Two transactions read the same condition, each writes a different row, neither ever sees a conflict, and the condition they both checked is false by the time they have both committed.

The panel below is a working multiversion concurrency control engine — MVCC, the scheme where an UPDATE writes a new row version rather than overwriting the old one, described in MVCC and vacuum. Two sessions sit side by side. You choose which one runs its next statement, so the interleaving is yours. Start with the preset schedule, then change the isolation levels underneath the same schedule and watch the outcome change. Read the decision log: it states, for every statement, which row version was chosen and why the others were rejected.

With the default schedule — non-repeatable read, both sessions at READ COMMITTED — press Play the classic interleaving. T1 reads a balance of 100, T2 changes it to 150 and commits, T1 reads the same row again and gets 150. One transaction, two answers. Now set T1 to REPEATABLE READ without touching the schedule. The same statements in the same order return 100 both times, because T1's snapshot was frozen at its first statement and T2's XID is above the snapshot's horizon forever.

Then switch to the write skew scenario and put both sessions at REPEATABLE READ. Each transaction checks that two doctors are on call, each takes itself off call, both commit, and the number of doctors on call is zero. No error, no conflict, no row written twice. Only SERIALIZABLE on both sessions turns that into an error — set just one of them to SERIALIZABLE and the invariant breaks exactly as before.

What READ COMMITTED actually promises

It promises exactly one thing: no statement ever sees a row version written by a transaction that had not committed when that statement began. That is the whole guarantee. It says nothing about two statements agreeing with each other, and — this is the part that surprises people — it says nothing about a single statement seeing a consistent picture either.

Run the DELETE that deletes nothing scenario with both sessions at READ COMMITTED and press the preset. T1 adds 1 to every balance. T2 runs DELETE FROM accounts WHERE balance = 101 and deletes zero rows, even though a row with a balance of 101 exists both before T2 starts and after it finishes. The decision log shows why, and it is not a bug — it is the documented rule:

"If the first updater commits, the second updater will ignore the row if the first updater deleted it, otherwise it will attempt to apply its operation to the updated version of the row. The search condition of the command (the WHERE clause) is re-evaluated to see if the updated version of the row still matches the search condition."

T2's snapshot found bob at 101. T2 blocked on the row lock. When T1 committed, bob was 102, the re-checked WHERE no longer matched, and the row was dropped from the statement. Meanwhile alice had become 101 — but alice was 100 in T2's snapshot, so she was never a candidate and no re-check applies to her. The statement saw one version of the world for finding rows and another for writing them.

That same re-check is what makes the two lost update scenarios end differently at the identical isolation level. In the first, both sessions read 100 and issue SET balance = 110. The second one waits, the re-check passes, and 110 is written over 110: the final balance is 110 and one increment is gone. In the second, both issue SET balance = balance + 10. The re-check re-points the statement at the winner's version, the arithmetic runs against that version, and the balance ends at 120. Nothing about the isolation level changed. The difference is whether the value being written was computed inside the statement or carried in from a read that is now stale.

So the rule for READ COMMITTED is narrow and worth memorising. A read-modify-write cycle that passes through your application — read a row, compute in Python, write it back — is not safe at this level. Doing the arithmetic in SQL is safe for that one row. Anything that reads several rows and writes a conclusion drawn from them is not safe at all, because the rows were read under different snapshots than the one you write under.

REPEATABLE READ is snapshot isolation with a different name

One snapshot is taken at the start of the transaction's first query or data-modifying statement — not at BEGIN — and every statement after that uses it. Nothing another transaction commits can enter your view for the rest of your transaction. That is what removes non-repeatable reads, and in Postgres it also removes phantom reads, because a snapshot has no concept of "new row" versus "changed row": a version whose xmin is above your horizon is invisible either way. In the documentation's anomaly table, the phantom-read cell for this level reads "Allowed, but not in PG".

The cost is that writes can now fail. A transaction at this level may not modify a row whose current version it cannot see, so any collision with a concurrent committed update ends in:

ERROR:  could not serialize access due to concurrent update
SQLSTATE 40001

Set both sessions to REPEATABLE READ in either lost-update scenario and you get exactly that: T2 waits, T1 commits, T2 is rolled back. The failure mode has moved from silent corruption to a loud error, which is a strictly better place for it to live — but only if the application retries. Postgres will not retry for you and says so directly: it "does not offer an automatic retry facility, since it cannot do so with any guarantee of correctness." The retry must re-run the reads too, because the values they returned are exactly what turned out to be stale.

And now the part people are not told. Switch to write skew, set both sessions to REPEATABLE READ, and press the preset. Both transactions count two doctors on call. Each takes a different doctor off call. There is no row they both wrote, so there is no conflict to detect. Both commit. Zero doctors are on call, and the rule both of them checked is now false.

Every ingredient here is normal application code: a SELECT that validates a rule, an UPDATE that acts on it. The reason it fails is structural. Under snapshot isolation a transaction that reads data written by a concurrent transaction sees the older state, so it "appears to have executed first" regardless of the real order. When two transactions each appear to have gone first, there is no serial order consistent with both, and any invariant that spans more than the rows you wrote can be violated without either transaction noticing. The stable snapshot is what makes this possible: it guarantees your view is consistent, never that it is current.

One operational side effect belongs here too. A REPEATABLE READ transaction registers its snapshot for its entire lifetime, so a session that opens one and goes idle holds the oldest visible transaction id back for the whole cluster. That is the scenario in MVCC and vacuum where dead rows become unremovable, and if it runs long enough it turns into transaction ID wraparound. Under READ COMMITTED, a read-only session that goes idle releases its snapshot at the end of each statement and does no such damage.

What SERIALIZABLE adds, and what it costs

SERIALIZABLE is REPEATABLE READ plus one thing: a monitor. It uses the same snapshots and takes no extra locks that block anybody. What it adds is Serializable Snapshot Isolation (SSI), which records what each transaction read and watches for the pattern that makes write skew possible.

The record it keeps is a predicate lock — an entry describing the rows a query examined, which shows up in pg_locks with mode SIReadLock. It is not a lock in the blocking sense. The documentation is explicit: these locks "do not cause any blocking and therefore can not play any part in causing a deadlock." They exist so the server can answer one question later: if this other transaction's write had happened first, would that earlier read have returned something different?

When the answer is yes, Postgres records a read/write dependency: the reader must be ordered before the writer in any equivalent serial run. Run the write-skew scenario with both sessions at SERIALIZABLE and the log names both of them — T1 read the row T2 wrote, and T2 read the row T1 wrote. Each must go first. That is a cycle, and the second transaction to commit is rolled back to break it:

ERROR:  could not serialize access due to read/write dependencies among transactions
HINT:  The transaction might succeed if retried.
SQLSTATE 40001

Notice what the simulation's SIRead locks held readout does when you switch a session to SERIALIZABLE: predicate locks appear on the rows a SELECT merely looked at. That is the difference between the two errors. "Concurrent update" is about a row you tried to write. "Read/write dependencies" is about a row you only read — a transaction can be aborted for a conflict on data it never modified, which is the single most surprising thing about this level.

Now the boundary that matters more than any of it. Set T1 to SERIALIZABLE and leave T2 at READ COMMITTED, and run write skew again. Both commit. Nobody is on call. The guarantee is not a property of your session — it is a property of the set of transactions, and it holds only for transactions that are all running at SERIALIZABLE. One legacy job, one ORM that opens connections with the default level, one psql session run by hand, and the protection is gone for the rows they touch. This is why the documentation recommends setting default_transaction_isolation = serializable cluster-wide, and even checking the level in a trigger, rather than setting it per transaction and hoping.

Three more edges are worth knowing before you turn it on:

• It does not extend to replicas. The documentation carries an explicit warning: this protection "does not yet extend to hot standby mode … or logical replicas", so a read on a standby is not covered even if it asks for the level. If you rely on it, run those reads on the primary.
• It still allows errors a serial run would not produce. A check-then-insert can raise a unique-constraint violation (SQLSTATE 23505) rather than a serialization failure, because the server cannot always connect the insert to the earlier read. Retry logic that only catches 40001 will miss it.
• False positives are a tuning parameter. Predicate locks are promoted from tuple to page to relation granularity when the lock table fills, and a promoted lock covers rows the query never read, so unrelated transactions start conflicting. The relevant defaults are max_pred_locks_per_transaction at 64, max_pred_locks_per_page at 2 — only two rows per page get individual locks before the whole page is locked — and max_pred_locks_per_relation at −2, meaning max_pred_locks_per_transaction ÷ 2 pages before the entire relation is locked. A sequential scan always takes a relation-level predicate lock, so a missing index turns into a serialization-failure storm.

This is the opposite trade from Delta Lake's optimistic concurrency, and the contrast is clarifying. Delta lets both writers work, compares the files each one touched at commit time, and fails the loser with ConcurrentAppendException at file granularity — coarse, but it needs no bookkeeping during the transaction. Postgres tracks conflicts as they happen, at row and predicate granularity, and can therefore tell you that two transactions conflicted over a row neither of them wrote. Both systems end at the same place: the writer must be prepared to run again.

Choosing, and checking it on a real system

The decision is not "how much safety do I want". It is "what does this transaction assume, and does anything else write that data":

• Single-row read-modify-write, arithmetic expressible in SQL: READ COMMITTED is correct. Write SET n = n + 1, never SET n = 42 computed from an earlier read.
• Read a value in the application, decide, write it back: READ COMMITTED is wrong. Use SELECT … FOR UPDATE to take the row lock at read time — see explicit row locks — or move up a level and retry.
• A report or export that must be internally consistent: REPEATABLE READ, and keep it short. Read-only transactions never raise serialization failures.
• A rule that spans rows the transaction does not write — balance non-negative, at least one on call, no overlapping bookings: SERIALIZABLE for every transaction that touches those tables, or an explicit lock that covers the whole predicate. Nothing else works.

To watch it happen for yourself, open two psql sessions side by side and run this — it is the write-skew scenario, and it commits cleanly at the level most applications run at:

-- setup, once
CREATE TABLE oncall (id int primary key, doctor text, on_call boolean);
INSERT INTO oncall VALUES (1,'alice',true), (2,'bob',true);

-- session 1                         -- session 2
BEGIN ISOLATION LEVEL REPEATABLE READ;
                                     BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM oncall
  WHERE on_call;          -- 2
                                     SELECT count(*) FROM oncall
                                       WHERE on_call;          -- 2
UPDATE oncall SET on_call = false
  WHERE id = 1;
                                     UPDATE oncall SET on_call = false
                                       WHERE id = 2;
COMMIT;
                                     COMMIT;                   -- succeeds

SELECT count(*) FROM oncall WHERE on_call;   -- 0

Re-run it with ISOLATION LEVEL SERIALIZABLE on both and the second COMMIT fails with 40001. Re-run it with SERIALIZABLE on one side only and it succeeds again, leaving nobody on call — that third run is the one to show a colleague who believes setting the level on the important transaction is enough.

Four things to check in a live system, in the order you need them:

-- 1. what level is this session actually getting?
SHOW default_transaction_isolation;
SELECT current_setting('transaction_isolation');

-- 2. who is blocked on a row lock right now, and behind whom
SELECT pid, state, wait_event_type, wait_event,
       pg_blocking_pids(pid) AS blocked_by,
       now() - xact_start AS open_for, left(query, 60) AS query
FROM pg_stat_activity
WHERE backend_type = 'client backend' AND state <> 'idle';

-- 3. predicate locks, i.e. what SSI is currently tracking
SELECT locktype, relation::regclass, page, tuple, pid
FROM pg_locks WHERE mode = 'SIReadLock' ORDER BY pid;

-- 4. rollbacks over time, the cheap serialization-failure proxy
SELECT datname, xact_commit, xact_rollback,
       round(100.0 * xact_rollback / nullif(xact_commit + xact_rollback, 0), 2) AS pct_rollback
FROM pg_stat_database WHERE datname = current_database();

Query 2 is the one that ends a "the database is hanging" incident: wait_event_type = 'Lock' with wait_event = 'transactionid' means a writer is waiting for another transaction to end, which is the ordinary row-lock wait the simulation shows — identical at every isolation level, and unrelated to SSI. pg_blocking_pids() names the session to talk to. If the wait resolves into a deadlock detected error, that is SQLSTATE 40P01 after deadlock_timeout (default 1 second) — see deadlocks — and it is retryable for the same reason 40001 is.

Query 3 returning rows tells you SSI is doing work; a locktype of relation rather than tuple or page tells you a lock has been promoted and your serialization-failure rate is about to include transactions that never overlapped. Query 4 has no dedicated counter for 40001, so the rollback ratio plus a log grep is what people actually use: set log_min_error_statement = error and count occurrences of could not serialize access, split by which of the two messages it is. "Concurrent update" means contention on specific hot rows. "Read/write dependencies" means your transactions are reading wider than they write, and the fix is usually a narrower SELECT or an index that turns a sequential scan into an index scan.

Finally, whatever level you choose, wrap writes in a retry loop that re-executes the transaction from the first statement — not just the failed statement — on SQLSTATE 40001 and 40P01, with a small randomised backoff and a cap. The documentation is blunt that retries may be needed more than once under contention. A retry that reuses values read during the failed attempt reintroduces exactly the anomaly the abort prevented; see writing a correct retry loop.

Two directions from here. The mechanism underneath every level is which row version a snapshot can see, and the versions the losers leave behind are what VACUUM exists to clean up. The alternative to raising the level — taking the lock yourself, at read time, so there is no conflict to detect later — is SELECT FOR UPDATE.