Isolation levels and MVCC

What each isolation level lets a transaction see, shown with recordings from a real server.

Draft. This page is new and has not yet been reviewed by a second person. If something looks wrong, report it.

Before thisTransactions
On this page
  1. MVCC: snapshots instead of read locks
  2. The isolation levels
  3. Read committed: each statement sees the latest commits
  4. Repeatable read: one snapshot for the whole transaction
  5. Serializable: as if one at a time
  6. Choosing
  7. In MySQL
  8. Sources

Isolation decides what a transaction can see of other transactions that run at the same time. Stronger isolation removes more surprises and costs more retries; weaker isolation is faster and leaves more for the application to get right. PostgreSQL implements all of it with MVCC: instead of making readers wait for writers, every transaction reads from a snapshot.

MVCC: snapshots instead of read locks#

As the storage page shows, an update writes a new version of a row and leaves the old one in place. Each version records the transaction that created it (xmin) and the one that replaced it (xmax). A snapshot is simply a note of which transactions had committed when it was taken; a query sees exactly the row versions those transactions produced.

The consequences are what make PostgreSQL pleasant under load:

  • Readers never block writers, and writers never block readers. A long report does not stop updates, and updates do not stop the report.
  • Writers do block writers on the same row: the second one waits for the first to commit or roll back (see locks).
  • Old versions pile up until no snapshot can need them, and then VACUUM removes them. A transaction left open for hours keeps every newer dead version alive.

The isolation levels#

Level in PostgreSQLSnapshot takenDirty readNon-repeatable readPhantom readLost update / write skew
Read uncommitted(behaves as read committed)not possiblepossiblepossiblepossible
Read committed (default)at the start of each statementnot possiblepossiblepossiblepossible
Repeatable readat the first query of the transactionnot possiblenot possiblenot possiblewrite skew possible; a conflicting update is refused
Serializableas repeatable read, plus conflict trackingnot possiblenot possiblenot possiblenot possible: one transaction is aborted

The SQL standard allows phantom reads at repeatable read; PostgreSQL's snapshot prevents them anyway. Higher levels report problems as errors with SQLSTATE 40001 (serialization failure). Code that uses them must retry the whole transaction when it sees one.

Read committed: each statement sees the latest commits#

Recorded on a real server. Session B runs the same query twice in one transaction and gets two different answers, because A committed in between:

Recorded on PostgreSQL 18.3, 2026-10-16

Read committed sees each new commit

  1. A: begin → ok
  2. A: update accounts set balance = 150 where id = 1 → ok
    A changes Asha's balance but has not committed.
  3. B: begin → ok
  4. B: select balance from accounts where id = 1 → 100
    B still sees 100: PostgreSQL never shows another transaction's uncommitted work (no dirty reads), at any isolation level.
  5. A: commit → ok
  6. B: select balance from accounts where id = 1 → 150
    The same query inside the same transaction now returns 150. At read committed, each statement sees everything committed before it started: a non-repeatable read.
  7. B: commit → ok

That is fine for most work, as long as you do not read a value, decide in the application, and write back a result based on it. That is the lost update.

Repeatable read: one snapshot for the whole transaction#

Every query in the transaction sees the database as it was at its first query. Reports that read several tables get a consistent picture. If the transaction tries to update a row that someone else changed after its snapshot, PostgreSQL refuses rather than overwrite it:

Recorded on PostgreSQL 18.3, 2026-10-16

Repeatable read keeps one snapshot

  1. B: begin isolation level repeatable read → ok
  2. B: select balance from accounts where id = 1 → 100
    B's snapshot is taken by its first query: Asha has 100.
  3. A: update accounts set balance = 150 where id = 1 → ok
    A, in autocommit mode, changes it to 150 and commits at once.
  4. B: select balance from accounts where id = 1 → 100
    B still sees 100. Every query in a repeatable read transaction sees the same snapshot.
  5. B: update accounts set balance = balance + 10 where id = 1 → error: could not serialize access due to concurrent update
    B tries to change a row that was changed after its snapshot. PostgreSQL refuses rather than silently overwrite A's change; the application must retry the whole transaction.
  6. B: rollback → ok

Serializable: as if one at a time#

Some anomalies involve no conflicting write at all. Two doctors are on call; the rule is that at least one must stay. Each checks that two are on call and takes themselves off, each updating a different row. At repeatable read both commit and nobody is on call. That is write skew. Serializable isolation tracks what each transaction read and wrote, and aborts one of them when the combination could not have happened one after the other:

Recorded on PostgreSQL 18.3, 2026-10-16

Write skew, caught by serializable

  1. A: begin isolation level serializable → ok
  2. B: begin isolation level serializable → ok
  3. A: select count(*) from doctors where on_call → 2
    The rule: at least one doctor must stay on call. Ana sees two on call, so she may go off call.
  4. B: select count(*) from doctors where on_call → 2
    Raj checks at the same moment and also sees two.
  5. A: update doctors set on_call = false where name = 'Ana' → ok
  6. B: update doctors set on_call = false where name = 'Raj' → ok
    They change different rows, so neither blocks the other. At repeatable read both would commit and nobody would be on call.
  7. A: commit → ok
  8. B: commit → error: could not serialize access due to read/write dependencies among transactions
    Serializable notices that the two transactions could not have run one after the other and still produced this result, and aborts one of them. Raj's transaction must be retried, and on retry it sees only one doctor on call.

Serializable is the easiest level to reason about: if every transaction is correct on its own, they are correct together. The price is more aborted transactions under contention, and a little tracking overhead. Keep serializable transactions short, and declare read-only ones READ ONLY.

Choosing#

  • Read committed for most OLTP work, with the database doing read-modify-write in one statement, or SELECT … FOR UPDATE where it cannot.
  • Repeatable read for reports and exports that must see one consistent state across many queries.
  • Serializable when correctness depends on rules that span rows or tables (balances, bookings, quotas), and you would rather retry than reason about every interleaving.

In MySQL#

Not run here; from the MySQL reference manual. InnoDB's default is repeatable read, not read committed. Plain SELECTs read from a snapshot, but locking reads and UPDATEs see the latest committed version and use next-key (gap) locks to prevent phantoms, so the behaviour differs from PostgreSQL's in detail. InnoDB's serializable level turns plain SELECTs into locking reads instead of using PostgreSQL's conflict tracking.

Sources#

Esc
↑ ↓ to move↵ to open