Read views: when your snapshot is taken
InnoDB calls a snapshot a read view: the set of transactions whose changes you're allowed to see. Chapter 2 used snapshots to explain isolation; this lesson pins down the mechanics — when the view is created, and what it costs (almost nothing).
The timing rule, per the manual: "all consistent reads within the same transaction read the snapshot established by the first such read in that transaction." Not by BEGIN — by the first read. A transaction that has begun but not yet read has no opinions about the world at all:
BEGIN takes no snapshot — the first read does
R> BEGIN;
Query OKR 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 OKSTART 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)One more thing R hasn't needed so far: a transaction ID. InnoDB hands read-only transactions a placeholder above 2^48 instead of consuming a real, persistent ID.
A> SELECT CAST(trx_id AS UNSIGNED) > 281474976710656 AS placeholder_id FROM information_schema.innodb_trx; -- R is the only open transaction — and its ID is fake
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; -- …and only now does it get a real transaction ID
placeholder_id
----------------
0
(1 row)
R> COMMIT;
Query OKVerified against MySQL 8.4.10 · Run it yourself · Scenario source
Readers are free
The last part of the transcript shows something PostgreSQL users don't expect: the idle reader's trx_id is fake. The manual on INNODB_TRX.TRX_ID: "A unique transaction ID number, internal to InnoDB. These IDs are not created for transactions that are read only and nonlocking." And why: "A transaction ID is only needed for a transaction that might perform write operations or locking reads such as SELECT ... FOR UPDATE" — so "InnoDB can avoid the overhead associated with setting up the transaction ID (TRX_ID field) for transactions that are known to be read-only."
What information_schema.innodb_trx shows for such a transaction is a placeholder above 2⁴⁸, where real IDs never reach — the boolean in the transcript is exactly that check. The moment the transaction writes, a real ID is allocated and stamped into every row it touches (DB_TRX_ID, previous lesson).
The rule to carry away is that the read view is created by the transaction's first consistent read, not by BEGIN, so the gap between the two is a window where the world can still move — the same trap as PostgreSQL's snapshot-at-first-statement rule (compare). Reach for START TRANSACTION WITH CONSISTENT SNAPSHOT when you need the snapshot pinned at the start instead. And because a read-only transaction never even allocates a transaction ID, reading is cheap by construction; the expensive thing a reader does is stay open, which is where the history list comes in.