InnoDB MV2PL在READ COMMITTED级别下为何出现不可重复读？源码是否有判断？

Great question—this cuts to how InnoDB balances MVCC (Multi-Version Concurrency Control) and two-phase locking (2PL) across isolation levels. Let’s break this down clearly:

1. How REPEATABLE READ (Default) Prevents Non-Repeatable Reads #

InnoDB’s MV2PL relies on consistent read views (snapshots of the database’s state) to enforce isolation. For the REPEATABLE READ isolation level:

• Your transaction creates a single consistent read view the first time it executes a SELECT statement (or explicitly starts with START TRANSACTION WITH CONSISTENT SNAPSHOT).
• Every subsequent SELECT in the same transaction reuses this snapshot. It only sees data committed before the snapshot was created—even if other transactions modify and commit those rows later.
• This "stuck" snapshot is why you don’t get non-repeatable reads: the same query will always pull the same version of rows, regardless of external changes.

2. Why READ COMMITTED Lets Non-Repeatable Reads Slip In #

The difference boils down to when consistent read views are created:

• For READ COMMITTED, InnoDB creates a new consistent read view every single time you run a SELECT statement.
• Each new snapshot reflects the latest committed state of the database. So if another transaction modifies and commits a row between two identical SELECTs in your transaction, the second SELECT will pick up the new version of that row.
• This is intentional—READ COMMITTED prioritizes seeing the latest committed data over repeatable reads, which aligns with the SQL standard for that isolation level. MV2PL’s core mechanics (versioning + locking) are still at play, but the visibility rules for the isolation level override the repeatability guarantee.

3. Is There Isolation Level Logic in InnoDB’s Source Code? #

Absolutely—InnoDB has explicit checks for transaction isolation levels to control view creation and version filtering. Here’s a high-level look at where this happens:

• In functions like trx_get_read_view() (part of the transaction subsystem), there’s a branch based on trx->isolation_level:
  • If the level is TRX_ISO_REPEATABLE_READ, the function checks if a view already exists for the transaction. If yes, it reuses it; if not, it creates one and stores it for future reads.
  • If the level is TRX_ISO_READ_COMMITTED, the function discards any existing view and creates a fresh one each time a read is executed.
• Additionally, in the version filtering logic (like in row_vers_build_for_consistent_read()), the isolation level determines which row versions are considered visible. For READ COMMITTED, it only filters out uncommitted versions from other transactions, while REPEATABLE READ also filters out versions committed after the initial snapshot.

In short: MV2PL doesn’t "fail" in READ COMMITTED—the isolation level’s rules simply adjust how MVCC’s snapshotting works, intentionally allowing non-repeatable reads as per the SQL standard.

内容的提问来源于stack exchange，提问作者Kyungmin Kim