Mastering MySQL MVCC: How Multi-Version Concurrency Control Works in Real Interviews

In a mock interview, the candidate explains MySQL transaction fundamentals, describing ACID properties (Atomicity, Isolation, Durability) and how they differ from consistency, then introduces Multi-Version Concurrency Control (MVCC) and its role in implementing the Read Committed and Repeatable Read isolation levels.

MVCC works by storing multiple versions of a row; each modification creates an undo log entry containing the previous version. Readers can access older versions without acquiring locks, improving concurrency.

When a transaction inserts a row, InnoDB records the transaction ID ( trx_id) and a roll_pointer to the undo log. The undo log entry type TRX_UNDO_INSERT_REC stores the reverse operation, allowing rollback.

Updates generate TRX_UNDO_UPD_EXIST_REC entries that remain until no active transaction needs the older version, forming a version chain.

The read view used by MVCC contains four elements: creator_trx_id, the set of active transaction IDs ( m_ids), the minimum active ID ( min_trx_id), and the next transaction ID to be assigned ( max_trx_id). A version is visible if its trx_id matches the creator, is less than min_trx_id, is not in m_ids, or is greater than or equal to max_trx_id.

If trx_id == creator_trx_id, the version is visible.

If trx_id < min_trx_id, the modifying transaction committed before the read view and is visible.

If trx_id is in m_ids, the transaction is still active and the version is invisible.

If trx_id >= max_trx_id, the transaction started after the read view and is invisible.

Examples illustrate how Read Committed generates a new read view for each SELECT, causing different results in successive queries, while Repeatable Read reuses the same read view, yielding consistent results within a transaction.

Read Committed Isolation Level Example

Transaction 1 has committed, transaction 5 is active, and transaction 6 (the newest) is about to update a row. A SELECT on id 1 first sees the latest version (trx_id 5), which is still active, so it follows the roll_pointer to the previous version (trx_id 1) that is committed, returning the value "XX".

After transaction 5 commits, the same SELECT generates a new read view; now the latest version (trx_id 5) is visible, returning "NO".

Repeatable Read Isolation Level Example

The first SELECT creates a read view that includes active transactions 5 and 6. Even after transaction 5 commits, the read view remains unchanged, so a second SELECT still sees the older version and returns "XX", demonstrating repeatable read.

Additional details cover how InnoDB stores undo logs for insert, update, and delete operations, how undo logs are reclaimed after commit, and how a purge thread eventually removes truly deleted rows.