Understanding Consistent Reads and MVCC in MySQL InnoDB

Understanding Consistent Reads and MVCC in MySQL InnoDB

The public‑business team at Hujiang migrated its core systems from Windows to Linux and switched the database layer from SQL Server to MySQL; to ensure high‑concurrency performance they need to understand the inner workings of InnoDB’s consistency‑read mechanism.

Consistent Read (Consistend Read)

In InnoDB a consistent read returns a snapshot of the data as of a specific point in time, showing changes committed before that point while ignoring later modifications and uncommitted transactions. The snapshot rules differ depending on the transaction isolation level:

When the isolation level is REPEATABLE READ , all consistent reads in the same transaction see the snapshot taken by the first SELECT.

When the isolation level is READ COMMITTED , each SELECT builds its own fresh snapshot.

Consistent reads are the default mode for SELECT statements under REPEATABLE READ and READ COMMITTED; they acquire no locks, allowing other sessions to read and write the table concurrently.

Consistent reads apply only to SELECT statements; DML statements (INSERT, UPDATE, DELETE) are not guaranteed to use the same snapshot.

Example Scenario

Session A                               Session B
-----------------------------------------------
begin;                                   begin;
-----------------------------------------------
SELECT * FROM t;                         SELECT * FROM t;
> empty   set                            > empty   set
-----------------------------------------------
INSERT INTO t (id) VALUES (1);
> 1 row affected
SELECT * FROM t;
-----------
|  id |
-----------
|  1  |
-----------
-----------------------------------------------
SELECT * FROM t;                         SELECT * FROM t;
> empty   set                            > empty   set
-----------------------------------------------
COMMIT;
-----------------------------------------------
SELECT * FROM t;
> empty   set
-----------------------------------------------

Consistent reads do not apply to certain DDL statements such as DROP TABLE and ALTER TABLE , which may raise ER_TABLE_DEF_CHANGED errors when a snapshot cannot be built.

Multi‑Version Concurrency Control (MVCC)

InnoDB, along with other engines like Falcon, implements MVCC using undo logs to store previous versions of rows. MVCC is also used by Oracle and PostgreSQL, though implementations differ.

Undo Log

The undo log records the old version of a row and is stored in undo‑log segments, which reside in the system tablespace, temporary tablespace, or dedicated undo tablespaces.

Property

Value

Command‑line option

--innodb-rollback-segments=#

System variable

innodb_rollback_segments

Scope

Global

Dynamic

Yes

Data type

Integer

Default

128 (max)

Min

1

Max

128

InnoDB can support up to 128 rollback segments; each segment can handle up to 1,023 concurrent transactions.

mysql> show variables like 'innodb_rollback_segments';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_rollback_segments | 128   |
+--------------------------+-------+

Hidden Columns

InnoDB adds three hidden columns to each row to implement MVCC:

Column Name

Length (bytes)

Purpose

DB_TRX_ID

6

Transaction ID of the last insert or update.

DB_ROLL_PTR

7

Pointer to the undo log record for rollback.

DB_ROW_ID

6

Hidden monotonic row identifier.

Transaction Commit and Purge

Undo logs are classified as insert undo log (generated by INSERT) and update undo log (generated by UPDATE/DELETE). Insert undo logs can be discarded immediately after commit, while update undo logs are placed on the history list for later purge.

During commit InnoDB sets the transaction state to COMMIT and marks associated undo logs as completed. Depending on size and type, the undo log may be cached, freed, or scheduled for purge:

If the undo log occupies a single page and its header is less than 3/4 full, it is added to the undo cache list (state TRX_UNDO_CACHED).

If it is an insert undo log, its state becomes TRX_UNDO_TO_FREE.

Otherwise it is marked TRX_UNDO_TO_PURGE for the purge thread.

History List

The history list links undo logs in commit order; the purge thread scans the list, removes obsolete logs, and skips logs still referenced by active transactions.

Cleanup

DELETE and UPDATE do not physically remove rows immediately; they set a delete flag in the B+‑tree. The purge thread later removes rows whose delete flag is set, ensuring that other transactions that may still need the old version are not affected.

In MySQL 5.7 temporary‑table undo logs never write redo records, while ordinary table undo logs do.

References

MySQL 5.7 Reference Manual: https://dev.mysql.com/doc/refman/5.7/en/

MySQL InnoDB undo log walkthrough: http://mysql.taobao.org/monthly/2015/04/01/

High Performance MySQL (3rd Edition)

MySQL技术内幕‑InnoDB存储引擎