Isolation levels and read anomalies

An isolation level decides how much a transaction may see of other transactions that run at the same time. Weaker levels let more read anomalies through: a dirty read sees data that may never be committed, a non-repeatable read gets two answers to one question, a phantom finds new rows on a second search, a lost update overwrites a change it never saw, and write skew breaks a rule that two transactions each checked. The SQL standard names four isolation levels: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ and SERIALIZABLE. PostgreSQL starts every transaction at Read Committed and MySQL’s InnoDB at Repeatable Read. SQLite has no level to choose: it admits a single writer at a time, and that alone makes its transactions serializable. Choose the level by the anomalies your code cannot live with, and set it for the transactions that need it.

Five read anomalies, one sentence each

• Dirty read: a transaction reads a change that another transaction has not committed and may still roll back.
• Non-repeatable read: reading one row twice gives two committed values, because another transaction changed it in between.
• Phantom: running one search twice returns a different set of rows, because another transaction inserted or deleted a match.
• Lost update: two transactions read a value and each writes a new value computed from it, so one write silently replaces the other.
• Write skew: two transactions check a rule that spans several rows, then change different rows, and together break the rule that each one checked.

The standard defines its levels by the first three. Lost updates and write skew start with a read that goes stale before the write, which is why they count among the read anomalies here, but the standard’s table does not mention them, and each engine handles them its own way.

The four levels, and what each engine does

The standard sets the least each level must prevent, and engines may prevent more. The isolation levels share their names across engines, but not their machinery:

• PostgreSQL runs Read Uncommitted as Read Committed, so it never shows a dirty read. Its Repeatable Read keeps one snapshot for the whole transaction, which also hides phantoms. Serializable adds checks and fails one transaction when the result could differ from every one-at-a-time order.
• MySQL with InnoDB really allows dirty reads at Read Uncommitted. At its default, Repeatable Read, plain SELECTs read a snapshot taken at the transaction’s first read, while UPDATE, DELETE and locking reads work on the newest committed rows.
• SQLite has nothing to set. Its transactions are serializable because writers take turns, and in WAL mode a reader keeps a stable snapshot while a writer commits.

You choose the level per transaction. In PostgreSQL, write BEGIN ISOLATION LEVEL REPEATABLE READ, or SET TRANSACTION ISOLATION LEVEL REPEATABLE READ as the first statement after BEGIN. In MySQL, SET TRANSACTION ISOLATION LEVEL READ COMMITTED comes before START TRANSACTION and applies to the next transaction only, while SET SESSION TRANSACTION ISOLATION LEVEL changes it for the rest of the connection.

A non-repeatable read on a real database

The browser runs one session at a time, so this program opens two connections to one SQLite file in WAL mode and runs them step by step. The reader reads the stock of a kettle twice, once with each read in a transaction of its own and once with both reads inside one transaction:

"""When does a read see another session's commit? Two connections to one SQLite file.

SQLite in WAL mode gives each read transaction a snapshot. A read outside BEGIN is a
transaction of its own; reads inside BEGIN ... COMMIT share one snapshot.
"""
import os
import sqlite3

DB = "shop.db"
for suffix in ("", "-wal", "-shm"):
    if os.path.exists(DB + suffix):
        os.remove(DB + suffix)


def connect():
    return sqlite3.connect(DB, autocommit=True, timeout=0)


setup = connect()
setup.execute("PRAGMA journal_mode = WAL")
setup.execute("CREATE TABLE stock (id INTEGER PRIMARY KEY, item TEXT NOT NULL, qty INTEGER NOT NULL)")
setup.execute("INSERT INTO stock VALUES (1, 'kettle', 10)")

reader, writer = connect(), connect()
QTY = "SELECT qty FROM stock WHERE id = 1"


def read(label):
    print(f"   reader sees {reader.execute(QTY).fetchone()[0]}{label}")


print("1. Two reads, each in a transaction of its own")
read("")
writer.execute("UPDATE stock SET qty = 7 WHERE id = 1")
print("   writer sets qty to 7 and commits")
read("  <- a non-repeatable read")

print("2. Two reads inside one transaction")
reader.execute("BEGIN")
read("")
writer.execute("UPDATE stock SET qty = 4 WHERE id = 1")
print("   writer sets qty to 4 and commits")
read("  <- the same snapshot as before")
reader.execute("COMMIT")
read(" after its own COMMIT")

1. Two reads, each in a transaction of its own
   reader sees 10
   writer sets qty to 7 and commits
   reader sees 7  <- a non-repeatable read
2. Two reads inside one transaction
   reader sees 7
   writer sets qty to 4 and commits
   reader sees 7  <- the same snapshot as before
   reader sees 4 after its own COMMIT

In the first run, each read is its own transaction and sees the latest commit, so the reader gets 10 and then 7. That is exactly how every statement behaves at PostgreSQL’s Read Committed: each one takes a fresh snapshot. In the second run, both reads share one snapshot and return 7, although the writer committed 4 in between, which is the promise of Repeatable Read. Only after its own COMMIT does the reader see 4.

Check yourself

Every anomaly on PostgreSQL, MySQL and SQLite, replayed Pro: opens with a Pro, Premium or Ultimate pass

Choosing and setting a level, with worked cases Pro: opens with a Pro, Premium or Ultimate pass

Interview questions

Key takeaways

• An isolation level decides which read anomalies a transaction may meet: dirty reads, non-repeatable reads, phantoms, lost updates and write skew.
• Defaults differ: PostgreSQL Read Committed, MySQL Repeatable Read, SQLite serializable with one writer at a time.
• Each statement at Read Committed sees the latest commits; a Repeatable Read transaction keeps one snapshot.
• Set the level per transaction, at its start.