SQL Isolation Levels Explained: Dirty, Non-Repeatable, Phantom

Two transactions touch the same row at the same time. What is each one allowed to see of the other's unfinished work? The answer is the isolation level, and the four standard levels are best understood not as settings but as a list of anomalies each one rules out. Every transaction below was run with Python's sqlite3 module on SQLite 3.37, and the outputs shown are the real ones.

The problem it solves

Full isolation, where every transaction behaves as if it ran alone, is expensive: somebody has to wait, or somebody has to be told to retry. Most workloads can tolerate some interference in exchange for more concurrency. Isolation levels name the trade precisely, in terms of three read anomalies the SQL standard defines, plus one more that PostgreSQL's documentation lists alongside them:

• Dirty read. You read data another transaction has written but not committed. If it rolls back, you acted on a value that never existed.
• Non-repeatable read. You read a row, another transaction commits a change to it, and your second read of the same row returns something different.
• Phantom read. You run a query with a condition twice, and the set of matching rows has changed because another transaction inserted or deleted rows.
• Serialization anomaly. Each transaction looked fine, but the combined result matches no order of running them one at a time.

The intuition

The levels form a ladder. Each rung forbids one more anomaly:

• Read uncommitted — forbids nothing in the standard. Dirty reads are allowed.
• Read committed — no dirty reads. Each statement sees data committed before it began, so two reads in one transaction can disagree.
• Repeatable read — also no non-repeatable reads. Phantoms are still allowed by the standard.
• Serializable — no anomalies at all; the outcome equals some serial order.

Two things make the ladder less tidy in practice. First, the standard sets minimums: an engine may give more than a level promises. PostgreSQL's read uncommitted behaves like read committed, and its repeatable read does not allow phantoms. Second, defaults differ: PostgreSQL defaults to read committed and MySQL's InnoDB to repeatable read. "The default level" is not a portable assumption.

Engines implement the stronger levels in one of two ways. Locking makes a conflicting transaction wait. Multiversion concurrency control gives each transaction a snapshot of committed data and detects conflicts at write or commit time, aborting one side. Either way, the cost of isolation is paid in concurrency.

Watch it run

The animation follows the lesson's ward: one row, three free beds. The writer sets the count to zero without committing, and the reader, inside its own transaction, still reads three. The writer commits, and the reader asks again: still three, because its snapshot did not move. Only when the reader ends its transaction does it see zero. A caption then points out that at a weaker level the middle read would have changed under it: a non-repeatable read.

The code

SQLite runs every transaction as serializable, with one documented exception: two connections sharing a cache, where the reader turns on PRAGMA read_uncommitted. That exception is a real dirty read:

import sqlite3

uri = "file:ward?mode=memory&cache=shared"
keep = sqlite3.connect(uri, uri=True)            # keeps the in-memory database alive
keep.executescript("CREATE TABLE bed (ward TEXT, free INT); INSERT INTO bed VALUES ('ivy', 3);")
writer = sqlite3.connect(uri, uri=True, isolation_level=None)
dirty = sqlite3.connect(uri, uri=True, isolation_level=None)
clean = sqlite3.connect(uri, uri=True, isolation_level=None)
dirty.execute("PRAGMA read_uncommitted = 1")

writer.execute("BEGIN")
writer.execute("UPDATE bed SET free = 0")        # not committed
print(dirty.execute("SELECT free FROM bed").fetchone())    # (0,)  a dirty read
try:
    clean.execute("SELECT free FROM bed")
except sqlite3.OperationalError as e:
    print(e)                                     # database table is locked: bed
writer.execute("ROLLBACK")
print(dirty.execute("SELECT free FROM bed").fetchone())    # (3,)  the 0 never existed

The connection without the pragma is refused rather than shown uncommitted data: here SQLite chooses waiting-or-failing over a dirty read.

In WAL mode, a read transaction sees a snapshot fixed by its first read. Later commits by other connections stay invisible until it ends, which rules out the non-repeatable read. Outside a transaction, every statement starts fresh and sees the latest commit, which is read-committed behaviour:

import os, tempfile

path = os.path.join(tempfile.mkdtemp(), "ward.db")
setup = sqlite3.connect(path)
setup.executescript("PRAGMA journal_mode=WAL; CREATE TABLE bed (ward TEXT, free INT); INSERT INTO bed VALUES ('ivy', 3);")
setup.close()
w = sqlite3.connect(path, isolation_level=None)
r = sqlite3.connect(path, isolation_level=None)

r.execute("BEGIN")
w.execute("UPDATE bed SET free = 2")             # committed before r's first read
print(r.execute("SELECT free FROM bed").fetchone())        # (2,)  snapshot fixed now
w.execute("UPDATE bed SET free = 1")             # committed after it
print(r.execute("SELECT free FROM bed").fetchone())        # (2,)  repeatable
try:
    r.execute("UPDATE bed SET free = free - 1")  # write from a stale snapshot
except sqlite3.OperationalError as e:
    print(e)                                     # database is locked
r.execute("ROLLBACK")
print(r.execute("SELECT free FROM bed").fetchone())        # (1,)  fresh statement

The failed update is the serializable guarantee at work. The reader's snapshot says two beds are free, but the database now says one. Letting it write 2 - 1 would silently undo the other commit, so SQLite refuses and the transaction must retry from a fresh read.

The same bug appears in application code as the lost update, and no isolation level protects you if the read and the write are separate transactions. Two booking clerks each read 3 free beds and each write back 3 - 1. Two beds were booked, but the count fell by one:

w.execute("UPDATE bed SET free = 3")
seen_a = w.execute("SELECT free FROM bed").fetchone()[0]  # clerk A reads 3
seen_b = r.execute("SELECT free FROM bed").fetchone()[0]  # clerk B reads 3
w.execute("UPDATE bed SET free = ?", (seen_a - 1,))
r.execute("UPDATE bed SET free = ?", (seen_b - 1,))
print(r.execute("SELECT free FROM bed").fetchone())        # (2,)  two bookings, one bed gone

w.execute("UPDATE bed SET free = 3")
for clerk in (w, r):                             # read and write in ONE statement
    clerk.execute("UPDATE bed SET free = free - 1 WHERE free > 0")
print(r.execute("SELECT free FROM bed").fetchone())        # (1,)

The complexity

Isolation is paid for in concurrency, not in big-O:

• Weaker levels block less and abort less, and push the burden of reasoning about interleavings onto application code.
• Locking engines make conflicting transactions wait; long transactions hold locks longer and make everyone slower, and lock cycles become deadlocks.
• Snapshot engines let readers and writers proceed without blocking each other; at their stronger levels they abort one of two conflicting writers. PostgreSQL reports these as serialization failures with SQLSTATE 40001, and its documentation says to retry the whole transaction.

Where it goes wrong

• Assuming the default. Code tested on one engine can see different anomalies on another, because the defaults and the implementations differ.
• Read-modify-write across statements. Reading a value into the application and writing back a computed one loses updates. Push the arithmetic into one UPDATE, or lock the row for the duration.
• No retry loop at serializable. Stronger levels do not fail less; they fail differently. Without a retry, "serializable" turns anomalies into user-visible errors.
• Long transactions. A transaction that stays open while the user thinks, or while a network call runs, holds its snapshot or its locks the whole time.

How to say it in an interview

"Isolation levels are defined by the anomalies they prevent. Read committed stops dirty reads; repeatable read also stops non-repeatable reads; serializable stops everything, including phantoms and write skew, so the result equals some serial order. The standard only sets minimums, so PostgreSQL's repeatable read is stronger than required, and defaults differ: read committed in PostgreSQL, repeatable read in InnoDB. Stronger isolation costs concurrency, as waiting or as aborted transactions, so at serializable I always wrap the transaction in a retry loop. And for counters I do the arithmetic in a single UPDATE so I can't lose an update."

Isolation is the I in transactions and ACID; the thread-level version of the lost update is the race condition.

Sources

• Transaction Isolation — PostgreSQL Documentation
• Isolation In SQLite — SQLite Documentation
• Write-Ahead Logging — SQLite Documentation
• Transaction Isolation Levels — MySQL 8.4 Reference Manual