Database Transactions

Appearance

The anomaly catalog ​

What each MySQL isolation level does about every named anomaly, and the proof. The anomalies themselves (definitions, diagrams, and where the G-codes come from) live in Concepts: the anomaly catalog; this page is MySQL's answer sheet, covering every case Hermitage tests — including the rows where MySQL's answer differs from PostgreSQL's.

The short version: on MySQL, isolation levels protect reads, not read-modify-write cycles, so the ⚠️ cells in the bottom half of this table get fixed with locks or SQL arithmetic rather than the isolation knob. The REPEATABLE READ default still loses updates and current reads see phantoms, while SERIALIZABLE closes the rest with locks — so plan to retry on 1213.

CodeAnomalyREAD UNCOMMITTEDREAD COMMITTEDREPEATABLE READ (default)SERIALIZABLE
G0Dirty write✅ impossible — proof✅✅✅
G1aDirty read (aborted read)⚠️ happens✅ impossible✅✅
G1bIntermediate read⚠️ happens✅ impossible — proof✅✅
G1cCircular information flow⚠️ happens✅ impossible — proof✅✅
OTVObserved transaction vanishes⚠️ happens✅ impossible — proof✅✅
P2Non-repeatable read⚠️⚠️ happens✅ prevented (plain SELECTs) — proof✅
G-singleRead skew⚠️⚠️ happens⚠️ plain SELECTs safe — but writes aren't✅
PMPPhantom read⚠️⚠️ happens⚠️ plain SELECTs safe — current reads see phantoms✅
P4Lost update⚠️⚠️ happens⚠️ still happens — proof✅ prevented (locks + deadlock)
G2-item / G2Write skew (item & predicate)⚠️⚠️⚠️ happens✅ prevented — proof

The MySQL-specific pattern ​

Reading this table column by column, one theme emerges: on MySQL, isolation levels protect reads, not read-modify-write cycles. REPEATABLE READ gives your SELECTs a perfectly stable world — and your UPDATEs a completely different, current one. Everything in the bottom half of the table is fixed with explicit locking or SQL-side arithmetic, not with the isolation knob.

PostgreSQL's catalog looks different: its RR turns lost updates into retryable errors, its SERIALIZABLE detects write skew without locks, and its weakest level is Hermitage-clean — see both engines' answers side by side. The three ⚠️ cells in MySQL's REPEATABLE READ column are exactly where Hermitage's results for the two databases diverge, and all three trace back to current reads.

What READ UNCOMMITTED really costs ​

The G1 family and OTV are all ✅ from READ COMMITTED up — but unlike PostgreSQL, MySQL really does serve dirty data if you ask for it, so each one is observable:

Dirty writes (G0) ​

The one guarantee every level keeps: InnoDB always takes exclusive row locks for writes, so interleaved writes can't produce a state no serial order could:

A
B
UPDATE mug = 11
UPDATE mug = 12→ ⏳ waits
UPDATE cap = 21
COMMIT
⏵ UPDATE mug = 12→ completes
UPDATE cap = 22
COMMIT
SELECT→ 12, 22 (all B, never mixed)

Two batch jobs reprice the whole catalog concurrently — at READ UNCOMMITTED, the weakest level MySQL has. A mix of their prices (A's mug with B's cap) would be a dirty write: a state no serial order could produce.

A> SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
Query OK

B> SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
Query OK

A> BEGIN;
Query OK

B> BEGIN;
Query OK

A> UPDATE items SET price = 11 WHERE id = 1;
Query OK, 1 row affected

B may READ A's uncommitted rows at this level — but it may not overwrite them.

B> UPDATE items SET price = 12 WHERE id = 1;
⏳ B is waiting for a lock…

A> UPDATE items SET price = 21 WHERE id = 2;
Query OK, 1 row affected

A> COMMIT; -- releases both row locks
Query OK

⏵ B resumes:
Query OK, 1 row affected

B> UPDATE items SET price = 22 WHERE id = 2;
Query OK, 1 row affected

B> COMMIT;
Query OK

A> SELECT id, price FROM items ORDER BY id; -- all B — as if B ran after A. Never 12/21 or 11/22.
 id | price 
----+-------
  1 |    12 
  2 |    22 
(2 rows)

Verified against MySQL 8.4.10 · Run it yourself · Scenario source

Intermediate reads (G1b) ​

At READ UNCOMMITTED you don't only read uncommitted data — you read drafts the writer later overwrites, values that are never part of any committed history:

A
B
UPDATE balance = 999 (a draft)
SELECT balance→ 999 (reads the draft)
UPDATE balance = 110 (final value)
COMMIT
SELECT balance→ 110 (999 was never real)

A recalculates alice's balance in two steps. B watches at READ UNCOMMITTED.

B> SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
Query OK

B> BEGIN;
Query OK

A> BEGIN;
Query OK

A> UPDATE accounts SET balance = 999 WHERE id = 1; -- a working draft — A isn't done yet
Query OK, 1 row affected

B> SELECT balance FROM accounts WHERE id = 1; -- B just read a draft
 balance 
---------
     999 
(1 row)

A> UPDATE accounts SET balance = 110 WHERE id = 1; -- A settles on the final value…
Query OK, 1 row affected

A> COMMIT;
Query OK

B> SELECT balance FROM accounts WHERE id = 1;
 balance 
---------
     110 
(1 row)

B> COMMIT;
Query OK

The committed history is 100 → 110. The 999 B acted on was never true — not even for an instant of committed time. At READ COMMITTED the same watch shows only 100, then 110:

B> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK

B> BEGIN;
Query OK

A> BEGIN;
Query OK

A> UPDATE accounts SET balance = 555 WHERE id = 1;
Query OK, 1 row affected

B> SELECT balance FROM accounts WHERE id = 1; -- no drafts served here
 balance 
---------
     110 
(1 row)

A> ROLLBACK;
Query OK

B> COMMIT;
Query OK

Verified against MySQL 8.4.10 · Run it yourself · Scenario source

Circular information flow (G1c) ​

Two transactions reading each other's uncommitted writes — an exchange with no serial explanation, and gone at READ COMMITTED:

A
B
UPDATE alice = 111 (uncommitted)
UPDATE bob = 222 (uncommitted)
read bob→ 222 (B's uncommitted)
read alice→ 111 (A's uncommitted)
ROLLBACK
ROLLBACK

A adjusts alice while B adjusts bob — then each peeks at the other's row. In any serial order, at most one of them can see the other's write.

A> SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
Query OK

B> SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
Query OK

A> BEGIN;
Query OK

B> BEGIN;
Query OK

A> UPDATE accounts SET balance = 111 WHERE id = 1;
Query OK, 1 row affected

B> UPDATE accounts SET balance = 222 WHERE id = 2;
Query OK, 1 row affected

A> SELECT balance FROM accounts WHERE id = 2; -- A sees B's uncommitted write…
 balance 
---------
     222 
(1 row)

B> SELECT balance FROM accounts WHERE id = 1; -- …and B sees A's. Information flowed in a circle.
 balance 
---------
     111 
(1 row)

Neither value was committed when it was read — if either side now rolls back, the other has computed on data that never existed. Both roll back:

A> ROLLBACK;
Query OK

B> ROLLBACK;
Query OK

The same dance at READ COMMITTED: each sees the world before the other.

A> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK

B> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK

A> BEGIN;
Query OK

B> BEGIN;
Query OK

A> UPDATE accounts SET balance = 111 WHERE id = 1;
Query OK, 1 row affected

B> UPDATE accounts SET balance = 222 WHERE id = 2;
Query OK, 1 row affected

A> SELECT balance FROM accounts WHERE id = 2;
 balance 
---------
     100 
(1 row)

B> SELECT balance FROM accounts WHERE id = 1;
 balance 
---------
     100 
(1 row)

A> COMMIT;
Query OK

B> COMMIT;
Query OK

Verified against MySQL 8.4.10 · Run it yourself · Scenario source

Observed transaction vanishes (OTV) ​

At READ UNCOMMITTED a committed transaction can be visible in pieces — one row already overwritten by an uncommitted successor, the other still showing through:

A
B
C
id1 = 11
id2 = 19
UPDATE id1 = 12→ ⏳ waits
COMMIT (11 and 19)
⏵ UPDATE id1 = 12→ completes
C reads→ 12, 19 (A's 11 already gone)

A rewrites both balances and commits. B overwrites one of them. C watches — dirtily.

C> SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
Query OK

C> BEGIN;
Query OK

A> BEGIN;
Query OK

A> UPDATE accounts SET balance = 11 WHERE id = 1;
Query OK, 1 row affected

A> UPDATE accounts SET balance = 19 WHERE id = 2;
Query OK, 1 row affected

B> BEGIN;
Query OK

B> UPDATE accounts SET balance = 12 WHERE id = 1;
⏳ B is waiting for a lock…

A> COMMIT; -- A is committed — its writes are 11 and 19
Query OK

⏵ B resumes:
Query OK, 1 row affected

C> SELECT id, balance FROM accounts ORDER BY id; -- 12 is B's uncommitted draft, 19 is A's commit — A's 11 already vanished
 id | balance 
----+---------
  1 |      12 
  2 |      19 
(2 rows)

C never saw the committed state {11, 19}. Half of A was overwritten before C ever observed it — as far as C can tell, A only ever wrote one row. B now finishes:

B> UPDATE accounts SET balance = 18 WHERE id = 2;
Query OK, 1 row affected

B> COMMIT;
Query OK

C> SELECT id, balance FROM accounts ORDER BY id;
 id | balance 
----+---------
  1 |      12 
  2 |      18 
(2 rows)

C> COMMIT;
Query OK

READ COMMITTED never shows a committed transaction in pieces:

C> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK

C> BEGIN;
Query OK

A> BEGIN;
Query OK

A> UPDATE accounts SET balance = 51 WHERE id = 1;
Query OK, 1 row affected

A> UPDATE accounts SET balance = 59 WHERE id = 2;
Query OK, 1 row affected

C> SELECT id, balance FROM accounts ORDER BY id; -- all of the last committed state — nothing of A's draft
 id | balance 
----+---------
  1 |      12 
  2 |      18 
(2 rows)

A> COMMIT;
Query OK

C> SELECT id, balance FROM accounts ORDER BY id; -- and now all of A, atomically
 id | balance 
----+---------
  1 |      51 
  2 |      59 
(2 rows)

C> COMMIT;
Query OK

Verified against MySQL 8.4.10 · Run it yourself · Scenario source

Error codes to retry on ​

errnoSQLSTATEMeaningWhere you saw it
121340001Deadlock found; transaction rolled backSERIALIZABLE write skew, deadlocks
1205HY000Lock wait timeout; statement rolled back, transaction surviveslock timeouts

Further reading ​

MIT Licensed · Every transcript on this site was generated by a real database run against MySQL 8.4.10 and PostgreSQL 18.4 at bd6f201, and re-proven through psycopg and PyMySQL.

© 2026 Leonid Svyatov