Repeatable Read
The MySQL 8.4 REPEATABLE READ contract establishes the snapshot at the first consistent table read, not necessarily BEGIN or the first query of any kind. Later consistent SELECTs exclude concurrent commits after that point. The transaction's own earlier changes remain visible. START TRANSACTION WITH CONSISTENT SNAPSHOT is a separate documented way to take the snapshot at transaction start at this level; it is not executed below.
UPDATE, DELETE and locking reads such as SELECT FOR UPDATE/SHARE use current rows rather than that snapshot. They can wait for conflicting locks, and see the transaction's own writes. These rules concern InnoDB on MySQL 8.4; the displayed schedules run on 8.4.11. They do not promise that a write always succeeds.
One snapshot, no phantoms
B commits 120 after A's BEGIN but before its first consistent SELECT. A includes 120 in its snapshot, excludes B's later 999 and new carol row, and includes its own change to bob (60). After COMMIT, a new standalone SELECT sees 999, 60, and carol.
A> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Query OK
A> BEGIN;
Query OK
B> UPDATE accounts SET balance = 120 WHERE id = 1;
Query OK, 1 row affectedThe first consistent table read includes B's commit after BEGIN.
A> SELECT id, balance FROM accounts ORDER BY id;
id | balance
----+---------
1 | 120
2 | 50
(2 rows)B changes an existing row AND inserts a new one; both committed instantly.
B> UPDATE accounts SET balance = 999 WHERE id = 1;
Query OK, 1 row affected
B> INSERT INTO accounts VALUES (3, 'carol', 300);
Query OK, 1 row affected
A> SELECT id, balance FROM accounts ORDER BY id; -- not 999; no non-repeatable read; and no carol; no phantom
id | balance
----+---------
1 | 120
2 | 50
(2 rows)
A> UPDATE accounts SET balance = balance + 10 WHERE id = 2;
Query OK, 1 row affected
A> SELECT id, balance FROM accounts ORDER BY id; -- own write is visible alongside the snapshot's old row 1
id | balance
----+---------
1 | 120
2 | 60
(2 rows)
A> COMMIT;
Query OKAfter COMMIT, this standalone SELECT takes a fresh snapshot.
A> SELECT id, balance FROM accounts ORDER BY id;
id | balance
----+---------
1 | 999
2 | 60
3 | 300
(3 rows)Verified against MySQL 8.4.11 · Run it yourself · Scenario source
Current reads punch holes in the snapshot
A's snapshot reads 100. Its UPDATE uses B's committed 150 to produce 200, which A's later SELECT sees as its own write. In the second schedule it waits for B's uncommitted 300 and then produces 350 after B commits.
A> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Query OK
A> BEGIN;
Query OK
A> SELECT balance FROM accounts WHERE id = 1; -- snapshot taken
balance
---------
100
(1 row)B commits a change. A keeps READING its stale snapshot…
B> UPDATE accounts SET balance = 150 WHERE id = 1;
Query OK, 1 row affected
A> SELECT balance FROM accounts WHERE id = 1;
balance
---------
100
(1 row)…but A's UPDATE is a current read: it computes from B's committed 150, not from the snapshot's 100.
A> UPDATE accounts SET balance = balance + 50 WHERE id = 1;
Query OK, 1 row affected
A> SELECT balance FROM accounts WHERE id = 1; -- 150 + 50; and now A sees it: the snapshot has a hole
balance
---------
200
(1 row)
A> COMMIT;
Query OKA's own write is visible to its later consistent SELECT. The PostgreSQL comparison is in its concurrent-update lesson.
If the competing write is NOT yet committed, A first waits on the row lock…
A> BEGIN;
Query OK
A> SELECT balance FROM accounts WHERE id = 1; -- snapshot taken
balance
---------
200
(1 row)
B> BEGIN;
Query OK
B> UPDATE accounts SET balance = 300 WHERE id = 1;
Query OK, 1 row affected
A> UPDATE accounts SET balance = balance + 50 WHERE id = 1;
⏳ A is waiting for a lock……and proceeds from B's value the moment B commits. No error here either.
B> COMMIT;
Query OK
⏵ A resumes:
Query OK, 1 row affected
A> SELECT balance FROM accounts WHERE id = 1; -- 300 + 50
balance
---------
350
(1 row)
A> COMMIT;
Query OKVerified against MySQL 8.4.11 · Run it yourself · Scenario source
Porting from PostgreSQL?
For these post-snapshot target-row changes, PostgreSQL REPEATABLE READ rejects the UPDATE with 40001; see its executed conflict cases. InnoDB accepts these current-row UPDATEs. It does not reject them merely because the earlier snapshot was stale. It can still fail with a deadlock (1213, SQLSTATE 40001), timeout, or constraint error. Do not infer that 40001 never occurs on MySQL.
Your DELETE and your SELECT live in different worlds
A's DELETE finds no current row with balance 20, yet its consistent SELECT still sees bob with snapshot balance 20. The Scenario asserts zero deleted rows and the retained snapshot value; this is not a new-row phantom demonstration.
A> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Query OKA opens a snapshot. B then reshuffles every balance and commits.
A> BEGIN;
Query OK
A> SELECT id, balance FROM accounts ORDER BY id; -- snapshot taken
id | balance
----+---------
1 | 10
2 | 20
(2 rows)
B> BEGIN;
Query OK
B> UPDATE accounts SET balance = balance + 5;
Query OK, 2 rows affected
B> COMMIT; -- current data is now 15 and 25
Query OKA closes every account with balance 20; its snapshot says that's bob. But the DELETE is a current read: it scans the committed 15 and 25, finds nothing, and deletes nothing.
A> DELETE FROM accounts WHERE balance = 20;
Query OK, 0 rows affected
A> SELECT id, balance FROM accounts WHERE balance = 20 ORDER BY id; -- …yet A still SEES a row with balance 20. Deleted: no such rows. Visible: one.
id | balance
----+---------
2 | 20
(1 row)
A> COMMIT;
Query OKA's DELETE used current rows while its consistent SELECT retained an older snapshot. A locking read must precede the decision and all relevant writers must coordinate; changing a later read alone is not a general repair. See the locking and SERIALIZABLE lessons.
Verified against MySQL 8.4.11 · Run it yourself · Scenario source
The consistent-read manual documents own-write/current-DML exceptions. A stable snapshot is not a guarantee that every operation sees the same rows, or that a multi-row business rule survives concurrent writers. See lost updates and write skew. A repair must protect the reads and decisions before writing, with participation by all relevant writers.
Reading and locking one row
Try the reading practice before reading this section if you want to predict the result.
In this MySQL 8.4.11 InnoDB schedule A's retained consistent read returns 100 after B commits 150. A's SELECT FOR UPDATE returns 150, but its next consistent read still returns 100. A's UPDATE uses current 150 to produce 200, visible to A before commit. B's plain read sees 150 before A commits and 200 afterward.
A> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Query OK
A> BEGIN;
Query OK
A> SELECT balance FROM accounts WHERE id = 1;
balance
---------
100
(1 row)
B> UPDATE accounts SET balance = 150 WHERE id = 1;
Query OK, 1 row affected
A> SELECT balance FROM accounts WHERE id = 1;
balance
---------
100
(1 row)
A> SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
balance
---------
150
(1 row)
A> SELECT balance FROM accounts WHERE id = 1;
balance
---------
100
(1 row)
A> UPDATE accounts SET balance = balance + 50 WHERE id = 1;
Query OK, 1 row affected
A> SELECT balance FROM accounts WHERE id = 1;
balance
---------
200
(1 row)
B> SELECT balance FROM accounts WHERE id = 1;
balance
---------
150
(1 row)
A> COMMIT;
Query OK
B> SELECT balance FROM accounts WHERE id = 1;
balance
---------
200
(1 row)Verified against MySQL 8.4.11 · Run it yourself · Scenario source
Text equivalent: the clustered record now contains B's committed 150. Undo information can reconstruct the earlier 100 that fits A's retained consistent-read view. A's locking read uses current 150 without refreshing that view. Its next consistent read still returns 100. A's UPDATE adds 50 to current 150, and its own 200 is visible to its later consistent read. Before A commits, B's consistent read still sees committed 150. After commit, B's fresh read sees 200.
Evidence boundary: the transcript asserts the returned values and affected rows, not hidden storage fields. The MySQL 8.4 multi-versioning contract states: “The roll pointer points to an undo log record written to the rollback segment.” It documents DB_TRX_ID, DB_ROLL_PTR, and reconstruction for clustered records; secondary indexes have separate behavior. The undo lesson distinguishes that contract from executed SQL visibility, and read views gives timing and algorithm limits. The drawing omits rollback, purge, secondary-index lookup, and full visibility checks. Compare PostgreSQL's heap tuple history; storing history does not give both engines the same locking-read rule.