12.2 InnoDB Read View, Undo, and Locked 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 locked 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 statements executed before the current transaction are visible to the current transaction.

In Repeatable Read mode, a Read View created after an START TRANSACTION statement and before the first ordinary consistent read may still include concurrently committed transactions. If your application requires immediate snapshot validation, consider using START TRANSACTION WITH CONSISTENT SNAPSHOT in appropriate scenarios.

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

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 acquire a shared lock, FOR UPDATE acquire a stronger modification intent; NOWAIT cause conflicts to fail immediately, SKIP LOCKED skip locked rows, this behavior is suitable for multi-consumer task queues, but unsuitable for general queries requiring a complete and 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, SELECT and UPDATE statements may use gap or next-key locks to prevent phantom rows within a scanned range. If a single record is precisely located using the full unique condition of a unique index, a record lock is typically sufficient. 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-after-modify of specific 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

A Application with Optimistic Locking That Doesn't Silent Overwrite

Add a version number to the record:

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 = 7;

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 situation 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 locked 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.