Read views: when your snapshot is taken

At MySQL 8.4 InnoDB REPEATABLE READ, consistent reads use a read view and can also see the transaction's own earlier changes. The consistent-read contract places the persistent view at the first consistent read, not BEGIN. READ COMMITTED instead uses a fresh snapshot for each consistent read; locking reads use different rules.

BEGIN takes no snapshot: the first read does

R first sees A's update committed after BEGIN. With START TRANSACTION WITH CONSISTENT SNAPSHOT, R instead retains the earlier balance. The START TRANSACTION manual limits that snapshot modifier to REPEATABLE READ; it does not change the isolation level.

A

R

UPDATE balance=999 (+ commit)

first read→ 999 (view opens here, not at BEGIN)

consistent snapshot — view opens now

UPDATE balance=111

read→ 999 (snapshot predates it)

R> BEGIN;
Query OK

R has "begun" — but hasn't read anything. A commits a change meanwhile.

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

R> SELECT balance FROM accounts WHERE id = 1; -- A's update is visible — the read view was created just now, by this SELECT
 balance 
---------
     999 
(1 row)

R> COMMIT;
Query OK

START TRANSACTION WITH CONSISTENT SNAPSHOT moves the snapshot to the start:

R> START TRANSACTION WITH CONSISTENT SNAPSHOT;
Query OK

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

R> SELECT balance FROM accounts WHERE id = 1; -- this time the read view predates A's update
 balance 
---------
     999 
(1 row)

The documented nonlocking read-only optimization avoids a persistent transaction ID. This build exposes a placeholder above 2^48 for R; that numeric representation is not a public format guarantee.

A> SELECT CAST(trx_id AS UNSIGNED) > 281474976710656 AS placeholder_id FROM information_schema.innodb_trx; -- R is the only open transaction; this build reports an ID above 2^48
 placeholder_id 
----------------
              1 
(1 row)

R> UPDATE accounts SET balance = balance + 1 WHERE id = 1; -- R's first write…
Query OK, 1 row affected

A> SELECT CAST(trx_id AS UNSIGNED) > 281474976710656 AS placeholder_id FROM information_schema.innodb_trx; -- after the write, this build reports an ID below 2^48
 placeholder_id 
----------------
              0 
(1 row)

R> COMMIT;
Query OK

Verified against MySQL 8.4.11 · Run it yourself · Scenario source

Readers are free

The read-only optimization contract allows InnoDB to avoid a persistent transaction ID for nonlocking read-only work. This is an allocation optimization, not proof that reads cost nothing or that every declared read-only transaction avoids an ID.

The transcript observes INNODB_TRX.trx_id above 2^48 for R before its first write, then below that threshold after it writes. That numeric placeholder is an observation on the verified build, not a public ID-format contract or a guarantee that real IDs never reach it. The TRX_ID documentation is the contract to use when interpreting transaction information.

An idle read view can still retain undo. A cheap allocation does not make an indefinitely open transaction harmless. Next: history-list growth.

Further reading

• The PostgreSQL counterpart

The reconstructed support assessment records the exact manual support and execution limits for this lesson.