Transaction Isolation Levels Explained: Dirty Reads, Phantom Reads, and MVCC

A transaction isolation level defines which concurrency anomalies a database permits when transactions run at the same time. The SQL standard names four levels - READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE - ordered from weakest to strongest. Each level trades a degree of correctness for throughput: weaker levels let transactions see more of each other's in-flight work and run with less coordination, stronger levels make concurrent execution behave as if transactions ran one after another. The level you pick decides whether a read can return data that another transaction is about to roll back, or whether a SELECT repeated in the same transaction can return a different answer.

The Read Phenomena the Standard Defines

The SQL-92 standard characterizes isolation levels by three read phenomena they must prevent. A dirty read happens when transaction A reads a row that transaction B has modified but not yet committed - if B rolls back, A acted on data that never existed. A non-repeatable read happens when A reads a row, B updates and commits that row, and A's second read of the same row returns the new value. A phantom read happens when A runs a range query (WHERE salary < 250), B inserts a row matching that predicate and commits, and A's second run of the same query returns an extra row.

Two anomalies the standard does not name matter just as much in practice. A lost update occurs when two transactions read a value, both compute a new value from it, and the second write silently overwrites the first. Write skew occurs when two transactions read an overlapping set of rows, each makes a decision based on what it read, and they write to disjoint rows in a way that violates an invariant neither transaction could see being broken. Write skew is the anomaly snapshot isolation famously fails to catch.

The standard maps levels to phenomena like this:

The levels are not mandates on implementation. The standard only requires that a level prevent at least the phenomena marked "not possible." A database is free to prevent more, and several do.

How PostgreSQL Maps the Four Levels to Three Behaviors

PostgreSQL accepts all four level names in SET TRANSACTION, but internally it implements only three distinct behaviors. READ UNCOMMITTED behaves identically to READ COMMITTED: PostgreSQL never returns uncommitted data, so dirty reads cannot occur at any level (PostgreSQL transaction isolation docs). The default level is READ COMMITTED.

PostgreSQL also makes REPEATABLE READ stronger than the standard requires. Its REPEATABLE READ is snapshot isolation, which prevents phantom reads in addition to non-repeatable reads - the snapshot a transaction takes at its first query hides every later commit. It still allows write skew, the anomaly the standard's table does not cover. SERIALIZABLE adds Serializable Snapshot Isolation (SSI), which detects the dangerous read-write dependencies behind write skew and aborts one transaction to preserve a true serial order.

Set the level explicitly when correctness depends on it:

-- Take a consistent snapshot for the whole transaction
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT sum(balance) FROM accounts WHERE owner_id = 42;
-- every later read in this transaction sees the same snapshot
COMMIT;

-- Full serializability with conflict detection
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- application logic here
COMMIT;  -- may fail with SQLSTATE 40001

MVCC, Pessimistic Locking, and Optimistic Locking

PostgreSQL enforces isolation with Multiversion Concurrency Control (MVCC) rather than read locks. Every row version carries two hidden system columns, xmin (the transaction ID that created the version) and xmax (the transaction ID that deleted or replaced it). When a transaction takes a snapshot, it records which transaction IDs were committed at that moment; a row version is visible to it only if xmin is committed-as-of-the-snapshot and xmax is not. An UPDATE does not overwrite a row in place - it writes a new version and marks the old one's xmax, which is why VACUUM later reclaims the dead versions. Because readers see versioned snapshots, reads never block writes and writes never block reads.

This is the optimistic side of the design. Optimistic concurrency control lets transactions proceed against their snapshot and checks for conflicts at commit time; if a conflict is found, the transaction aborts and the application retries. Pessimistic concurrency control takes the opposite stance - it acquires locks up front (SELECT ... FOR UPDATE, or table locks) so conflicting transactions block and wait rather than abort. PostgreSQL uses both: MVCC handles read consistency optimistically, while explicit row locks and write-write conflicts use pessimistic locking. SERIALIZABLE adds optimistic conflict detection on top, raising serialization_failure (SQLSTATE 40001) when SSI finds a cycle, which means SERIALIZABLE and REPEATABLE READ code must wrap transactions in retry logic.

The classic illustration is the white-and-black marbles write skew. A marbles table holds one black and one white marble. Transaction 1 runs UPDATE marbles SET color = 'white' WHERE color = 'black' while Transaction 2 runs UPDATE marbles SET color = 'black' WHERE color = 'white'. Under any serial order the table ends up all one color. Under snapshot isolation (PostgreSQL REPEATABLE READ) both transactions read the original snapshot, write disjoint rows, and commit - leaving the colors swapped, a result no serial schedule produces. SERIALIZABLE detects the read-write dependency cycle and aborts one transaction.

Diagnosing which isolation anomaly caused a wrong result is hard after the fact, because the offending transactions have committed and the contradictory reads leave no error in the log. Pulse tracks serialization-failure and deadlock rates, long-running transactions that hold back the MVCC horizon, and lock-wait chains, and correlates them to the queries and sessions involved, so a spike in 40001 retries or a write-skew-prone access pattern surfaces as a concrete finding rather than an intermittent data bug you reproduce by hand.

Frequently Asked Questions

Q: What is the default transaction isolation level in PostgreSQL?
A: PostgreSQL defaults to READ COMMITTED. Each statement in a READ COMMITTED transaction sees a fresh snapshot of committed data, so two statements in the same transaction can see different results if another transaction commits in between. Change it per transaction with SET TRANSACTION ISOLATION LEVEL or per session with default_transaction_isolation.

Q: Why does PostgreSQL never allow dirty reads even at READ UNCOMMITTED?
A: PostgreSQL implements READ UNCOMMITTED identically to READ COMMITTED. Its MVCC design only ever exposes committed row versions through a snapshot, so there is no mechanism to read another transaction's uncommitted writes. Requesting READ UNCOMMITTED is accepted for SQL compatibility but gives you READ COMMITTED behavior.

Q: What is the difference between non-repeatable read and phantom read?
A: A non-repeatable read is when a row you already read changes value on a second read because another transaction updated it. A phantom read is when a second run of a range query returns additional or fewer rows because another transaction inserted or deleted rows matching the predicate. Non-repeatable reads concern existing rows; phantoms concern the set of rows a predicate matches.

Q: Does PostgreSQL REPEATABLE READ prevent phantom reads?
A: Yes. PostgreSQL REPEATABLE READ is snapshot isolation, which is stronger than the SQL standard requires - the transaction works against a single snapshot taken at its first query, so inserts and deletes by other transactions are invisible and phantom reads cannot occur. It still permits write skew, which is why SERIALIZABLE exists.

Q: What is write skew and which isolation level prevents it?
A: Write skew happens when two transactions read overlapping data, each decides based on what it read, then writes to disjoint rows in a way that breaks an invariant neither transaction could observe. Snapshot isolation (PostgreSQL REPEATABLE READ) allows it. Only SERIALIZABLE, using Serializable Snapshot Isolation, detects the conflict and aborts one transaction.

Q: Why do I need retry logic with SERIALIZABLE in PostgreSQL?
A: SERIALIZABLE uses optimistic conflict detection. When SSI finds that committing a transaction would violate a serial order, it raises a serialization_failure error with SQLSTATE 40001 instead of producing wrong data. The transaction did not commit, so the application must catch 40001 and re-run the whole transaction. REPEATABLE READ can raise the same error on write-write conflicts.

Q: How does MVCC let readers avoid blocking writers in PostgreSQL?
A: MVCC keeps multiple versions of each row, tagged with the creating and deleting transaction IDs (xmin and xmax). A reader sees only the versions visible to its snapshot and never takes read locks, so writers create new versions in parallel without waiting. The cost is dead row versions that VACUUM must later reclaim.

Related Reading

• How to Fix PostgreSQL Error: Serialization Failure: Why SERIALIZABLE and REPEATABLE READ raise SQLSTATE 40001 and how to retry.
• How to Fix PostgreSQL Error: Could Not Serialize Access Due to Concurrent Update: The write-write conflict variant of serialization failure.
• PostgreSQL VACUUM Explained: How MVCC's dead row versions are reclaimed and why long transactions hold them back.
• How PostgreSQL Indexes Work: B-tree internals behind the scans that range predicates and phantom reads touch.
• How to Fix PostgreSQL Error: Canceling Statement Due to Lock Timeout: When pessimistic locks make transactions wait past the timeout.
• How to Fix PostgreSQL Error: Deadlock Detected: What happens when two transactions wait on each other's locks.