Transaction isolation levels: an interactive simulator of dirty reads, lost updates and write skew

An isolation level says how much of each other concurrent transactions can see. At the strongest, serializable, the result is always as if the transactions ran one at a time. Weaker levels, repeatable read, read committed and read uncommitted, let more run at once but allow anomalies: reads of uncommitted data, values that change mid-transaction, updates that vanish and rules that break.

The playground runs two transactions on a fixed schedule in a small multi-version database. Pick an anomaly and a level, step through what each statement returns, and compare every combination in the table.

The five anomalies

What each level does

Most databases implement isolation with multi-version concurrency control: an update writes a new version of the row, and each reader picks the version its snapshot allows. Read committed takes a new snapshot for every statement, so a second read can see another transaction’s commit. Repeatable read, called snapshot isolation in the literature, keeps one snapshot for the whole transaction; in PostgreSQL, updating a row that changed after that snapshot fails with a serialization error, which is what stops the lost update in the playground. Serializable in PostgreSQL adds serializable snapshot isolation: it tracks what each transaction read and aborts one when two transactions each read something the other wrote, the pattern behind write skew. Other databases get serializability with locks instead, blocking rather than aborting.

Defaults differ. PostgreSQL, SQL Server and Oracle default to read committed and MySQL to repeatable read. Names differ too: Oracle’s serializable is snapshot isolation and allows write skew, and MySQL’s repeatable read lets lost updates through, because its updates read the latest row, not the snapshot.

Fixing anomalies without serializable

Most applications run at read committed and close the specific gaps that matter. Do the read-modify-write in one statement, lock the rows you read with SELECT ... FOR UPDATE, add a version column for optimistic locking, or let a unique or exclusion constraint enforce the rule:

-- lost update, fixed at read committed: let the database do the read-modify-write
UPDATE posts SET likes = likes + 1 WHERE id = 1;

-- or lock the row you read until you commit
SELECT likes FROM posts WHERE id = 1 FOR UPDATE;

-- write skew, fixed by locking every row the rule depends on
SELECT name FROM doctors WHERE on_call FOR UPDATE;

-- double booking, fixed by a constraint instead of a check (needs btree_gist)
ALTER TABLE bookings ADD CONSTRAINT no_overlap
  EXCLUDE USING gist (room WITH =, during WITH &&);

Or run the transactions that need it at serializable and retry on serialization failures. The whole transaction has to be retried, reads included, and it should be short:

from psycopg import errors

def transfer(conn, src, dst, amount, tries=5):
    for attempt in range(tries):
        try:
            with conn.transaction():
                conn.execute("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE")
                (balance,) = conn.execute(
                    "SELECT balance FROM accounts WHERE id = %s", (src,)).fetchone()
                if balance < amount:
                    raise ValueError("insufficient funds")
                conn.execute("UPDATE accounts SET balance = balance - %s WHERE id = %s", (amount, src))
                conn.execute("UPDATE accounts SET balance = balance + %s WHERE id = %s", (amount, dst))
            return
        except errors.SerializationFailure:   # SQLSTATE 40001: rerun the whole transaction
            continue
    raise RuntimeError("gave up after repeated serialization failures")

In a system design interview

Name the anomaly you are protecting against rather than just a level: “Bookings run at read committed, and an exclusion constraint on room and time range makes double booking impossible whatever the interleaving. Balance transfers lock both account rows in id order with SELECT ... FOR UPDATE, so they serialize without deadlocks. The reporting queries use repeatable read so a long report sees one consistent snapshot.” Expect the follow-ups: what happens across shards, where a single database’s isolation no longer applies, and how retries interact with side effects such as sending an email.

Read the guide to transactions and isolation or ACID vs BASE, see two-phase commit for transactions across databases, or watch a strong candidate design a ticketing system.

Questions people ask