← System Design Simulator

Isolation Levels

By Rahul Kumar · Senior Software Engineer · Updated · Category: Kleppmann · Designing Data-Intensive Applications

Dirty read, phantom, write skew across RC / SI / SSI. Ch 7.

This interactive explanation is built for system design interview prep: step through Isolation Levels, watch the internal state change, and connect the concept to real distributed-system trade-offs.

Overview

Transaction isolation is the lie databases tell you to keep concurrent programming tractable. Every engine serves many transactions at once, but pretends to each one that it is the only transaction in the world. The degree to which that illusion holds up is the isolation level. The ANSI levels — Read Uncommitted, Read Committed, Repeatable Read, Serializable — were defined by the anomalies they forbid (dirty reads, non-repeatable reads, phantoms), but Kleppmann shows these definitions are historical and full of gaps. Modern systems ship Snapshot Isolation and Serializable Snapshot Isolation, which are not in the original standard. The classic demo is write-skew: two on-call doctors each check that at least one doctor is still on call, then each go off call simultaneously, leaving nobody on call. No single-row conflict fires, no lost update is visible, and yet an application invariant is broken. Only true serializability prevents it.

Isolation Levels — Interactive Simulator

Runs fully client-side in your browser; no sign-up. Or open full screen →

Launch the interactive Isolation Levels widget — step through the algorithm or protocol and observe the internal state updating in real time.

How it works

Read Committed (RC) guarantees you never see uncommitted data, typically via row-level locks on writes and MVCC snapshots on reads. Each statement sees a fresh snapshot, so two reads within one transaction can return different values — non-repeatable reads are allowed. Repeatable Read (RR) takes a snapshot at transaction start; all reads see that same snapshot regardless of concurrent commits. In MySQL's implementation, RR prevents non-repeatable reads and (surprisingly) most phantoms via gap locks. Snapshot Isolation (SI), as implemented in Postgres and Oracle, uses MVCC: readers see the snapshot at transaction start, writers check for write-write conflicts at commit. SI prevents lost updates via first-committer-wins but permits write-skew because the conflict is across different rows. Serializable Snapshot Isolation (SSI), Postgres's SERIALIZABLE mode, layers conflict detection on top of SI: it tracks read-write dependencies between concurrent transactions and aborts one if they form a cycle that would be non-serializable. The cost is false-positive aborts — your transaction may fail for reasons invisible to it — but application-level invariants hold without pessimistic locking. Two-phase locking (2PL) is the historical serializable implementation: hold read and write locks until commit, which serializes any conflicting pair but tanks throughput under contention.

Implementation

TransactionRunner: classic write-skew (doctor on-call) under RC, SI, SSI
import java.sql.*;
import java.util.concurrent.*;

/** Two doctors are on call. Each checks "at least one other doctor is on call"
 *  and then goes off. Under RC and SI both succeed and nobody is left on call. */
public class TransactionRunner {
    private final String url;

    public TransactionRunner(String url) { this.url = url; }

    /** Attempt to go off call. Returns true if the transaction committed. */
    public boolean tryGoOffCall(int doctorId, int isolationLevel) throws SQLException {
        try (Connection c = DriverManager.getConnection(url)) {
            c.setAutoCommit(false);
            c.setTransactionIsolation(isolationLevel);
            try (PreparedStatement q = c.prepareStatement(
                     "SELECT COUNT(*) FROM doctors WHERE on_call = true")) {
                ResultSet rs = q.executeQuery();
                rs.next();
                int onCall = rs.getInt(1);
                if (onCall < 2) {
                    c.rollback();
                    return false;
                }
            }
            try (PreparedStatement u = c.prepareStatement(
                     "UPDATE doctors SET on_call = false WHERE id = ?")) {
                u.setInt(1, doctorId);
                u.executeUpdate();
            }
            try {
                c.commit();
                return true;
            } catch (SQLException e) {
                // SSI can raise a serialization failure here. RC and SI do not.
                c.rollback();
                return false;
            }
        }
    }

    /** Run both doctors concurrently and show the result under a given level. */
    public void demo(int level, String levelName) throws Exception {
        ExecutorService pool = Executors.newFixedThreadPool(2);
        Future<Boolean> a = pool.submit(() -> tryGoOffCall(1, level));
        Future<Boolean> b = pool.submit(() -> tryGoOffCall(2, level));
        boolean aOk = a.get(), bOk = b.get();
        pool.shutdown();
        System.out.printf("%s: doctor1=%s doctor2=%s -> invariant broken? %s%n",
            levelName, aOk, bOk, (aOk && bOk));
    }

    public static void main(String[] args) throws Exception {
        TransactionRunner r = new TransactionRunner(args[0]);
        r.demo(Connection.TRANSACTION_READ_COMMITTED,  "READ_COMMITTED");
        r.demo(Connection.TRANSACTION_REPEATABLE_READ, "SNAPSHOT_ISOLATION");
        r.demo(Connection.TRANSACTION_SERIALIZABLE,    "SSI (Postgres)");
    }
}

Complexity

Key design decisions & trade-offs

Common pitfalls

Interview follow-ups

Recommended reading

Related