How MySQL Uses MVCC to Manage Database Locking

This article explains how MySQL, specifically through its InnoDB storage engine, implements Multi-Version Concurrency Control (MVCC) to manage database locking. You will learn about the internal mechanisms of MVCC, including hidden row fields, undo logs, and read views, and how these components work together to allow high-concurrency read and write operations without blocking each other.

The Role of MVCC in MySQL

In traditional database systems, locking is used to prevent multiple transactions from modifying or reading the same data simultaneously, which can lead to performance bottlenecks. MySQL utilizes MVCC within the InnoDB storage engine to solve this issue.

MVCC allows point-in-time consistent reads. Instead of locking a row when a transaction wants to read it, InnoDB presents the transaction with a snapshot of the data as it existed at a specific point in time. This means that readers do not block writers, and writers do not block readers, drastically improving database throughput.

Hidden Column Metadata

To support MVCC, InnoDB automatically appends three hidden fields to every row in a table:

• DB_TRX_ID (6 bytes): This field records the transaction identifier of the last transaction that inserted or updated the row. Depletions are also treated internally as updates, where a special bit in the row header is set to mark it as deleted.
• DB_ROLL_PTR (7 bytes): This is the roll pointer. It points to a undo log record written to the rollback segment. If the row has been updated, the undo log contains the information necessary to rebuild the row to its prior state.
• DB_ROW_ID (6 bytes): A row ID that is used if the table does not have a primary key or unique index, serving as the clustered index key.

Undo Logs and Row Versioning

When a transaction modifies a row, InnoDB does not overwrite the old data permanently in place without a backup. Instead, it writes the current version of the row to the Undo Log before applying the change. The DB_ROLL_PTR of the new row is then pointed to this undo log entry.

If another transaction modifies the same row again, a chain of undo logs is created. This chain links the current active row back through all its previous versions. When a query needs to read an older version of the data, InnoDB traverses this undo log chain using the roll pointers to reconstruct the row as it existed at the required point in time.

Read Views and Transaction Isolation

The visibility of a row version is determined by a data structure called a Read View. A Read View defines which transaction IDs are visible to the current transaction. When a transaction starts a consistent read, InnoDB creates a Read View containing:

• A list of active (uncommitted) transaction IDs at that moment.
• The minimum transaction ID that is active.
• The next transaction ID to be assigned.

Using this information, InnoDB determines whether it should read the current state of a row or traverse the undo log to find an older version:

• Read Committed: A new Read View is created for every individual SELECT statement. This ensures the query sees only data committed before the statement started.
• Repeatable Read (Default): A single Read View is created when the first SELECT statement in the transaction runs. This same snapshot is used for all subsequent reads within that transaction, guaranteeing consistent results.

How MVCC Interacts with Locking

While MVCC handles read consistency without locks, MySQL still uses locks to manage write operations (inserts, updates, and deletes) and explicit locking reads (like SELECT ... FOR UPDATE or SELECT ... FOR SHARE).

When a write operation occurs, InnoDB obtains an exclusive record lock on the index record. Because readers use MVCC and read from historical snapshots (undo logs), they bypass these locks entirely. Locking is only triggered when two transactions attempt to modify the exact same row simultaneously, at which point the second transaction must wait for the first to commit or roll back.