Undo Logs and MVCC

In this lesson, you will learn how MySQL’s InnoDB engine utilizes Undo Logs and Multi-Version Concurrency Control (MVCC) to ensure that long-running write operations never block your database reads.

The Conflict of Data Access

In traditional database systems, if one transaction is actively updating a row, other transactions wanting to read that row must wait for the update to finish. This leads to massive performance bottlenecks. InnoDB solves this by using MVCC, which allows multiple versions of a single row to coexist.

How Undo Logs Power MVCC

When you perform an UPDATE or DELETE on a row, InnoDB doesn't simply overwrite the existing data. Instead, it:

1. Records the old version of the data into an Undo Log.
2. Updates the actual data page with the new version.
3. Adds a hidden DB_ROLL_PTR (rollback pointer) to the row, which points back to the undo log entry containing the older version.

When a query arrives to read that row, InnoDB checks the query's start time (using a Transaction ID). If the record was modified by a transaction that started after the read query, InnoDB follows the DB_ROLL_PTR in the undo log to reconstruct the exact state of the row as it existed when the read query began.

A Concrete Illustration

Imagine two sessions:

• Session A (Update): Executes UPDATE users SET balance = 500 WHERE id = 1;
• Session B (Read): Executes SELECT balance FROM users WHERE id = 1;

While Session A is still running, Session B requests the balance. Because of the Undo Log, Session B doesn't see the "dirty" uncommitted value (500). Instead, it sees the original value (e.g., 200) retrieved from the undo logs.

-- Session A changes data; undo logs store the original version
START TRANSACTION;
UPDATE users SET balance = 500 WHERE id = 1;

-- Session B reads the same row simultaneously
-- InnoDB uses the undo log to show Session B the 200 balance, 
-- ignoring the pending change in Session A.
SELECT balance FROM users WHERE id = 1; 

By maintaining these "historical" versions, the database allows high-concurrency environments where readers can always access consistent data without waiting for writers to commit.

Key Takeaway: MVCC uses Undo Logs to store older row versions, enabling snapshot-consistent reads that remain completely independent of concurrent write locks.