Isolation Levels

Read uncommitted, read committed, snapshot isolation and serializable: which anomalies each one allows, how MVCC implements them, and why the same name means different things in different databases.

Overview

Why there are levels

Isolation decides what a transaction can see of other transactions running at the same time. Full isolation makes concurrent transactions behave as if they ran one after another. That costs waiting, aborts or both, so databases offer weaker levels that are faster but let some anomalies through.

The SQL standard names four levels and defines them by which anomalies they forbid. Each step up forbids more.

The anomalies

An anomaly is a result that could not happen if the transactions had run one at a time. These are the ones interviewers ask about.

Dirty read — You read a value another transaction wrote but has not committed — You see a payment that is later rolled back
Dirty write — You overwrite another transaction's uncommitted value — Two admins edit a profile; one's name ends up with the other's email
Non-repeatable read (read skew) — Reading the same row twice gives two values — A report sums balances while a transfer runs and the total is off
Lost update — Two read-modify-write cycles run at once and one overwrites the other — Two clicks on 'like' raise the counter by one instead of two
Phantom read — Re-running a search returns rows that were not there before — A second count of today's bookings includes a new one
Write skew — Two transactions read the same data, then update different rows, and together break a rule — Two withdrawals from a pair of linked accounts each see enough in total, and together overdraw it

The standard's table names only three of these: dirty read, non-repeatable read and phantom read. Berenson and colleagues showed in 1995 that its definitions miss others, such as dirty write, lost update and write skew, and that snapshot isolation does not fit its scheme. The PostgreSQL documentation summarizes the standard like this.

Read uncommitted — Allowed (not in PostgreSQL) — Possible — Possible — Possible
Read committed — Not possible — Possible — Possible — Possible
Repeatable read — Not possible — Not possible — Allowed (not in PostgreSQL) — Possible
Serializable — Not possible — Not possible — Not possible — Not possible

Read committed

Read committed makes two promises: you only read committed data, and you only overwrite committed data. Writers take a row lock and hold it until they commit, so a second writer to the same row waits. It is PostgreSQL's default.

In PostgreSQL each statement gets a fresh snapshot of committed data. Two statements in the same transaction can therefore see different data.

That is fine for most single-statement work. It is wrong for backups, reports and checks that read several rows and expect them to agree.

Snapshot isolation and MVCC

Snapshot isolation gives the whole transaction one consistent view: the data as committed when it took its snapshot. Other transactions' later commits stay invisible to it. PostgreSQL implements its repeatable read level this way, and its docs name the technique snapshot isolation.

The usual implementation is multi-version concurrency control (MVCC). An update does not overwrite a row; it writes a new version and marks the old one as ended. In PostgreSQL every row version carries xmin (the transaction that created it) and xmax (the transaction that deleted or replaced it, zero if none). A reader uses its snapshot to pick the one version it may see.

Readers do not take locks that block writers — Reading never blocks writing and writing never blocks reading
Old versions stay until no snapshot needs them — VACUUM must clean them up
A long-running transaction keeps an old snapshot alive — Dead versions pile up and tables bloat
Writers still lock the rows they change — Two writers to one row still wait for each other

Snapshot isolation also blocks many lost updates. In PostgreSQL, if a repeatable read transaction tries to update a row that another transaction changed and committed after its snapshot, it fails with 'could not serialize access due to concurrent update' (SQLSTATE 40001). The application retries the whole transaction.

What snapshot isolation does not stop is write skew: two transactions that read overlapping data and then write different rows. Neither touches a row the other wrote, so nothing conflicts. That needs serializable isolation or explicit locks (see Write Skew and Phantom Reads).

Same name, different behavior

The names of levels are not portable. Check the product's own docs before you rely on one.

PostgreSQL read uncommitted — Behaves exactly like read committed
PostgreSQL repeatable read — Snapshot isolation; no phantoms in reads; write skew possible
PostgreSQL serializable — Serializable snapshot isolation (SSI), true serializability since 9.1
MySQL InnoDB repeatable read (default) — Plain reads use the snapshot from the first read; locking reads and writes see the latest data and lock index ranges
MySQL InnoDB serializable — Like repeatable read, but plain SELECTs become SELECT ... FOR SHARE when autocommit is off
Oracle serializable — Snapshot isolation, not full serializability

Setting a level is one line. The block below runs on PostgreSQL.

repeatable read

Serializable

Serializable is the only level that promises the result of some serial order. Databases reach it in three ways: two-phase locking, serializable snapshot isolation, or running transactions one at a time. The costs differ a lot; see Implementing Serializability.

What you will be able to do

Name each anomaly and give a one-line example of it.
Say which anomalies each level allows, and which one is the default in PostgreSQL and MySQL.
Explain how MVCC gives each transaction a snapshot, and why long transactions cause bloat.
Explain why snapshot isolation is not serializable.
Read a vendor's level name with suspicion and check what it really does.

Key concepts

Dirty read: Reading data another transaction wrote but has not committed. Read committed and stronger levels forbid it.
Non-repeatable read: Reading the same row twice in one transaction and getting different values, because another transaction committed in between. Also called read skew.
Lost update: Two transactions read a value, change it and write it back; the second write silently erases the first.
Phantom read: Re-running a search in one transaction returns a different set of rows because another transaction inserted or deleted matching rows.
Write skew: Two transactions read overlapping data, write different rows, and together break a rule that each checked on its own.
Snapshot isolation: Each transaction reads from one consistent snapshot of committed data. Stops dirty and non-repeatable reads, but not write skew.
MVCC: Multi-version concurrency control: updates write new row versions, and each reader picks the version its snapshot allows, so reads and writes do not block each other.
Serialization failure: The error a database raises when it aborts a transaction to protect isolation. PostgreSQL uses SQLSTATE 40001; the client must retry the whole transaction.

When to use it

Choosing an isolation level for code that reads several rows and then decides what to write.
Explaining why a report or backup needs one consistent snapshot.
Explaining why snapshot isolation still allows write skew.
Moving an application between PostgreSQL, MySQL and Oracle, where the same level name behaves differently.

Interview questions

What is the difference between snapshot isolation and serializable isolation?
How does MVCC work in PostgreSQL?
Which isolation level would you choose for a banking application?
Why does repeatable read mean different things in MySQL and PostgreSQL?
How do you prevent lost updates?