MySQL Redo Log vs Undo Log: Key Differences

This article explores the core architectural differences between MySQL’s redo logs and undo logs within the InnoDB storage engine. While both logs are essential for maintaining database transactions and preventing data corruption, they serve fundamentally opposite purposes. Redo logs ensure durability by preserving write operations during system crashes, whereas undo logs support transaction rollbacks and Multi-Version Concurrency Control (MVCC) by keeping track of previous data states.

What is a Redo Log?

The redo log is a disk-based data structure used during crash recovery to correct data written by incomplete transactions. It enforces the Durability property of ACID (Atomicity, Consistency, Isolation, Durability) transactions.

Architecture and Write Mechanism

• Write-Ahead Logging (WAL): InnoDB uses the WAL protocol. When a transaction modifies data, the changes are first written to the in-memory redo log buffer and then flushed to the redo log files on disk before the actual data pages in the buffer pool are updated on disk.
• Physical Logging: Redo logs record physical changes at the storage page level. They contain low-level instructions representing exactly which bytes in which data pages were modified.
• Storage Structure: Redo logs are stored as a set of fixed-size files (usually named ib_logfile0 and ib_logfile1) written in a circular fashion. Once the log writer reaches the end of the last file, it wraps around to the beginning, overwriting old data that has already been safely flushed to the main database tablespace.

What is an Undo Log?

The undo log is a collection of log records associated with a single transaction. It is designed to roll back transactions and support consistent reads, fulfilling the Atomicity and Isolation aspects of ACID.

Architecture and Write Mechanism

• Logical Logging: Unlike the physical nature of redo logs, undo logs are logical. They store instructions on how to reverse a transaction. For example, if a transaction performs an INSERT, the undo log records a corresponding DELETE. If a transaction performs an UPDATE, the undo log stores the old values of the modified columns.
• Multi-Version Concurrency Control (MVCC): Undo logs allow other transactions to read the consistent, older state of modified rows without locking them. When a query requests a row currently being modified, InnoDB uses the undo log to reconstruct the older version of the record.
• Storage Structure: Undo logs are stored within undo segments located in undo tablespaces. Unlike the circular, fixed-size design of redo logs, undo logs grow dynamically. They are only purged by background “purge threads” when they are no longer needed by any active transactions for rollback or MVCC purposes.

Architectural Comparison

To clearly understand how these two log systems compare, we can examine their distinct technical attributes:

How Redo and Undo Logs Work Together

When a transaction executes in MySQL, both logs work in tandem to ensure system stability.

For instance, when a row is updated: 1. InnoDB writes the old state of the row to the undo log (to allow rollback or read-consistency). 2. The changes to the data page and the undo log page are written to the redo log (to ensure these modifications survive a crash). 3. The changes are made in the buffer pool. 4. When the transaction commits, the redo log buffer is flushed to disk, guaranteeing the transaction is permanent. 5. If the system crashes before the modified data pages reach the disk, MySQL uses the redo log to reconstruct the state. If a transaction was interrupted and never committed, MySQL uses the undo log to revert those partial changes.