Codelab: Trigger Isolation Anomalies

The Goal

You have read what isolation levels are — in Isolation Levels & Their Anomalies, where every level was defined by which anomalies it lets through. Now you are going to cause each one with your own hands, in two real database sessions, and watch the exact moment your data betrays you.

Here is the plan. Two terminals, one row, five anomalies. You will make a query return two different answers inside a single transaction. You will make a row appear out of nowhere. You will make two people withdraw the same money and watch one of the withdrawals silently vanish. You will send both on-call doctors home at once while the database smiles and says nothing. And then, for each one, you will turn the isolation dial up one notch and watch the anomaly either disappear or blow up in your face as an error you have to retry.

You will also see the single most surprising thing about the whole topic, and it is the one the enumeration promised you: you cannot make Postgres give you a dirty read, no matter how hard you ask. MySQL will. That difference, on the exact same SQL, is the entire reason this lesson exists.

The dirty read, the same commands run against two databases at READ UNCOMMITTED. Across the top, the shared setup: transaction T1 holds an uncommitted UPDATE that sets a balance to 999. On the left, in red, MySQL 8: a second session T2 reads 999 while T1 is still open — a value that never committed — and when T1 rolls back the real value is 100, so T2 acted on a ghost; that is a dirty read. On the right, in green, PostgreSQL 16: the same T2 read returns 100, the committed value, because Postgres silently upgrades READ UNCOMMITTED to READ COMMITTED and refuses to perform a dirty read at all. The bottom line: an isolation level name is not a portable guarantee — you must check your own database.

What this codelab owns. Isolation Levels & Their Anomalies taught the theory — the ladder, why the level names lie, why write skew is the boss. This is the keyboard. Every command below was run for real on PostgreSQL 16 and MySQL 8, and every block of output is pasted verbatim from that run. Your row values (999, 200, LSNs, timestamps) may differ in the boring details; what matters is the shape — which number changed when, and whether an error fired.

Prerequisites

Docker, two terminal windows, and about ten minutes. We use two databases on purpose — the dirty read only shows up on one of them, and that contrast is a lesson.

# a Postgres and a MySQL, side by side
docker run -d --name pg -e POSTGRES_PASSWORD=pw -e POSTGRES_DB=shop postgres:16
docker run -d --name my -e MYSQL_ROOT_PASSWORD=pw -e MYSQL_DATABASE=shop mysql:8

# give MySQL ~20s to finish its first-boot init before you connect

Setup: Two Terminals, One Row

Create the tables we'll fight over — an account balance, an events log, a counter, and two on-call doctors:

docker exec pg psql -U postgres -d shop -c "
CREATE TABLE accounts(id int PRIMARY KEY, balance int);
CREATE TABLE events(id serial PRIMARY KEY, kind text);
CREATE TABLE counters(id int PRIMARY KEY, val int);
CREATE TABLE doctors(name text PRIMARY KEY, on_call boolean);"

docker exec my mysql -uroot -ppw shop -e \
  "CREATE TABLE account(id int PRIMARY KEY, balance int); INSERT INTO account VALUES(1,100);"

Now the whole trick of this codelab: two sessions at once. Open two terminal windows and point each at the same database. Call them T1 and T2. Everything below is a dance between them — you type a line in T1, then a line in T2, in the order shown.

# Terminal 1                        # Terminal 2
docker exec -it pg psql \           docker exec -it pg psql \
  -U postgres -d shop                 -U postgres -d shop

Anomaly 1 — The Dirty Read Postgres Won't Give You

A dirty read is reading a value another transaction has written but not committed — a value that might vanish. Let's try to read one. This is the one anomaly where the two databases disagree, so do it on MySQL first.

In T1, set the weakest level, start a transaction, and change the balance to 999 — but do not commit. In T2, at the same level, read it:

-- Terminal 1 (MySQL)                       -- Terminal 2 (MySQL)
SET SESSION TRANSACTION ISOLATION
  LEVEL READ UNCOMMITTED;
START TRANSACTION;
UPDATE account SET balance=999 WHERE id=1;
                                            SET SESSION TRANSACTION ISOLATION
                                              LEVEL READ UNCOMMITTED;
                                            START TRANSACTION;
                                            SELECT balance FROM account WHERE id=1;
ROLLBACK;   -- the 999 never happened

-- what T2 read, while T1 was still open:
+---------+
| balance |
+---------+
|     999 |     <-- a value that was NEVER committed
+---------+

-- after T1's ROLLBACK, the real value:
|     100 |

T2 read 999 — a number that, thirty milliseconds later, had never existed. T2 acted on a ghost. That is a dirty read, and it is exactly as dangerous as it sounds: imagine T2 shipped an order because it saw a payment that then rolled back.

Now run the identical logic on Postgres and watch it refuse:

-- Terminal 1 (Postgres)                    -- Terminal 2 (Postgres)
BEGIN ISOLATION LEVEL READ UNCOMMITTED;
UPDATE accounts SET balance=999 WHERE id=1; -- (accounts seeded with 100)
                                            BEGIN ISOLATION LEVEL READ UNCOMMITTED;
                                            SELECT balance FROM accounts WHERE id=1;

SELECT balance FROM accounts WHERE id=1;
 balance
---------
     100        <-- NOT 999. the committed value.
(1 row)

100. You asked Postgres for READ UNCOMMITTED and it gave you READ COMMITTED anyway. This isn't a bug — it's documented behaviour: Postgres has no dirty read. Its four levels are really only three, because its weakest level is already too strong to leak one.

The first lesson of the lab, before any anomaly: the level name is not a portable guarantee. READ UNCOMMITTED means "dirty reads allowed" on MySQL and "exactly the same as READ COMMITTED" on Postgres. You cannot reason about isolation from the name; you have to know your engine.

Anomaly 2 — The Non-Repeatable Read

From here on we stay on Postgres. A non-repeatable read is when you run the same SELECT twice in one transaction and get different answers, because someone committed a change in between.

Seed the balance to 100. In T1, at READ COMMITTED, read it. In T2, change it to 200 and commit. Back in T1 — same query, same transaction — read again:

-- Terminal 1                               -- Terminal 2
BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT balance FROM accounts WHERE id=1;
                                            BEGIN;
                                            UPDATE accounts SET balance=200 WHERE id=1;
                                            COMMIT;
SELECT balance FROM accounts WHERE id=1;    -- same query, same txn
COMMIT;

SELECT balance FROM accounts WHERE id=1;
 balance
---------
     100     <-- first read
(1 row)

SELECT balance FROM accounts WHERE id=1;
 balance
---------
     200     <-- second read, SAME transaction. the ground moved.
(1 row)

One transaction, one query, two answers. Any logic in T1 that assumed the balance it read at the top is still true at the bottom is now wrong.

The fix: raise the level one notch to REPEATABLE READ. Reset the balance to 100 and run the exact same dance, changing only the first line to BEGIN ISOLATION LEVEL REPEATABLE READ;:

 balance
---------
     100     <-- first read
(1 row)

 balance
---------
     100     <-- second read. UNCHANGED, even though T2 committed 200.
(1 row)

100 → 100. At REPEATABLE READ, T1 reads from a snapshot taken when it began, so T2's committed change is invisible to it. The query is now, in fact, repeatable. Notice the fix cost you nothing but one keyword — and no error fired.

Anomaly 3 — The Phantom

A phantom is the non-repeatable read's sibling for ranges: you run a query that matches a set of rows, and on re-running it a new row has appeared in the range because someone inserted and committed it. Empty the events table, then:

-- Terminal 1                               -- Terminal 2
BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT count(*) FROM events WHERE kind='order';
                                            BEGIN;
                                            INSERT INTO events(kind) VALUES('order');
                                            COMMIT;
SELECT count(*) FROM events WHERE kind='order';
COMMIT;

 count
-------
     0     <-- first count: no orders
(1 row)

 count
-------
     1     <-- second count: a row PHANTOMED into the range
(1 row)

0 → 1. A row you never saw is suddenly in your result set. Re-run at REPEATABLE READ and it stays 0 → 0 — and here Postgres does something the SQL standard doesn't even require:

 count
-------
     0
(1 row)

 count
-------
     0     <-- no phantom. PG's Repeatable Read blocks these too.
(1 row)

The standard says REPEATABLE READ still permits phantoms. Postgres's implementation is snapshot isolation, which is stronger than the standard's REPEATABLE READ and blocks phantoms for free. This is the "names lie" point from Isolation Levels & Their Anomalies, now measured in your own terminal: Postgres's REPEATABLE READ gives you more than the label promises. On MySQL, the same label means something different again. Never port the guarantee; port the test.

Anomaly 4 — The Lost Update

Now a write anomaly, and the most expensive one in the section: two transactions read the same value, both compute a new one from it, and both write — so one of the writes is silently destroyed. This is the concurrency bug behind a double-spend.

One critical detail. You must do the arithmetic in your application — read the value, then write back a literal — the way real code with a read-modify-write does it. (If you instead write SET val = val + 10, the database re-reads at write time and you get the right answer — that's the atomic fix from Flash Sale: Inventory Under Contention, a different lesson. To see the loss, the read and the write must be separate.) Seed the counter to 50:

-- Terminal 1                               -- Terminal 2
BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT val FROM counters WHERE id=1;   -- app reads 50
                                            BEGIN ISOLATION LEVEL READ COMMITTED;
                                            SELECT val FROM counters WHERE id=1;  -- also 50
UPDATE counters SET val=60 WHERE id=1; -- app writes 50+10
COMMIT;
                                            UPDATE counters SET val=60 WHERE id=1; -- 50+10 too
                                            COMMIT;

final val = 60      <-- two +10s were applied. it should be 70. one increment VANISHED.

Both transactions did their job perfectly, in isolation, and together they lost ten dollars. There is no error; the second write just overwrote the first.

Raise to REPEATABLE READ and the database refuses to let it happen — but watch how. It does not silently fix it; it aborts the second writer:

UPDATE counters SET val = 60 WHERE id=1;
ERROR:  could not serialize access due to concurrent update

-- so T2 retries: reads the fresh value, redoes the math
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT val FROM counters WHERE id=1;   -- now 60
UPDATE counters SET val = 70 WHERE id=1;
COMMIT;

final val = 70      <-- correct.

This is a completely different kind of fix from the last two. Non-repeatable reads and phantoms vanished silently under a snapshot. A lost update can't — so REPEATABLE READ throws ERROR: could not serialize access due to concurrent update and hands the problem back to you. Your application must catch that error and retry the transaction. A retry loop is not optional at this level; it is the price of admission.

Anomaly 5 — Write Skew, the Boss

The last one is the anomaly that survives the level you just decided was bulletproof. Write skew is when two transactions each read a shared invariant, each individually decide their write is safe, and together break the invariant — because they wrote to different rows, so no snapshot ever saw a conflict.

The classic: an on-call rota that must always have at least one doctor on call. Alice and Bob are both on call. Both, independently, decide to go home. Run it at REPEATABLE READ — snapshot isolation, the level that just fixed everything:

-- Terminal 1  (Alice)                      -- Terminal 2  (Bob)
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM doctors WHERE on_call;  -- 2. safe to leave.
                                            BEGIN ISOLATION LEVEL REPEATABLE READ;
                                            SELECT count(*) FROM doctors WHERE on_call; -- 2. safe.
UPDATE doctors SET on_call=false
  WHERE name='alice';
COMMIT;
                                            UPDATE doctors SET on_call=false
                                              WHERE name='bob';
                                            COMMIT;

doctors on call = 0      <-- the invariant is BROKEN. and not one error fired.

Zero doctors on call. Each transaction read 2, each correctly concluded "there's another doctor, I can leave," and each wrote to its own row — so snapshot isolation, which only watches for two transactions writing the same row, saw nothing wrong. The invariant spanned two rows, and no snapshot can see across that gap. This is why snapshot isolation is not serializable, and it is the single most important thing to carry out of the isolation topic.

There is exactly one level that catches it. Re-run at SERIALIZABLE:

-- Terminal 2's COMMIT:
UPDATE doctors SET on_call=false WHERE name='bob';
ERROR:  could not serialize access due to read/write dependencies among transactions
DETAIL:  Reason code: Canceled on identification as a pivot, during write.
HINT:  The transaction might succeed if retried.

doctors on call = 1      <-- invariant HELD. Bob's txn was aborted; a retry sees Alice already off.

ERROR: could not serialize access due to read/write dependencies among transactions. Read the DETAIL: Canceled on identification as a pivot. Postgres's Serializable (an implementation called SSI — serializable snapshot isolation) actively watches the read/write dependencies between live transactions, spots the dangerous cycle write skew creates, and aborts one transaction to break it. The invariant holds — at the cost, again, of an abort you must retry. Only true serializable closes write skew; nothing weaker can.

Raising the isolation level stops an anomaly in two different ways, side by side. On the left, in green, PREVENT: a stable snapshot makes the READ anomalies vanish — non-repeatable read goes to a steady 100 then 100, and the phantom count goes to 0 then 0, with no error at all, because REPEATABLE READ re-reads its own snapshot and the anomaly has nowhere to happen; it is free and you change nothing. On the right, in amber, ABORT and RETRY: the WRITE anomalies cannot be snapshotted away, so the database aborts the losing transaction with ERROR could not serialize access, SQLSTATE 40001 — the lost update retries to 70, the write skew retries to 1 doctor on call — because SERIALIZABLE aborts the loser and your application must be ready to retry. The bottom line: reads are prevented for free, writes are caught only if you retry.

What the Levels Actually Did — and a Lab to Keep

Step back and look at the two kinds of fix you just saw, because they are not the same tool:

The read anomalies — non-repeatable read, phantom — were fixed silently, for free. A higher level gives your transaction a stable snapshot, and the anomaly simply has nowhere to happen. You change one keyword and nothing else.
The write anomalies — lost update, write skew — cannot be snapshotted away, because the conflict is between two writes. So the database does the only thing it can: it lets one transaction win and aborts the other with serialization_failure (SQLSTATE 40001). The fix is correct, but it is not free — your application must catch the error and retry.

That single distinction is the practical heart of choosing an isolation level: raising it either costs you nothing (reads) or costs you a retry loop (writes). Never turn Serializable on without a retry loop behind it.

You've now done this the hard way, in real terminals. Here's the same machinery with no setup — the lab from Isolation Levels & Their Anomalies. Pick a level and an anomaly, interleave the two transactions yourself, and watch the anomaly appear or get blocked. Reproduce every result above, then go looking for the edges.

You just triggered these by hand, in real terminals. Here is the same thing without the setup: pick an isolation level and an anomaly, then interleave the two transactions op-by-op — click the next runnable step in either column, or auto-play the classic schedule — and watch the anomaly appear or get blocked. Reads resolve against a per-transaction snapshot (Repeatable Read / Serializable), the live committed value (Read Committed), or even the other transaction's uncommitted buffer (Read Uncommitted, the dirty read). Run write skew at Repeatable Read and watch the invariant break with no error; run it again at Serializable and watch the exact abort Postgres gave you above.

Clean Up

docker rm -f pg my

Both containers gone. Nothing left running on your machine.

What Just Happened: The Map

the anomaly	what you saw (measured)	the fix — and how it fixes it
Dirty read	MySQL `READ UNCOMMITTED` → T2 read the uncommitted `999`; Postgres → `100`	Postgres has no dirty read (RU = READ COMMITTED); on MySQL, raise to READ COMMITTED
Non-repeatable read	`READ COMMITTED` → same SELECT `100 → 200` in one txn	REPEATABLE READ → `100 → 100` — snapshot prevents it, no error
Phantom	`READ COMMITTED` → range count `0 → 1`	REPEATABLE READ → `0 → 0` — snapshot (Postgres blocks these, beyond the standard)
Lost update	`READ COMMITTED` → two `+10`s → final `60`, not `70`	REPEATABLE READ → `ERROR: could not serialize access due to concurrent update` → abort + retry → `70`
Write skew	REPEATABLE READ → both leave → `on_call = 0`, no error	SERIALIZABLE → `could not serialize access due to read/write dependencies` → abort + retry → `on_call = 1`

Mental-Model Corrections

"READ UNCOMMITTED lets me see a dirty read." On MySQL, yes. On Postgres, no — RU is silently READ COMMITTED and a dirty read is impossible. The level name is not a portable guarantee.

"REPEATABLE READ is the same everywhere." Postgres's REPEATABLE READ is snapshot isolation and blocks phantoms, which the SQL standard says REPEATABLE READ may permit. Same words, stronger behaviour — and different again on MySQL. Check your engine.

"A higher isolation level just makes anomalies disappear." Only the read anomalies vanish silently under a snapshot. The write anomalies come back as an abort you have to catch and retry — serialization_failure / 40001.

"Snapshot isolation (REPEATABLE READ) is basically serializable." It is not. Write skew slips right through it, with no error, because the two writes touch different rows. You measured it: on_call = 0. Only true SERIALIZABLE catches it.

"Just always use SERIALIZABLE, then." Fine — but only with a retry loop, because it will abort transactions under contention (you saw Canceled on identification as a pivot). No retry loop, no serializable.

Key Takeaways

⭐ The level name is not a portable guarantee. READ UNCOMMITTED gives a dirty read on MySQL (999) and nothing on Postgres (100). A level is defined by which anomalies actually leak — so test your own database, don't trust the label.
Read anomalies are fixed for free. Non-repeatable read (100 → 200) and phantom (0 → 1) both vanish at REPEATABLE READ because a snapshot holds still. One keyword, no error.
⭐ Write anomalies are fixed by an abort you must retry. Lost update (60 instead of 70) and write skew (on_call = 0) can't be snapshotted away; the DB raises serialization_failure (SQLSTATE 40001) and hands the conflict back. A retry loop is mandatory at these levels.
⭐⭐ Write skew is the boss. Two transactions, a shared invariant, two different rows — snapshot isolation never sees the conflict and the invariant breaks with no error at all. Only true SERIALIZABLE (SSI) catches it, by watching read/write dependencies and aborting a pivot.
You proved every rung of the ladder with your own hands. That is the difference between knowing the isolation table and believing it. Keep the lab; break things in it.