Isolation Levels: The Table That Does Not Describe Your Database

Almost every database conversation about isolation starts from a four-row table. That table is the ANSI SQL definition, and it is genuinely useful, and mapping it onto a real engine reliably produces a discussion where both people are correct and nobody learns anything.

The problem is that the table is organised around symptoms. It says which anomalies are prevented and says nothing about what the engine does, so you cannot reason from the table to a plan.

Once you reason from the mechanism instead, most anomalies stop being a settings question. A predicate lock is a lock, and locks are the subject of deadlocks and lock ordering. A stale read is a visibility question, and visibility is what the primary and the replica disagree about before the replica catches up: replica lag and read-your-writes.

The table, and why it misleads

                 dirty read   non-repeatable read   phantom read
  ------------------------------------------------
  read uncommitted  possible   possible              possible
  read committed    no         possible              possible
  repeatable read   no         no                    possible
  serializable      no         no                    no

This is the SQL-standard phenomenon table. PostgreSQL’s implementation is stronger in two places: Read Uncommitted behaves as Read Committed, and PostgreSQL Repeatable Read also prevents phantom reads while still allowing serialization anomalies such as write skew.

Read as a specification this is fine. Read as a description of a running system it fails immediately, because the entries are defined as “the database must not allow this anomaly”, and there are far more anomalies than the four named ones.

The full list of anomalies runs past a dozen and includes write skew, read skew, lost update, and the serialization anomalies from Adya’s formal treatment. Any engine that claims serializable isolation is claiming all of them are impossible, not the three in the table. The table is a subset that happened to be the interesting ones in 1992.

The second problem is that the table implies a mechanism. It invites the reading that Read Committed means shared locks, Repeatable Read means shared locks held to commit, and Serializable means exclusive locks everywhere. Engines implement these guarantees with different combinations of MVCC, locking, validation, and dependency tracking, so the table alone cannot predict blocking or abort behavior.

MVCC changed the question

The anomalies in the table mostly come from one problem: a reader can observe a write that is not yet committed. Blocking that observation is what locks are for, and a reader who blocks a writer is a throughput problem.

MVCC changes the ordinary row-read path. Instead of one version of each row, there are several, tagged with creating and deleting transactions. A plain reader uses a snapshot and normally does not block a row writer; the writer normally does not block that plain reader. SELECT ... FOR UPDATE, conflicting writes, explicit table locks, and DDL are outside that simple statement.

  row 88213

    xmin 501  xmax 604   committed by T501, replaced by T604   <- old version
    xmin 604  xmax 0     committed by T604, current            <- new version

  reader with snapshot at xmin 500  sees  neither, row did not exist yet
  reader with snapshot at xmin 550  sees  the T501 version
  reader with snapshot at xmin 610  sees  the T604 version

Dirty reads disappear from PostgreSQL’s offered behavior. A version written by an uncommitted transaction is not visible to another transaction’s ordinary snapshot.

So Postgres offers three isolation levels rather than four, and the mapping is not intuitive:

Level	Behaviour in Postgres
Read Uncommitted	Not a distinct level. A read is a snapshot, and it cannot see uncommitted data, so it behaves as Read Committed.
Read Committed	A new snapshot per statement. Default.
Repeatable Read	A new snapshot per transaction.
Serializable	Repeatable Read plus conflict detection, aborting one transaction when a cycle is detected.

Do not transfer this table mechanically to another engine. InnoDB uses next-key locking in relevant modes, SQL Server offers both locking and row-versioning behaviors, CockroachDB defaults to Serializable but also supports configurable Read Committed, and FoundationDB defaults to strict serializability with optimistic conflict detection. The label is the beginning of the documentation lookup, not the end.

Read Committed surprises in two ways

The default level has a behaviour that catches everyone once, and it is not the one the table predicts.

A transaction sees a different world in each statement. The snapshot is taken per statement, so this is legal and correct:

BEGIN;

SELECT total_cents FROM orders WHERE id = 88213;   -- 4,900
-- another transaction commits: total is now 5,900
SELECT total_cents FROM orders WHERE id = 88213;   -- 5,900, same transaction

COMMIT;

Application code that reads the same row twice inside a transaction and compares the values is relying on something Read Committed does not provide. This is a very common source of a check-then-act bug that only reproduces under concurrency.

An UPDATE re-checks its predicate after a conflict. This one is subtler and it is the mechanism behind most lost-update arguments.

-- T1 and T2 both run, concurrently, at Read Committed

T1: UPDATE orders SET status = 'paid' WHERE id = 88213 AND status = 'pending';
T2: UPDATE orders SET status = 'cancelled' WHERE id = 88213 AND status = 'pending';

T2 finds the row, sees pending, and tries to lock it. T1 holds the lock. T2 waits. T1 commits. T2 wakes up, and re-evaluates the WHERE status = 'pending' against the row as it now exists.

  T1 acquires the lock, sets status = 'paid', commits
  T2 was blocked, re-evaluates: status is now 'paid', not 'pending'
  T2's WHERE no longer matches, so T2 updates 0 rows

That behaviour is correct and intentional, and it is what makes this a conditional update rather than a lost update. It is also invisible unless you look at the row count, because the statement reports success either way. An application that treats a successful UPDATE as a changed row is making a mistake, and the fix is to check the affected count.

Write skew is the one the table hides

Repeatable Read gives every transaction a stable snapshot for its whole life. The obvious conclusion is that transactions see a consistent world and therefore cannot interfere. That conclusion is wrong, and the failure has a name.

  shifts table, one night shift, one slot free

  T1: BEGIN ISOLATION LEVEL REPEATABLE READ;
  T2: BEGIN ISOLATION LEVEL REPEATABLE READ;

  T1: SELECT count(*) FROM shifts WHERE slot = 'night' AND assigned = false;
      -> 1
  T1: UPDATE shifts SET assigned = true WHERE id = 101;

  T2: SELECT count(*) FROM shifts WHERE slot = 'night' AND assigned = false;
      -> 1        (T1 has not committed, T2 cannot see it)
  T2: UPDATE shifts SET assigned = true WHERE id = 102;

  T1: COMMIT
  T2: COMMIT

  final state: two people assigned to a shift with one place
  neither transaction violated anything it checked

Both transactions read a correct snapshot. Both took an action that was correct given that snapshot. The combination violates a constraint, and no lock prevented it, because each transaction only wrote a row the other never read.

This is write skew, and the same shape appears in every system built on Repeatable Read:

• two users confirming the last seat on a flight
• two doctors taking the last on-call slot
• two workers claiming the last unit of inventory
• two nodes believing they are the leader because both read the same stale heartbeat

The distinguishing property is always the same: each transaction reads a set of rows, and writes a different set, and the invariant spans both. If the two sets overlap, a lock or a predicate check catches it. If they do not overlap, Repeatable Read does not help at all.

Serializable adds dependency detection to ordinary locking

Postgres implements Serializable on top of Repeatable Read using Serializable Snapshot Isolation. The approach is worth understanding because it inverts the usual expectation.

PostgreSQL still uses ordinary row and table locks for writes. SSI adds non-blocking SIReadLock predicate-lock information and dependency tracking. It permits concurrent work to proceed, but it does not permit a non-serializable outcome to commit: a transaction is aborted when PostgreSQL detects a dangerous dependency structure.

  T1 reads row A, writes row B     ->  rw-conflict on B
  T2 reads row B, writes row A     ->  rw-conflict on A
                                     dangerous structure: a cycle

  SSI tracks read and write dependencies, and when it finds a
  cycle with an ordering that cannot be serialised, it aborts one
  of the participants:

    ERROR: could not serialize access due to read/write
           dependencies among transactions

The predicate-lock representation can be at tuple, page, or relation granularity depending on plan and available predicate-lock memory. These SIReadLock entries do not block writers. PostgreSQL uses them with write information to track read-write dependencies; aborts can occur before or during commit, and false positives are allowed to preserve correctness.

Two practical consequences follow, and they change how you write application code.

The error is an expected result that must be handled. A serialization failure is the database declining an execution it cannot safely serialize. Roll back the complete transaction. Retry when the operation is still wanted and safe to repeat; otherwise return a domain outcome. Bound attempts, pass cancellation, and keep non-transactional side effects outside the retry body.

const int MaxAttempts = 5;

for (var attempt = 1; ; attempt++)
{
    try
    {
        await using var tx = await conn.BeginTransactionAsync(
            System.Data.IsolationLevel.Serializable);

        var remaining = await ReadFreeSlotsAsync(conn, tx);
        if (remaining == 0) throw new SoldOutException();

        await ClaimSlotAsync(conn, tx, remaining[0]);
        await tx.CommitAsync();
        return;
    }
    catch (PostgresException ex) when (ex.SqlState == "40001" && attempt < MaxAttempts)
    {
        // 40001 is serialization_failure. Retrying is the designed path.
        await Task.Delay(Random.Shared.Next(2, 40) * attempt);
    }
}

Read Committed is a real choice, not a compromise to apologise for. A reporting query, a dashboard refresh, and a search request gain nothing from a transaction-wide snapshot, and on a busy system the higher abort rate of Serializable is pure cost. The level should be chosen per operation rather than set once for the connection.

What a phantom is, at the index level

The table lists phantoms as the hardest anomaly to prevent, and it is worth being precise about why, because the reason is an implementation detail that also explains the fix.

A phantom is a row that appears or disappears inside a range a transaction has already read. At the index level, a range scan reads a sequence of index entries pointing at heap pages.

  predicate:  WHERE slot = 'night' AND assigned = false

  scan reads index entries:  101  -> heap page 4
                             102  -> heap page 9

  a concurrent transaction inserts 103
     -> a new index entry, and a new heap page

  a re-scan now finds 103, which was not in the first result

Nothing in the first scan marked those pages, because the transaction never intended to block the insertion. A conventional B-tree index cannot know that a range scan is about to happen. It only finds out when a write inserts into a region the reader has already passed.

PostgreSQL’s Serializable mode does not block the index gap. It records the predicate read and detects read-write dependency patterns when another transaction changes the relevant range. Other MVCC engines can choose differently: InnoDB, for example, uses next-key and gap locking to prevent relevant phantoms under locking reads.

Under PostgreSQL Serializable, a transaction may observe its own snapshot while a concurrent insert proceeds, but the combination is not allowed to produce a non-serializable committed history. The trade is abort-and-retry pressure rather than blocking predicate ranges. Neither failure mode is universally better; workload contention and application retry cost decide.

A production-ready architecture

  operation
     |
     +-- read only, no cross-row invariant
     |      READ COMMITTED, no transaction
     |      snapshot per statement, no aborts, cheapest
     |
     +-- read-modify-write on rows the query also reads
     |      REPEATABLE READ
     |      stable snapshot, still needs retry on 40001
     |
     +-- invariant spans rows a transaction reads and others it writes
            SERIALIZABLE
            detector aborts one side, application retries

A delivery checklist:

1. Choose the level per operation, not per connection. A dashboard refresh and a booking do not need the same answer.
2. Handle 40001 and 40P01 at the complete transaction boundary. Retry only safe operations, with jitter, cancellation, and one total deadline.
3. Bound the retry loop. Three to five attempts with jitter is enough; beyond that the operation is contended rather than unlucky.
4. Check the affected row count on every conditional UPDATE. A successful statement that matched nothing is not a success.
5. Stop reading the same row twice in a transaction and expecting the same value at Read Committed.
6. Identify write skew by asking whether a transaction’s read set and write set overlap another transaction’s. If they do not overlap, Repeatable Read will not save you.
7. Fix write skew with a constraint where you can, because a constraint is checked under a lock and no isolation level is involved.
8. Do not reach for Serializable to solve a write skew you could express as a unique constraint. It is much more expensive and much harder to test.
9. Make sure a serialization failure becomes an intentional retry or domain conflict, not an unexplained 500.
10. Measure the abort rate after enabling Serializable. A high rate means the invariant should be a constraint, or the transactions are too long.

Failure stories worth testing

Run the two-transaction write skew under Repeatable Read and commit both

The shift example above, in a test, with the transactions genuinely concurrent. If both commit and you end up with two rows assigned to one slot, you have reproduced write skew and you can prove the isolation level is not protecting you.

Do the same test under Serializable and catch the 40001

One of the two transactions should fail with 40001. Confirm the application reruns the complete decision: the retry may now return “slot unavailable” rather than commit. The invariant—not forced success of the retried request—is the assertion.

Read the same row twice in one Read Committed transaction while another session updates it

The two reads return different values from the same transaction. Nothing is broken, and it is exactly the assumption that produces check-then-act bugs in application code.

Run a conditional UPDATE from two sessions and count affected rows on the loser

The blocked transaction re-evaluates its predicate and reports zero rows affected. Confirm the application treats that as a conflict rather than a success, because most conditional-update bugs are here.

Increase predicate-lock pressure in a controlled test

Run the serializable workload with the production query plans and concurrency while observing pg_locks for SIReadLock entries and counting 40001 by operation. Plan changes can alter tuple/page/relation predicate-lock granularity, so validate after index and query changes rather than manufacturing aborts with an unrelated statement timeout.

Common mistakes

Mistake	What actually happens	Better decision
Reading the ANSI table as a description of a running engine	The mapping to mechanism is wrong, so every plan built on it is a guess	Ask what the engine does, not what the table names
Expecting Read Uncommitted to show uncommitted rows	A snapshot read cannot see them, so it is just Read Committed	Do not design around a level that does not exist
Assuming one snapshot per transaction is the default	Read Committed takes one per statement	Choose the level deliberately per operation
Assuming Repeatable Read prevents concurrency anomalies	Write skew slips through because read and write sets do not overlap	Serializable, or a constraint
Believing a successful UPDATE means a row changed	A blocked statement re-checks its predicate and matches nothing	Check the affected row count
Treating a 40001 as an application defect	It is an expected possible result of Serializable execution	Roll back the whole transaction and retry only when safe
Retrying a serialisation failure in a loop with no cap	Contention converts into a retry storm	Cap at a few attempts with backoff
Using Serializable to fix a write skew a unique index can prevent	Higher abort rate, more complex retries, weaker testability	Express the invariant as a constraint
Splitting reads across services to avoid a lock	Atomicity across the boundary is gone entirely	Design the boundary so the invariant lives on one side
Assuming every MVCC engine handles phantom ranges alike	PostgreSQL SSI detects dependencies; InnoDB can use next-key locks	Read the engine and access-mode semantics
Never measuring the abort rate	A high abort rate is invisible until it is an incident	Track 40001 by operation

The complete story in one minute

The isolation table names phenomena and says nothing about mechanism, so mapping it onto an engine produces a specification rather than a blocking or retry plan. PostgreSQL’s ordinary MVCC reads use snapshots and normally do not block row writers, with explicit locking and DDL as important exceptions. Read Uncommitted behaves as Read Committed; Read Committed takes a snapshot per statement; Repeatable Read uses a transaction snapshot and is stronger than the SQL-standard minimum because it also prevents phantom reads.

Write skew survives PostgreSQL Repeatable Read because each transaction can write a different row after reading a shared invariant from one stable snapshot. PostgreSQL Serializable adds SIReadLock predicate information and read-write dependency tracking; it allows concurrency to proceed but aborts a participant before a non-serializable history commits. The application must treat 40001 as an expected possible outcome and rerun the whole decision only when doing so remains safe.

Practically that makes the level a per-operation decision. A one-statement dashboard read often needs no explicit multi-statement transaction beyond the statement’s own transaction. A booking that reads availability and writes a reservation wants either a constraint that makes the invariant unbreakable or Serializable with a retry loop. And a conditional update has to check its affected row count, because a blocked statement can re-evaluate its predicate against the row another transaction changed and legitimately affect zero rows without throwing.

Technical references

• PostgreSQL transaction isolation
• PostgreSQL Serializable Snapshot Isolation
• PostgreSQL predicate-lock monitoring
• PostgreSQL retry guidance for serialization failures
• CockroachDB transaction isolation
• FoundationDB transaction guarantees
• InnoDB next-key locking and phantom prevention