12.2 InnoDB Read View, Undo, and Locking Reads

---

Metadata Card

• Prerequisites: 12.1 PostgreSQL MVCC, Snapshots, and Isolation Levels
• Keywords: InnoDB, Read View, Undo, Consistent Read, Next-Key Lock, Isolation Level Selection
• Code Language: SQL (MySQL 8.4)

---

The same name (Repeatable Read) does not imply identical behavior between PostgreSQL and InnoDB. When migrating applications, simply copying isolation level names is insufficient. Developers must distinguish between ordinary snapshot reads, current reads, and locking read ranges.

How InnoDB Rebuilds Old Versions

InnoDB maintains hidden fields within clustered index records, such as transaction IDs and roll-back pointers to undo records. When data is modified, undo information is generated. For consistent reads that require an older version of a row, InnoDB reconstructs the previous row content by following these pointers.

Current version in clustered record
  DB_TRX_ID = 120
  DB_ROLL_PTR ──> undo(old value, previous pointer) ──> older undo ...

Undo records serve both transaction rollbacks and consistent reads. As long as any active Read View might need an older version of a row, the corresponding update undo records remain unpurged. Long-running transactions thus increase the history list size, slow down cleanup operations, and expand storage usage.

Consistent Read and Read View

Standard SELECT reads in Read Committed and Repeatable Read isolation levels are typically non-locking consistent reads. A Read View records the boundary and set of active transactions at its creation time, and readers use this to determine whether a current version is visible. If a version is not visible, the system traverses the undo log to reconstruct an earlier version.

• Read Committed: Each consistent read establishes a new Read View;
• Repeatable Read: Within the same transaction, ordinary consistent reads reuse the snapshot established by the first consistent read;
• Modifications made by earlier statements in the same transaction are visible to that transaction.

At Repeatable Read, commits between START TRANSACTION and the first consistent read can appear in the first snapshot. START TRANSACTION WITH CONSISTENT SNAPSHOT establishes it immediately at this isolation level.

Ordinary Read and Locking Read Are Not at the Same Point in Time

This is a separate MySQL 8.4 lab, using two mysql connections to the same practice database. Create this InnoDB table in an empty database:

CREATE TABLE account (
  account_id bigint PRIMARY KEY,
  balance decimal(18, 2) NOT NULL CHECK (balance >= 0)
) ENGINE=InnoDB;
INSERT INTO account VALUES (1, 500.00), (2, 200.00);

In A, run SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; START TRANSACTION;, then the ordinary read below: expect 500.00. In B, add 10 with autocommit. A's next ordinary read remains 500.00, but its following locking read sees 510.00. ROLLBACK in A after observing this. Use locking reads inside an explicit transaction to retain their locks for subsequent modifications.

SELECT balance
FROM account
WHERE account_id = 1;

Ordinary reads retrieve historical versions visible in the current Read View and do not wait if another transaction holds a write lock on the row.

SELECT balance
FROM account
WHERE account_id = 1
FOR UPDATE;

Locking reads lock the current index record and may wait for a newer version if necessary; older versions cannot be locked. As a result, within the same Repeatable Read transaction, mixing ordinary snapshot reads with locking reads can lead to observations of data from different points in time. When designing a read (evaluate) write workflow, it's essential to clearly distinguish between reads that merely report information and those that must protect subsequent modifications.

FOR SHARE takes shared locks; FOR UPDATE takes exclusive locks. NOWAIT fails immediately on a conflicting row lock. SKIP LOCKED skips locked rows, which suits a worker queue but cannot supply a complete consistent result set.

Record, Gap, and Next-Key Lock

InnoDB locks index records and index ranges encountered during a scan, not abstract "SQL row conditions."

• record lock: locks a specific index record;
• gap lock: locks the gap between two index keys, preventing new inserts;
• next-key lock: a combination of a record lock and the gap immediately preceding it;
• insert intention: multiple transactions preparing to insert into the same gap at different positions can express their intent.

Under the Repeatable Read isolation level, locking reads and modification statements may use gap or next-key locks to prevent phantom rows within a scanned range. A full unique-index equality lookup that finds an existing record typically needs only a record lock. A missing key may still require a lock on the insertion gap. Missing indexes or overly broad ranges can expand both the scan and the lock scope.

Read Committed generally disables gap locking for search and index scans, retaining it only for foreign key and duplicate key checks, and releases record locks on non-matching rows earlier. This can improve concurrency but may alter the range protection that applications rely on.

MySQL Isolation Levels Cannot Be Copied Directly from PostgreSQL

InnoDB's default isolation level is Repeatable Read, whereas PostgreSQL defaults to Read Committed. InnoDB allows dirty reads at the Read Uncommitted level, unlike PostgreSQL, which maps that level to Read Committed.

Serializable in InnoDB enhances ordinary read operations with stronger locking behavior, but its actual effect depends on autocommit settings and statement structure. It does not equate to PostgreSQL's SSI (Serializable Snapshot Isolation), nor should it be labeled simply as "another name for Repeatable Read."

Therefore, isolation level selection should be driven by business operations:

Operation	Preferred Starting Point	Still Requires Confirmation
Independent point queries, atomic incremental updates	Read Committed or the product's default level	Whether the query logic is encapsulated within an atomic update
Consistent reporting	Repeatable Read read-only transactions	Snapshot establishment timestamp, cost of long-running transaction cleanup
Read followed by modification of fixed rows	SELECT ... FOR UPDATE or conditional updates	Lock ordering, timeout settings, and deadlock retry strategies
Maintaining range invariants	Serializable, range locks, or rearchitecting the model	Whether indexes cover the predicate, retry rate and success probability
Work queue item pickup	FOR UPDATE SKIP LOCKED	Whether skipping locks leads to inconsistent snapshots

An Application Version Check That Rejects Stale Writes

Run ALTER once on the practice table above; MySQL DDL implicitly commits. Then use the observed version 0 as the expected version:

ALTER TABLE account
ADD COLUMN version bigint NOT NULL DEFAULT 0;

UPDATE account
SET balance = 400.00,
    version = version + 1
WHERE account_id = 1
  AND version = 0;
SELECT ROW_COUNT() AS affected_rows;
SELECT balance, version FROM account WHERE account_id = 1;

The first execution yields affected_rows=1, balance 400.00, and version 1. Repeat only the UPDATE and SELECT statements while still expecting version 0: no row changes and the values stay the same. Read ROW_COUNT() immediately after UPDATE. Every business path that changes this row must increment its version, or bypass writes can escape detection.

The application must assert that exactly one row is affected. If zero rows are affected, it means the version has changed or the target no longer exists; this requires re-reading the data and deciding whether to retry, merge, or return a conflict. It should not be treated as a success.

Diagnosis Order

When encountering an InnoDB blocking situation, ask the following questions in this order:

1. Is this a simple consistent read, a locking read, or a write statement?
2. Which index did the execution plan choose, and what range of data did it scan?
3. Is the current isolation level enabling range protection?
4. Is the blocking occurring at the record level, gap level, next-key level, or at the metadata lock level?
5. When did the transaction start, and is it waiting due to application or network latency?

Seeing only SQL text without examining the index access path often fails to explain why a single-row update is blocking another insert.

References

• MySQL 8.4: Consistent Nonlocking Reads
• MySQL 8.4: Locking Reads
• MySQL 8.4: Transaction Isolation Levels
• MySQL: InnoDB Multi-Versioning

The next chapter will compare the write locks, two-phase locking, deadlock detection, and optimistic validation underlying MVCC in a single diagram.