Isolation lab

What does REPEATABLE READ actually promise? It depends on the database, and the only reliable way to know is to run two transactions against each other and watch. This page does that, one statement at a time: sessions A, B (and C) in columns, the statements in the order you choose, and for each step the result, whether it had to wait and for whom, the error it got, and why.

The engines are models — PostgreSQL’s and InnoDB’s concurrency control written down as code — and every scenario on the page has been replayed on a real PostgreSQL 17 and MySQL 8.4 to check the model gives the same answers, step for step. For the real thing, connect a PostgreSQL server and the same steps run there, each session on its own connection.

How to use it

Pick a scenario, an engine and an isolation level, then Step ▶ (or → / Space) through it. Back (←) and Reset rewind — the model is deterministic, so it just replays — and Run all goes to the end. Click a step’s number to run up to it. Each session’s column header has its own level, so you can run A in SERIALIZABLE and B in READ COMMITTED.

A statement that cannot go on shows ⏳ waiting and the lock it wants: "FOR UPDATE lock on accounts id=1, held by A", or for InnoDB "insert intention on the gap (2, +∞)". When it is let go, its result appears in the step that freed it ("↳ step 6 finishes"). A statement sent to a session that is still waiting queues behind it, as it would in psql.
Step N on the right explains the step: whose snapshot was taken, a READ COMMITTED re-check of a changed row, an SSI rw-conflict, the deadlock cycle and its victim, what the error means and what the application should do (retry from BEGIN).
Sessions shows each one’s level, transaction, snapshot, what it waits for and — in SERIALIZABLE — its rw-antidependencies. Data shows committed values, uncommitted ones (→ 150, "B updated"), row locks (A X, B S) and InnoDB’s gap locks.
At the end, a line says whether the anomaly happened, or how it was prevented: by an error the application must retry, by a deadlock, by waiting, or by the snapshot.

Build your own: type a statement into the box under the timeline, pick the session and Add step. Hover a step (or press ✎ Edit on a phone) to edit, move or delete it; Setup SQL holds the CREATE TABLE and INSERT statements that run first. Editing a built-in scenario makes a copy, kept in this browser. Copy as SQL gives you the whole script; Open setup in the Postgres playground takes the setup to the Postgres playground.

Under each level runs every scenario on both engines at all four levels and colours the result: red where the anomaly happens, green where it is stopped (dark green: by an error you must retry). Click a cell to step through it.

The scenarios

Anomalies: dirty read, non-repeatable read, phantom, read skew, write skew (Alice and Bob both going off call), write skew with inserts, Fekete’s read-only transaction anomaly, and an InnoDB peculiarity — a REPEATABLE READ transaction whose UPDATE changes a row its own SELECT cannot see.
Lost update and three fixes: SELECT … FOR UPDATE, an atomic UPDATE … SET balance = balance + 25, and an optimistic version column (WHERE version = 1, then check the row count).
Locks: a deadlock, a job queue with FOR UPDATE SKIP LOCKED, the same queue without it (the second worker waits, then PostgreSQL READ COMMITTED re-checks the changed row, skips it and takes the next job — correct, but one worker at a time), NOWAIT, and InnoDB gap locks stopping an insert.
Hermitage tests: Martin Kleppmann’s Hermitage suite — G0, G1a, G1b, G1c, OTV, PMP, P4, G-single, G2-item, G2 — with T1, T2, T3 as A, B, C. The model reproduces Hermitage’s results table for both databases. Hermitage is licensed CC BY 4.0; the tests here are adapted from it.

The engines

PostgreSQL (as in chapter 13 of the manual). READ COMMITTED takes a snapshot per statement; a writer that waited for a row lock re-reads the newest version of the row and re-checks its WHERE clause. REPEATABLE READ takes one snapshot, at the transaction’s first statement, and fails a change to a row someone else changed since with 40001 could not serialize access due to concurrent update. SERIALIZABLE adds Serializable Snapshot Isolation: rw-antidependencies between serializable transactions and the "dangerous structure" checks of PostgreSQL’s predicate.c, including which transaction is failed and when (at the write, at a read, or at COMMIT). READ UNCOMMITTED is READ COMMITTED. A deadlock is broken by the session whose check finds it after deadlock_timeout (40P01); any error leaves the transaction failed (25P02) until ROLLBACK.

MySQL InnoDB (as in the MySQL 8.4 manual’s isolation levels and InnoDB locking). Plain SELECT`s are consistent reads from a read view — per statement in `READ COMMITTED, created by the first consistent read in REPEATABLE READ; READ UNCOMMITTED reads uncommitted data. UPDATE, DELETE and locking reads read the newest committed rows and lock them: record locks, plus gap and next-key locks in REPEATABLE READ and SERIALIZABLE (a scan with no usable key locks every row and the gap after the last, so every insert waits); READ COMMITTED releases locks on rows that do not match and UPDATE skips locked rows that no longer match (semi-consistent read). SERIALIZABLE turns a transaction’s plain SELECT`s into `FOR SHARE. Deadlocks are detected at once and the transaction with less work done is rolled back (1213, SQLSTATE 40001) — the session then runs in autocommit until the next BEGIN. Other errors undo only the statement.

The SQL it understands: BEGIN / START TRANSACTION (with ISOLATION LEVEL and READ ONLY), SET [SESSION] TRANSACTION ISOLATION LEVEL, COMMIT, ROLLBACK; SELECT with WHERE, ORDER BY, LIMIT, count/sum/min/max/avg, FOR UPDATE / FOR SHARE / LOCK IN SHARE MODE with NOWAIT or SKIP LOCKED; INSERT … VALUES; UPDATE … SET … WHERE; DELETE … WHERE; CREATE TABLE with a primary key; arithmetic, comparisons, AND/OR/NOT, IN, BETWEEN, IS NULL, coalesce. No joins, subqueries or GROUP BY; one index per table (the primary key); a waiting statement waits for ever (no lock wait timeout).

Where it is approximate: PostgreSQL’s SSI tracks reads by tuple, page and — for a sequential scan — whole table, so a real server can fail a transaction the model lets through (a false positive), never the other way round; the model tracks the predicates exactly. InnoDB’s choice of deadlock victim is reproduced for the scenarios here (equal weight: the transaction that closed the cycle). Messages are the servers' wording, shortened.

How it was checked

A small harness (scripts/isolation-lab-harness.py in the site’s repository) ran every scenario at every level on PostgreSQL 17.11 and MySQL 8.4.13, one connection per session, recording each statement’s result, which statements did not answer (blocked), which finished later, and the final tables. The test suite replays all 264 runs through the model and requires the same transcript, line for line, and the same final data — and checks the model against Hermitage’s published table.

A real PostgreSQL server

Real server connects through the same WebSocket bridge as the Postgres playground (its postgres-relay.py, or the gitwebsite app’s TCP relay). Then choose Real PostgreSQL as the engine: Reset runs the setup on the server and opens one connection per session with the chosen isolation level; each step sends the statement and waits 0.8 s — no answer means it is waiting, and pg_blocking_pids() says for whom. The side panel shows the committed tables and pg_locks as another connection sees them. The setup drops and creates tables: use a throwaway database (docker run -d -e POSTGRES_PASSWORD=iso -p 127.0.0.1:5432:5432 postgres:17). MySQL cannot be reached from a browser this way; its column of the matrix is the checked model.

Related: Postgres playground (real PostgreSQL in the browser, SKIP LOCKED queues, advisory locks, an animated concurrency model), transactions across services (two-phase commit, sagas, the outbox), storage engines (B+trees and LSM trees), DynamoDB and MongoDB.

Linking to a scenario

?scenario=write-skew&engine=postgres&level=serializable&step=8 opens a scenario at a step — e.g. write skew under SSI, a lost update in InnoDB, a gap lock, the matrix. Share packs an edited scenario into the link.

Privacy

The models run entirely in your browser. In real-server mode, statements go only to the bridge you name, and on to your server; the password stays in memory and is never put into a link.

Works with