Lesson 15 · Inside a database

Isolation levels

When two transactions run at the same time, the isolation level decides how much each one sees of the other's work. Follow one worked example in PostgreSQL: a report (T1) and a transfer (T2) share the acct table. PostgreSQL keeps several versions of every row (MVCC) and gives each transaction a snapshot. Watch Read Committed allow a nonrepeatable read, Repeatable Read freeze one snapshot and refuse a stale write, and Serializable stop a write skew with error 40001.

14 min11 steps5 questionsAdvanced

Fig 15 · Interactive

Loading figure…

or ← →

Check yourself

5 quick questions

Pick an answer to see why. Your score saves with your progress.

01Row 1 holds 100. T2 runs UPDATE acct SET bal = 70 WHERE id = 1 but has not committed. What is in the table now, and what does T1 (Read Committed) read?

With MVCC an UPDATE adds a new row version instead of overwriting the old one. Uncommitted work is never visible to other transactions in PostgreSQL, at any level, so T1 reads the committed version, 100, without waiting.

02A transaction runs BEGIN ISOLATION LEVEL READ UNCOMMITTED in PostgreSQL. What does it get?

The SQL standard names four levels. PostgreSQL accepts all four names but runs Read Uncommitted exactly like Read Committed, so dirty reads never happen. Its three distinct levels are Read Committed, Repeatable Read and Serializable.

03In Repeatable Read, T1 has read 70. T2 then updates the row to 40 and commits. T1 runs UPDATE acct SET bal = bal - 10 WHERE id = 1. What happens?

The newest version of the row was committed after T1's snapshot. Writing over it would lose T2's update, so PostgreSQL cancels T1 with a serialization failure.

04Both balances are 140 in total. Two transactions each check sum(bal) >= 100 and then take 100 from a different row. Under Repeatable Read, what can happen?

Each sees the same snapshot (140) and they write different rows, so no write conflict is detected. Only Serializable notices that the reads and writes cross and cancels one of them.

05What should an application do when a Serializable or Repeatable Read transaction fails with SQLSTATE 40001?

A serialization failure means nothing of the transaction was saved. Re-running the whole transaction lets it read fresh data and decide again; the retry here sees 40 and stops.

Key terms

Glossary

Isolation level: A setting that decides how much a transaction can see of other transactions running at the same time. Chosen per transaction, for example BEGIN ISOLATION LEVEL REPEATABLE READ.
MVCC: Multiversion concurrency control: an UPDATE adds a new version of a row instead of overwriting it, so readers and writers do not block each other.
Snapshot: The set of committed work a statement or transaction can see. Read Committed takes one per statement; Repeatable Read and Serializable take one per transaction.
Dirty read: Reading another transaction's change before it commits. PostgreSQL never allows it.
Nonrepeatable read: Reading the same row twice in one transaction and getting two different values, because another transaction committed in between.
Phantom read: Running the same search twice and getting a different set of rows. PostgreSQL's Repeatable Read prevents it.
Write skew: Two transactions read the same data, then each changes a different row, together breaking a rule that each one checked. A serialization anomaly.
Serialization failure (40001): The error PostgreSQL raises when it cancels a transaction to keep the result equal to some one-at-a-time order. The fix is to retry.

Fine print

Notes and sources

T1 and T2 are two database sessions. In the Repeatable Read and Serializable parts each one starts a fresh transaction with BEGIN; the names stay the same to keep the picture simple.
In Read Committed an UPDATE does not fail like this: it waits for the other writer, then works on the newest committed version of the row.
The SQL standard lets Repeatable Read show phantoms (new rows appearing in a repeated search). PostgreSQL’s Repeatable Read does not: its snapshot hides them too.
A dashed frame covers every committed version a snapshot includes; a read returns only the newest version inside the frame.
Old row versions are not kept forever: once no transaction can still see them, VACUUM removes them.
PostgreSQL’s Serializable (Serializable Snapshot Isolation) may sometimes cancel a transaction that would in fact have been safe. Which of the two is cancelled depends on timing; here T1 commits first, so T2 is the one to fail.
SQLite always gives serializable isolation between connections. In WAL mode a reading transaction sees a snapshot, and if it then tries to write after another connection has committed, SQLite refuses with SQLITE_BUSY_SNAPSHOT.
The retry loop uses psycopg 3, a PostgreSQL driver for Python; DSN is the connection string and withdraw() stands for the transaction’s own SQL. With autocommit on, each conn.transaction() block is one BEGIN ... COMMIT at the chosen level; SerializationFailure is psycopg’s error class for SQLSTATE 40001.

Quiz Unlocks after the last step.