Read Committed
The MySQL 8.4 READ COMMITTED contract gives each consistent nonlocking SELECT a fresh snapshot of committed data, plus the transaction's own earlier changes. This is not the rule for every statement: UPDATE and locking reads can wait and use current rows. The following schedules run on MySQL 8.4.11, InnoDB, with explicitly selected levels.
Non-repeatable reads
A reads 100, B commits 200, and A's next SELECT reads 200. The second part asserts a competing UPDATE's row-lock wait and eventual committed value 400. The plain SELECTs here do not wait for row writers; that does not exclude metadata-lock waits.
A> SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK
A> BEGIN;
Query OK
A> SELECT balance FROM accounts WHERE id = 1;
balance
---------
100
(1 row)While A's transaction is still open, B updates the row and commits.
B> UPDATE accounts SET balance = 200 WHERE id = 1;
Query OK, 1 row affected
A> SELECT balance FROM accounts WHERE id = 1; -- same query, same transaction; different answer
balance
---------
200
(1 row)
A> COMMIT;
Query OKThese nonlocking SELECTs did not wait for row locks. The interleaving with UPDATEs makes B wait for A's row lock.
A> BEGIN;
Query OK
A> UPDATE accounts SET balance = 300 WHERE id = 1;
Query OK, 1 row affected
B> UPDATE accounts SET balance = 400 WHERE id = 1;
⏳ B is waiting for a lock…
A> COMMIT; -- releases the row lock
Query OK
⏵ B resumes:
Query OK, 1 row affected
B> SELECT balance FROM accounts WHERE id = 1; -- B's write landed on top of A's committed 300
balance
---------
400
(1 row)Verified against MySQL 8.4.11 · Run it yourself · Scenario source
Phantoms
B's committed INSERT changes A's matching set from two rows to three, total 1500. The count and total are returned SQL values, not a published application report.
A runs two SELECTs inside one transaction: a count first, then a count and total.
A> SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK
A> BEGIN;
Query OK
A> SELECT count(*) AS n FROM orders WHERE amount >= 100;
n
---
2
(1 row)Between A's two statements, B commits a new order that matches A's WHERE clause.
B> INSERT INTO orders VALUES (3, 700);
Query OK, 1 row affected
A> SELECT count(*) AS n, CAST(sum(amount) AS SIGNED) AS total FROM orders WHERE amount >= 100; -- a third row appeared out of nowhere; a phantom
n | total
---+-------
3 | 1500
(1 row)
A> COMMIT;
Query OKA's first SELECT returns count 2; the later SELECT returns count 3 and total 1500. No report publication is executed.
Verified against MySQL 8.4.11 · Run it yourself · Scenario source
Read skew: a total that never existed
A reads alice before B's transfer and bob after it: the returned values add to 125 although each committed state totals 100. The repeated REPEATABLE READ schedule returns 25 and 75, using one snapshot with no writes by the reader.
The rule: alice + bob = 100 in each committed state. A models an auditor reading the accounts row by row; B transfers 25 between them mid-audit.
A> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK
A> BEGIN;
Query OK
A> SELECT balance FROM accounts WHERE owner = 'alice';
balance
---------
50
(1 row)
B> BEGIN;
Query OK
B> UPDATE accounts SET balance = balance - 25 WHERE owner = 'alice';
Query OK, 1 row affected
B> UPDATE accounts SET balance = balance + 25 WHERE owner = 'bob';
Query OK, 1 row affected
B> COMMIT; -- a perfectly correct transfer; atomic, invariant preserved
Query OK
A> SELECT balance FROM accounts WHERE owner = 'bob'; -- 50 + 75 = 125. The auditor found 25 that never existed.
balance
---------
75
(1 row)
A> COMMIT;
Query OKNeither row was ever wrong; A read alice BEFORE the transfer and bob AFTER it. The repeated REPEATABLE READ consistent SELECTs use one snapshot; the reader makes no changes of its own:
A> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Query OK
A> BEGIN;
Query OK
A> SELECT balance FROM accounts WHERE owner = 'alice';
balance
---------
25
(1 row)
B> BEGIN;
Query OK
B> UPDATE accounts SET balance = balance - 10 WHERE owner = 'alice';
Query OK, 1 row affected
B> UPDATE accounts SET balance = balance + 10 WHERE owner = 'bob';
Query OK, 1 row affected
B> COMMIT;
Query OK
A> SELECT balance FROM accounts WHERE owner = 'bob'; -- 25 + 75 = 100; one snapshot, one moment in time
balance
---------
75
(1 row)
A> COMMIT;
Query OKVerified against MySQL 8.4.11 · Run it yourself · Scenario source
After the wait, the re-check
The manual's READ COMMITTED section documents semi-consistent UPDATE reads: a committed version can be used to check whether a locked row matches, followed by a current re-read and lock if it qualifies. Here B waits for A, then skips the row after A commits value 20. Zero affected rows and final values 20/30 are asserted.
A> BEGIN;
Query OK
A> UPDATE items SET value = value * 2 WHERE id = 1; -- row 1: 10 → 20, uncommitted
Query OK, 1 row affectedB targets WHERE value = 10. The latest committed version of row 1 still qualifies; but it's locked by A, so B waits.
B> SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK
B> BEGIN;
Query OK
B> UPDATE items SET value = 99 WHERE value = 10;
⏳ B is waiting for a lock…A commits. B wakes up and re-checks the row it waited for against the NEW version, where value is 20.
A> COMMIT;
Query OK
⏵ B resumes:
Query OK, 0 rows affected0 rows affected; the row slipped away.
B> COMMIT;
Query OK
B> SELECT id, value FROM items ORDER BY id;
id | value
----+-------
1 | 20
2 | 30
(2 rows)Verified against MySQL 8.4.11 · Run it yourself · Scenario source
Check affected rows instead of assuming a prior SELECT fixed the UPDATE's targets. For reports made from several nonlocking SELECTs, a REPEATABLE READ snapshot can exclude intervening concurrent commits, provided the reader does not itself change the data or mix current reads into that report. The manual also documents that READ COMMITTED disables gap locking for searches/index scans except foreign-key and duplicate-key checks. These schedules do not measure throughput or lock cost.