MySQL Undo Log

In MySQL, the Undo Log is the backbone that guarantees transactional atomicity, consistency, and durability. It captures every change a transaction makes so that the database can roll back, provide consistent snapshots, and recover after a crash. This article walks through how the Undo Log works, its internal layout, its role in MVCC, the life‑cycle of its records, and practical tuning tips for production workloads.
The Undo Log serves three essential functions:

• Rollback – When a transaction is aborted or a ROLLBACK is issued, the old values stored in the Undo Log are written back to the data pages, restoring the state to what it was before the transaction began.
• Multi‑Version Concurrency Control (MVCC) – Readers consult the Undo Log to reconstruct the snapshot of a row that existed at the start of their transaction. This enables isolation levels such as READ COMMITTED and REPEATABLE READ.
• Crash Recovery – After a crash, InnoDB replays the Redo Log to redo committed changes and then uses the Undo Log to undo any uncommitted modifications that were written to the buffer pool.

Undo Log entries can be grouped into two categories:

Internal Structure

InnoDB organizes Undo data into a hierarchy:

Undo Segment → Undo Page → Undo Record

Undo Segment

An Undo Segment is a logical container that holds a linked list of Undo Pages. Segments are allocated on demand and are reused by the purge thread once they become empty.

Undo Page

Each Undo Page stores a fixed number of Undo Records. Pages are identified by a unique page number and are linked together within a segment.

Undo Record

A typical record contains three parts:

+-------------------+-------------------+-------------------+
| Header            | Before Image      | Transaction Info |
+-------------------+-------------------+-------------------+

• Header – Metadata such as undo_type (INSERT/UPDATE/DELETE), trx_id (transaction ID), and undo_no (sequence number within the transaction).
• Before Image – The old value of the row, stored in a compressed form that only includes changed columns.
• Transaction Info – A pointer to the transaction control block, used to determine visibility.

Undo Segments are linked in insertion order, forming a single‑direction chain. Even after a transaction commits, its Undo Records stay in place until no active transaction can see them. At that point the purge thread removes them.

Interaction with MVCC

Visibility Rules

When a transaction reads a row, it compares the row’s metadata against its own snapshot:

• trx_id – The transaction that created the row.
• undo_no – The modification sequence number.
• undo_ptr – Pointer to the corresponding Undo Record.

A simplified visibility check looks like this:

-- snapshot_trx_id is the ID of the reading transaction
SELECT *
FROM t
WHERE
    (trx_id < snapshot_trx_id)                     -- row created before snapshot
    AND (undo_no IS NULL OR undo_no > snapshot_undo_no);  -- no later uncommitted changes

If the reading transaction’s snapshot is older than a modification, the Undo Log is traversed to recover the previous value, ensuring repeatable reads.

Example Scenario

-- Transaction A
START TRANSACTION;
UPDATE account SET balance = balance - 100 WHERE id = 1;  -- generates Undo A1

-- Transaction B
START TRANSACTION;
SELECT balance FROM account WHERE id = 1;                -- sees original balance

Because Transaction B’s snapshot predates Transaction A’s trx_id, it follows Undo A1 to fetch the pre‑update balance. Even after A commits, Undo A1 remains until all snapshots that might need it are gone.

Generation and Purge Lifecycle

Generation Flow

1. DML Analysis – The optimizer identifies the rows to be modified.
2. Write Undo – The old values are written to the appropriate Undo Segment before any data page changes.
3. Write Redo – A Redo Log record describing the new state is flushed to disk.
4. Update Data Page – The new values are written to the buffer pool and the page is marked dirty.

The Undo → Redo → Data order guarantees that a crash cannot leave the database in an inconsistent state.

Purge Thread

InnoDB spawns a background purge thread that periodically scans Undo Segments:

1. Identify Obsolete Undo – If every active transaction’s snapshot is newer than an Undo Record, the record is no longer needed.
2. Mark and Reclaim – The corresponding Undo Page is marked free and added to the segment’s free list for reuse.

The purge process is throttled by innodb_max_purge_lag to prevent a backlog of unpurged Undo when long transactions are active.

Practical Tuning Tips

• innodb_undo_logs – Increase this value in high‑concurrency environments to reduce contention for Undo Segments. The default is 2; production workloads may benefit from 4–8.
• Monitor undo_log_size – Use SHOW ENGINE INNODB STATUS to watch the amount of Undo space in use. A sudden spike can indicate long‑running transactions or a mis‑configured innodb_undo_logs.
• Avoid Long Transactions – Large, long‑running transactions hold many Undo Records, delaying purge and potentially exhausting disk space. Break them into smaller units or use a lower isolation level such as READ COMMITTED to reduce Undo pressure.
• Enable innodb_purge_threads – In write‑heavy deployments, increasing the number of purge threads can keep up with the generation rate of Undo Records.

Summary

The Undo Log is a critical component of InnoDB’s transaction engine. By recording the before‑image of every DML operation, it enables:

• Safe rollback of aborted or rolled‑back transactions.
• Consistent snapshot reads through MVCC.
• Reliable crash recovery in concert with the Redo Log.

Understanding its structure—Undo Segments, Pages, and Records—and its life‑cycle—from generation to purge—helps database administrators diagnose transaction anomalies and tune systems for high throughput and resilience.