Decoding isolation levels: I built a toy DB to force dirty reads and phantom reads

Inventory goes negative during a flash sale. The stock check is right there in the code, the tests pass, and the reservation logic looks obviously correct to four reviewers. Then two requests land in the same few milliseconds and the counter drops below zero anyway.

The usual first instinct is to blame the application. Add a mutex, add a Redis lock, add a retry. Sometimes that works, and it works for reasons nobody on the team can articulate, which is its own kind of problem.

What is actually happening is that the transaction isolation level is doing exactly what it promised, and the promise is weaker than anyone assumed. I got tired of half remembering which anomaly each level permits, so I wrote a toy database whose only purpose is to reproduce the anomalies on demand. Roughly two hundred lines of Python. It taught me more than a decade of reading the comparison table.

The table everyone memorises is not about your database

Every isolation article opens with the same grid. Four levels, three anomalies, a scattering of yes and no.

This grid describes the ANSI SQL standard. It does not describe PostgreSQL, and it does not describe MySQL, and the gap between them is where production bugs live.

PostgreSQL accepts SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED and then quietly runs your transaction at READ COMMITTED. Dirty reads are not merely discouraged there, they are unimplementable, because the MVCC storage layer never exposes an uncommitted row version to another snapshot. The lowest level you can actually get is one step up from the lowest level the standard defines.

PostgreSQL also goes the other way at REPEATABLE READ. The standard permits phantoms at that level. Postgres implements it as snapshot isolation, where your transaction reads from a single frozen snapshot taken at first statement, so a plain SELECT cannot see rows inserted after you started. You get a guarantee stronger than the level name implies, for free, and most teams never learn they have it.

MySQL InnoDB defaults to REPEATABLE READ and handles it differently again. Plain reads use a consistent snapshot. Locking reads, the SELECT ... FOR UPDATE and SELECT ... LOCK IN SHARE MODE variants, take next-key locks that cover both the matched index records and the gaps between them.

So the same three words, REPEATABLE READ, mean at least three different things depending on which engine you are talking to and which kind of read you issue. The table is a summary of a standard nobody implements literally.

Building something small enough to break on purpose

Reproducing these anomalies against a real engine is annoying. You need two sessions, careful timing, and the engine keeps protecting you from the exact thing you are trying to observe. So the toy needs one property real databases refuse to give you: the ability to turn the safety off.

The core is a dictionary and a place to stash writes that have not been committed yet.

import threading

class ToyDB:
    def __init__(self):
        self.committed = {}        # key -> value visible to everyone
        self.pending = {}          # txid -> {key: value}, not yet committed
        self.lock = threading.Lock()

    def begin(self, txid):
        self.pending[txid] = {}

    def write(self, txid, key, value):
        self.pending[txid][key] = value

    def commit(self, txid):
        with self.lock:
            self.committed.update(self.pending.pop(txid))

    def rollback(self, txid):
        self.pending.pop(txid, None)

    def read(self, txid, key, dirty=False):
        if dirty:
            # Look inside every open transaction, including other people's.
            for other, writes in self.pending.items():
                if other != txid and key in writes:
                    return writes[key]
        return self.committed.get(key)

That dirty=True branch is the whole of READ UNCOMMITTED. It reads into another transaction’s private write set. No real engine hands you this, and once you see it written out as five lines you understand why.

db = ToyDB()
db.committed["stock:sku-42"] = 100

db.begin("T1")
db.write("T1", "stock:sku-42", 0)      # T1 zeroes the stock

db.begin("T2")
print(db.read("T2", "stock:sku-42", dirty=True))   # 0
db.rollback("T1")                                   # T1 never happened
print(db.read("T2", "stock:sku-42", dirty=True))   # 100

T2 made a decision based on a number that was never true. The transaction that produced it rolled back. There is no recovery here, because T2 already acted on the value. This is why dirty reads are treated as unacceptable rather than merely fast: the data was not stale, it was fictional.

Non-repeatable reads are a locking problem you can solve

Turn the dirty flag off and the next anomaly appears immediately.

db.begin("T3")
first = db.read("T3", "stock:sku-42")   # 100

db.begin("T4")
db.write("T4", "stock:sku-42", 60)
db.commit("T4")

second = db.read("T3", "stock:sku-42")  # 60, inside the same transaction

One transaction, one key, two different answers. Any logic that reads a value, does arithmetic, and writes the result is now wrong.

The fix is mechanical. Lock the rows you read, hold the locks until commit, and no one can modify them underneath you. That is two-phase locking, and it works because the row exists, so there is something concrete to lock.

That last clause is the entire reason phantoms are a separate category.

Phantoms live in the gaps, not in the rows

Add a predicate scan and the model breaks in a way row locking cannot repair.

def scan(self, txid, prefix):
    return {k: v for k, v in self.committed.items() if k.startswith(prefix)}

db.committed = {"order:1": 40, "order:2": 60}

db.begin("T5")
before = db.scan("T5", "order:")        # two rows, total 100

# T5 locks every row it just read. Both of them. Correctly.

db.begin("T6")
db.write("T6", "order:3", 25)           # a key T5 never saw
db.commit("T6")

after = db.scan("T5", "order:")         # three rows, total 125

T5 did everything right. It read a set of rows and locked every single one. The lock did nothing, because order:3 did not exist when the locks were taken. You cannot lock a row that has not been inserted.

sequenceDiagram
    participant T5 as T5 (reader)
    participant IDX as Index range order:*
    participant T6 as T6 (writer)

    T5->>IDX: scan prefix order:
    IDX-->>T5: order:1, order:2
    T5->>IDX: lock order:1, order:2
    Note over T5,IDX: Every returned row is locked
    T6->>IDX: insert order:3
    Note over IDX,T6: Falls in an unlocked gap
    T6->>IDX: commit
    T5->>IDX: re-scan prefix order:
    IDX-->>T5: order:1, order:2, order:3
    Note over T5: Phantom. The lock set was complete<br/>and still insufficient.

This is why engines that prevent phantoms through locking have to lock something other than rows. InnoDB takes next-key locks, which are a record lock plus a lock on the gap preceding it in the index. Locking the gap between order:2 and the end of the range blocks the insert of order:3 before it happens.

Two consequences follow, and both surprise people.

Gap locks are taken on the index that the query plan chose, not on the logical predicate you wrote. A SELECT ... FOR UPDATE filtering on an unindexed column can end up locking far more of the table than you intended, because the only structure available to lock ranges in is the index itself. Index design and lock granularity are the same problem wearing different hats, which is another reason the physical layout of your primary key matters more than it looks.

Gap locks also block inserts by transactions that are not conflicting with anything real. Two requests inserting genuinely unrelated orders can serialise against each other because their keys happen to fall in the same index gap. You bought phantom prevention with concurrency, and the bill arrives under load.

Snapshot isolation dodges all of it and introduces something worse

MVCC engines take a different route. Instead of locking ranges, give each transaction a consistent snapshot and let readers never block writers.

class SnapshotDB:
    def __init__(self):
        self.versions = {}      # key -> [(commit_ts, value), ...]
        self.clock = 0

    def begin(self):
        return self.clock       # the snapshot timestamp

    def read(self, snapshot_ts, key):
        # The newest version committed at or before the snapshot.
        history = self.versions.get(key, [])
        visible = [v for ts, v in history if ts <= snapshot_ts]
        return visible[-1] if visible else None

Every anomaly above disappears. Dirty reads are impossible because uncommitted versions have no commit timestamp. Non-repeatable reads are impossible because the snapshot does not move. Phantoms are impossible because a row committed after your snapshot is invisible no matter how you query for it.

Which sounds like the problem is solved, and it is not, and this is the part I find genuinely unsettling.

Consider an on-call rota with an invariant: at least one engineer must stay on duty. Two engineers, Ana and Ben, both currently on call, both try to go off call at the same moment.

sequenceDiagram
    participant A as Ana's transaction
    participant DB as Snapshot store
    participant B as Ben's transaction

    A->>DB: count on-call engineers
    DB-->>A: 2
    B->>DB: count on-call engineers
    DB-->>B: 2
    Note over A,B: Both snapshots say 2.<br/>Both conclude it is safe.
    A->>DB: set ana.on_call = false
    B->>DB: set ben.on_call = false
    A->>DB: commit
    B->>DB: commit
    Note over DB: Zero engineers on call.<br/>No write conflict was ever detected.

Ana wrote to her row. Ben wrote to his row. The two write sets do not overlap, so no conflict detector fires. Each transaction is individually correct against the state it observed. Together they broke the invariant.

This is write skew, and it is absent from the ANSI table entirely. Berenson and co-authors documented the omission in 1995 in A Critique of ANSI SQL Isolation Levels, which is where the term snapshot isolation gets its careful definition. The standard was written around a locking model, so the anomaly catalogue only contains anomalies that locking produces. An entirely different failure mode arrived with MVCC and the table never grew a column for it.

I keep coming back to how quietly this fails. There is no error, no deadlock, no rollback. Two transactions succeed and the database is wrong. Every observability signal you have looks healthy.

Where this lands in practice

Postgres answers write skew with true serializable isolation, implemented as serializable snapshot isolation since 9.1. It tracks read and write dependencies between concurrent transactions and aborts one when the pattern could produce a non serializable outcome.

The catch is what abort means for your application. SSI does not block, it fails you at commit with SQLSTATE 40001, a serialization failure. Code written against READ COMMITTED and moved to SERIALIZABLE without a retry loop does not become correct, it becomes intermittently broken in a new way. If you switch levels, the retry wrapper is part of the change, not a follow up ticket.

A few things I now check before arguing about isolation levels at all.

Find out what your ORM actually opens transactions at, rather than what you assume. Defaults differ by engine and by driver, and a framework that wraps every request in a transaction at READ COMMITTED behaves very differently from one that does not wrap at all.

Where an invariant spans rows, stop trying to express it through isolation. A unique constraint, an exclusion constraint, or a check on a single aggregate row gets enforced by the storage engine regardless of concurrency. Pushing the invariant into a constraint converts a subtle race into a loud violation, and a loud violation is a much better thing to receive at 3am than a silently negative counter.

Keep transactions short, which matters more than it used to. Gap locks and snapshot retention both cost more the longer a transaction stays open, and a transaction that holds a pooled connection while waiting on a network call combines the worst of both. That pattern has become common enough with agent driven backends that it deserves its own treatment, which is roughly what happens when reasoning latency ends up inside a database transaction.

One last distinction worth keeping straight, because the vocabulary collides. Isolation levels describe how concurrent transactions interfere inside one database. Consistency models describe what a distributed system guarantees about reads across replicas. Both fields use words like repeatable and consistent and they mean unrelated things, which is why a conversation about consistency levels in a globally distributed store and a conversation about REPEATABLE READ can run for twenty minutes before anyone notices they are discussing different problems.

The toy database is still sitting in a scratch directory. It cannot do joins, it has no durability, and its concurrency model is a single mutex. It remains the fastest way I have found to answer a question about isolation, because the anomaly shows up in about four lines instead of two sessions and a stopwatch.