The anomaly catalog

This catalog distinguishes executed schedules on MySQL 8.4.11, InnoDB from broader MySQL 8.4 contracts. The concept catalog defines the names. The examples concern transactional table rows, with isolation and transaction boundaries shown in their SQL. They do not establish full Hermitage coverage.

D means Demonstrated behavior: the linked Scenario executes and asserts that particular schedule, not all schedules. M means a Documented contract in the named manual sections below. † means an Entailed guarantee without a direct execution at that level, with its derivation in How to use this table. Own writes, mixed current/snapshot reads, and nontransactional effects need separate scope.

Code	Anomaly or operation	READ UNCOMMITTED	READ COMMITTED	REPEATABLE READ, default	SERIALIZABLE
G0	Overwrite before the prior writer ends	D: second writer waits; †	† row locks	† row locks	† row locks
G1a	Dirty read of a concurrent table change	D: 999 then rollback	M: excluded	M: excluded	M: excluded
G1b	Intermediate read	D: draft 999	D: draft 555 excluded; †	†	†
G1c	Cycle through dirty cross-reads	D: both new values, both commit	D: old values; † dirty cycle excluded	† dirty cycle excluded	† dirty cycle excluded
OTV	Committed rows hidden by an uncommitted overwrite	D: 12/19 split view	D: draft excluded; †	†	†
P2	Non-repeatable consistent SELECT from concurrent commit	Not executed here	D: 100 then 200	M: excluded; D: stable values	M: excluded in one transaction
G-single	Read skew across separate consistent SELECTs	Not executed here	D: 125 total	D: 100 total; † consistent reads; D: mixed DELETE/SELECT	† participating transaction
PMP	Phantom from concurrent commit	Not executed here	D: new matching row	M: excluded for consistent SELECTs; M: current DML can reach post-snapshot rows	† locking range in one transaction
P4	In-transaction read-modify-write	Not executed here	D: 110 instead of 120	D: 110 instead of 120	† retained read locks; not executed here
G2-item	On-call count and separate-row writes	Not executed here	Not executed here	D: both commit, count zero	D: B rejected, count one; † rule protection
G2	Predicate serialization anomaly beyond this on-call schedule	Not executed here	Not executed here	Not executed here; M: snapshot and current reads differ	† locking ranges for participating transactions; no predicate schedule here

How to use this table

The M cells use MySQL 8.4's level descriptions and consistent-read contract. READ COMMITTED and REPEATABLE READ consistent SELECTs exclude concurrent uncommitted versions, while own earlier writes remain visible. REPEATABLE READ retains the first consistent-read snapshot. Current UPDATE, DELETE and locking reads use different visibility. None of those rules makes every statement a snapshot read.

The † cells are explicit derivations, not executions at every level:

• G0: the exclusive locks on UPDATE targets prevent a second writer overwriting an uncommitted target. This does not prevent every dependency cycle or define dirty writes by a mixed final state.
• G1b and dirty-read G1c: excluding uncommitted versions excludes intermediate drafts and the dirty cross-reads that form this cycle. It does not exclude every read/write dependency cycle at READ COMMITTED; both old reads can still lack a serial order.
• OTV: a consistent read selects committed versions, so an uncommitted overwrite cannot hide the preceding committed version. Separate READ COMMITTED statements have different snapshots; a later committed partial overwrite can change one row.
• Read skew: one REPEATABLE READ snapshot excludes intervening concurrent commits between consistent SELECTs when the reader does not modify the observed data. SERIALIZABLE retained shared locks likewise prevent a writer from invalidating those reads before the transaction ends.
• SERIALIZABLE PMP, P4, and G2: locking reads retain shared locks in the explicit transaction, and the index-range rules protect scanned predicates against conflicting changes/inserts. Competing writes must wait or an attempt must fail rather than both silently invalidating the protected decisions. Every relevant operation must be inside the participating transaction; standalone autocommit reads do not retain this protection. A business rule additionally requires each serial transaction to preserve it.

The SERIALIZABLE Scenario demonstrates both explicit BEGIN locking and the standalone autocommit exception. It does not execute every predicate schedule, prove all catalog cells by repetition, or test stale reads made outside a protected transaction. A schedule's absence in one run is not impossibility.

The MySQL-specific pattern

REPEATABLE READ consistent SELECTs exclude later concurrent commits, but current DML and own writes can change what the transaction subsequently sees. The lost-update examples commit stale literal writes at both READ COMMITTED and REPEATABLE READ. This does not mean isolation can never protect a read-modify-write operation: explicit SERIALIZABLE transactions lock reads. Choose a protection with the transaction boundary and writer participation stated.

What READ UNCOMMITTED really costs

These schedules demonstrate particular dirty reads and a blocked competing write. The all-level exclusions above use contracts and marked derivations separately.

Dirty writes (G0)

B's first UPDATE waits for A to commit. Both of B's final prices are asserted in this order of execution. An uncommitted overwrite is the dirty-write event; interleaving or a mixed final state alone is not its definition.

A

B

UPDATE mug = 11

UPDATE mug = 12→ ⏳ waits

UPDATE cap = 21

COMMIT

⏵ UPDATE mug = 12→ completes

UPDATE cap = 22

COMMIT

SELECT→ 12, 22 (both B)

Two batch jobs reprice the whole catalog concurrently at READ UNCOMMITTED. The dirty-write event would be overwriting A's uncommitted row; a mixed final state is not its definition.

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; -- both of B's prices in this schedule
 id | price 
----+-------
  1 |    12 
  2 |    22 
(2 rows)

Verified against MySQL 8.4.11 · Run it yourself · Scenario source

Intermediate reads (G1b)

B reads draft 999 at READ UNCOMMITTED before A replaces it with committed 110. In the READ COMMITTED repeat, B reads 110 while A's draft 555 is uncommitted. This repeat does not claim to read 100 and then 110 at READ COMMITTED.

A

B

UPDATE balance = 999 (a draft)

SELECT balance→ 999 (reads the draft)

UPDATE balance = 110 (final value)

COMMIT

SELECT balance→ 110 (999 never committed)

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. B read 999 without executing any application action. At READ COMMITTED, B now excludes A's draft 555:

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.11 · Run it yourself · Scenario source

Circular information flow (G1c)

At READ UNCOMMITTED, both read the other's uncommitted new value and both commit: neither serial order produces both observations. After resetting the balances, READ COMMITTED excludes those dirty values. Both see 100 and both commit, a different dependency cycle that is still not serial-equivalent. No serializability claim is made for READ COMMITTED.

A

B

UPDATE alice = 111 (uncommitted)

UPDATE bob = 222 (uncommitted)

read bob→ 222 (B's uncommitted)

read alice→ 111 (A's uncommitted)

COMMIT

COMMIT

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)

Both dirty cross-reads are asserted, then both writers commit. No serial order of these two transactions gives both new values to the readers. No external application arithmetic is executed.

A> COMMIT;
Query OK

B> COMMIT;
Query OK

A> SELECT id, balance FROM accounts ORDER BY id;
 id | balance 
----+---------
  1 |     111 
  2 |     222 
(2 rows)

A> UPDATE accounts SET balance = 100;
Query OK, 2 rows affected

After resetting both balances, READ COMMITTED excludes these dirty cross-reads. The two old reads still do not form a serial execution.

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.11 · Run it yourself · Scenario source

Observed transaction vanishes (OTV)

C's READ UNCOMMITTED SELECT returns 12 from B's draft and 19 from A's commit. The READ COMMITTED repeat excludes A's uncommitted 51/59, then includes both after commit. These returned rows illustrate the stated split-view operation; the project does not claim the complete formal Hermitage suite or permanent visibility of a committed transaction's original values.

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 reads at READ UNCOMMITTED.

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 observed it. C's returned rows combine B's draft with A's committed second 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

These READ COMMITTED SELECTs exclude A's uncommitted changes, then include its commit:

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.11 · Run it yourself · Scenario source

Error codes to retry on

The InnoDB error-handling contract distinguishes rollback scope. Do not retry based on a code without checking the transaction state and application decision.

errno	SQLSTATE	Scope and next action	Evidence
1213	40001	Entire transaction rolled back; retry fresh reads/decisions or return controlled failure	D: SERIALIZABLE schedule; M: error handling
1205	HY000	Waiting statement rolled back by default; entire transaction with innodb_rollback_on_timeout=ON	M: error handling; separate locking lesson

These are not the only possible statement or connection errors. Neither retry success nor a particular deadlock victim is guaranteed. Deadlock detection can be disabled, and this audit does not execute the timeout configuration alternatives.

Further reading

• Hermitage: separate runnable isolation tests
• Anomalies by engine
• The same catalog for PostgreSQL