Isolation levels: dirty reads to write skew

An isolation level is a promise about which anomalies may appear when transactions run at the same time. The SQL standard defines four levels, READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ and SERIALIZABLE, by the phenomena each one forbids: dirty reads, non-repeatable reads and phantoms. Only SERIALIZABLE promises that the result matches some order of running the transactions one at a time. The weaker levels let through anomalies that the standard’s definitions do not even name, among them lost updates, read skew and write skew, and engines fill the gaps differently. PostgreSQL starts at Read Committed, MySQL’s InnoDB at Repeatable Read, and SQLite makes every transaction serializable by letting one writer in at a time. This lesson defines each anomaly with the schedule that shows it, and loses an update on a real database file.

Seven anomalies, one schedule each

• Dirty write. T2 overwrites a value that T1 has not committed yet: w1(x) w2(x) …
• Dirty read. T2 reads a value that T1 has not committed, which may still be rolled back: w1(x) r2(x) …
• Lost update. Both read x, both write a value computed from it, and one write silently replaces the other: r1(x) r2(x) w1(x) w2(x).
• Non-repeatable read. T1 reads x twice and gets two committed values, because T2 changed x in between: r1(x) w2(x) c2 r1(x).
• Phantom. T1 runs a search twice and gets different rows, because T2 inserted or deleted a match: r1(P) w2(y in P) c2 r1(P).
• Read skew. T1 reads x before T2 changes x and y, and y after, so it sees a state that never existed: r1(x) w2(x) w2(y) c2 r1(y).
• Write skew. Both read x and y, each changes a different one, and together they break a rule each one checked: r1(x) r1(y) r2(x) r2(y) w1(x) w2(y).

The first two are about uncommitted data, the subject of the previous lesson. The next three are about reading the same thing twice, or reading and then writing it. The last two involve two items, which is why they are easy to miss: every single row looks fine, and only the combination is wrong.

The four standard levels

• READ UNCOMMITTED: dirty reads, non-repeatable reads and phantoms are all possible.
• READ COMMITTED: no dirty reads; non-repeatable reads and phantoms are possible.
• REPEATABLE READ: no dirty reads and no non-repeatable reads; phantoms are possible.
• SERIALIZABLE: none of the three, and the result must match some serial order.

These are the minimum each level must forbid; an engine may forbid more. PostgreSQL, for example, lets you ask for READ UNCOMMITTED but runs it as READ COMMITTED, and its REPEATABLE READ does not allow phantoms either. The table also says nothing about lost updates, read skew or write skew, which is the gap the 1995 critique of the standard pointed out.

A lost update on a real database file

Two sessions add money to one wallet: A adds 50 and B adds 20, starting from 100, so the balance should end at 170. The program runs them on a SQLite file three ways:

"""A lost update, and two ways to avoid it, on a real SQLite database file.

Two sessions, A and B, each add money to the same wallet: A adds 50 and B adds 20,
starting from 100. Done one after the other, the balance ends at 170.
"""
import os
import sqlite3

DB = "wallet.db"


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


def balance(con):
    return con.execute("SELECT balance FROM wallet WHERE id = 1").fetchone()[0]


def fresh():
    for name in (DB, DB + "-wal", DB + "-shm", DB + "-journal"):
        if os.path.exists(name):
            os.remove(name)
    con = connect()
    con.execute("PRAGMA journal_mode = WAL")
    con.execute("CREATE TABLE wallet (id INTEGER PRIMARY KEY, balance INTEGER NOT NULL)")
    con.execute("INSERT INTO wallet VALUES (1, 100)")
    return con


print("1. Read, then write the new number: every statement on its own (autocommit)")
fresh()
a, b = connect(), connect()
seen_a, seen_b = balance(a), balance(b)
print(f"   A reads {seen_a}, B reads {seen_b}")
a.execute("UPDATE wallet SET balance = ? WHERE id = 1", (seen_a + 50,))
b.execute("UPDATE wallet SET balance = ? WHERE id = 1", (seen_b + 20,))
print(f"   A writes {seen_a + 50}, B writes {seen_b + 20}; the balance is now {balance(a)}: A's 50 is lost")

print("2. The same, but each session reads and writes inside one transaction")
fresh()
a, b = connect(), connect()
a.execute("BEGIN")
b.execute("BEGIN")
seen_a, seen_b = balance(a), balance(b)
print(f"   A reads {seen_a}, B reads {seen_b}")
a.execute("UPDATE wallet SET balance = ? WHERE id = 1", (seen_a + 50,))
a.execute("COMMIT")
print(f"   A writes {seen_a + 50} and commits")
try:
    b.execute("UPDATE wallet SET balance = ? WHERE id = 1", (seen_b + 20,))
except sqlite3.OperationalError as e:
    print(f"   B's write is refused: {e} ({e.sqlite_errorname})")
    b.execute("ROLLBACK")
    b.execute("BEGIN IMMEDIATE")
    seen_b = balance(b)
    b.execute("UPDATE wallet SET balance = ? WHERE id = 1", (seen_b + 20,))
    b.execute("COMMIT")
    print(f"   B starts again, reads {seen_b}, writes {seen_b + 20}; the balance is now {balance(a)}")

print("3. Let the database do the arithmetic: UPDATE ... SET balance = balance + n")
fresh()
a, b = connect(), connect()
a.execute("UPDATE wallet SET balance = balance + 50 WHERE id = 1")
b.execute("UPDATE wallet SET balance = balance + 20 WHERE id = 1")
print(f"   the balance is now {balance(a)}")

1. Read, then write the new number: every statement on its own (autocommit)
   A reads 100, B reads 100
   A writes 150, B writes 120; the balance is now 120: A's 50 is lost
2. The same, but each session reads and writes inside one transaction
   A reads 100, B reads 100
   A writes 150 and commits
   B's write is refused: database is locked (SQLITE_BUSY_SNAPSHOT)
   B starts again, reads 150, writes 170; the balance is now 170
3. Let the database do the arithmetic: UPDATE ... SET balance = balance + n
   the balance is now 170

In the first run, each session reads the balance and then writes the number it computed, with every statement in its own transaction. B’s write replaces A’s, and A’s 50 disappears without any error. In the second run, each session reads and writes inside one transaction. SQLite refuses B’s write: B’s view of the database is older than A’s commit, so writing would overwrite a change B never saw. B starts again, reads 150, and the balance reaches 170. In the third run, the database does the arithmetic with balance = balance + n, so there is nothing stale to write.

Other engines treat the second case differently. On PostgreSQL at Read Committed, the second run would not be refused: B’s UPDATE would store 120, because a number computed in the application is all the database sees. The third run is safe there too: the second UPDATE ... SET balance = balance + 20 waits for the first one to commit and then applies to the newest version of the row.

Check yourself

What PostgreSQL, InnoDB and SQLite allow at each level Pro: opens with a Pro, Premium or Ultimate pass

Why the standard's definitions were called ambiguous in 1995 Pro: opens with a Pro, Premium or Ultimate pass

Write skew reproduced, and five ways to handle it Pro: opens with a Pro, Premium or Ultimate pass

Interview questions

Key takeaways

• The standard defines its four levels by three phenomena: dirty reads, non-repeatable reads and phantoms; only SERIALIZABLE promises a serial result.
• Lost updates, read skew and write skew are not in the standard’s table, and weaker levels may allow them.
• A read followed by a write of a number computed in the application can lose an update; let the database do the arithmetic, or lock what you read.
• Engines differ: PostgreSQL starts at Read Committed, InnoDB at Repeatable Read, and SQLite serializes writers.