InnoDB Undo Logs and Read Views: Constructing Point-In-Time Snapshots

A data analyst runs an export script on a production database at midnight:

START TRANSACTION WITH CONSISTENT SNAPSHOT;
SELECT * FROM audit_logs; -- Exports 500,000,000 rows slowly over 4 hours

The script runs for four hours without throwing errors.

However, during those four hours, disk alerts fire across the infrastructure team. Disk usage in the InnoDB Undo Tablespace (undo_001) spikes from 2 Gigabytes to 180 Gigabytes, threatening to fill physical host storage.

Why did a read-only SELECT query cause database disk space to explode by 178 Gigabytes?

To understand this incident, we must investigate how InnoDB manages Undo Log Segments and the background Purge Thread Engine.

---

1. The Dual Purpose of Undo Logs

When a transaction modifies or inserts a row, InnoDB writes the historical delta state into an Undo Log Segment.

Undo logs serve two critical database functions:

1. Transaction Abort Recovery (Atomicity): If a transaction executes a ROLLBACK or encounters a server crash, InnoDB reads undo records to revert data pages back to their pre-transaction values.
2. Multi-Version Snapshot Reads (Isolation): When a concurrent transaction executes a non-blocking SELECT, it reads undo log records to reconstruct historical row versions matching its Read View snapshot.

---

2. Insert Undo Logs vs Update Undo Logs

InnoDB distinguishes between two distinct types of Undo Logs:

                      +---------------------------------------+
                      |          INNODB UNDO LOGS             |
                      +---------------------------------------+
                                          |
                   +----------------------+----------------------+
                   |                                             |
                   v                                             v
       [ Insert Undo Logs ]                        [ Update Undo Logs ]
  - Generated during `INSERT`                 - Generated during `UPDATE` & `DELETE`
  - Used ONLY for transaction ROLLBACK        - Used for ROLLBACK + MVCC Reads
  - **Discarded immediately upon COMMIT**     - **Preserved in Undo History List**
                                                until old Read Views release

A. Insert Undo Logs

When a new record is inserted into a table, no prior version of that row exists.

• The Undo Log only stores the primary key value necessary to execute a matching DELETE if the inserting transaction rolls back.
• Once the inserting transaction issues a COMMIT, no concurrent snapshot read will ever need to look at a pre-insert version of that row.
• Result: InnoDB purges and reclaims Insert Undo Logs immediately upon commit.

B. Update Undo Logs

When a record is modified or deleted (UPDATE / DELETE):

• The Undo Log stores the previous values of modified columns and the DB_ROLL_PTR of the prior version.
• Deleting a row does not remove bytes from disk immediately; it sets a deleted_flag bit on the record header (Marked for Deletion).
• Even after the updating transaction commits, older active Read Views may still require access to the historical row state.
• Result: Update Undo Logs cannot be discarded upon commit. They are appended to the system Undo History List (history_len).

---

3. The History List Length & Purge Thread Engine

The engine maintains a global counter called the History List Length (history_len), which tracks the total count of un-purged undo log pages containing committed updates.

Background worker threads called Purge Threads (innodb_purge_threads) continuously scan the Undo History List to perform cleanup tasks:

1. Physical Delete Purging: Removes records marked with deleted_flag from B+ Tree data pages once no active Read View needs them.
2. Undo Log Space Recycling: Frees physical pages in Undo Tablespaces for reuse.

Undo History List:
[ Undo Page (Trx 100) ] ---> [ Undo Page (Trx 101) ] ---> [ Undo Page (Trx 102) ]
          ^
          |
  Purge Thread Pointer (purge_sys.purge_trx_id)

The Purge Thread advances through the list sequentially, freeing pages up to the min_trx_id of the oldest active Read View in the database system.

---

4. Why Long-Running Transactions Cause Undo Space Bloat

Now we can diagnose why the 4-hour reporting script caused a 180GB disk space spike.

When the analyst ran START TRANSACTION WITH CONSISTENT SNAPSHOT at midnight (Transaction ID 105):

• InnoDB generated a Read View with min_trx_id = 105.
• For the next 4 hours, that Read View remained active in memory.

During those 4 hours, high-volume online application services executed millions of UPDATE and DELETE operations (Transactions 106 through 500,000).

As each application transaction committed, its Update Undo Logs were appended to the Undo History List.

       [ ReadView Trx 105 ] (Stuck open for 4 hours!)
                |
                v
Undo List: [Trx 105] -> [Trx 106] -> [Trx 107] -> ... -> [Trx 500,000]
                ^
                |
        Purge Thread (BLOCKED!)
        Cannot purge any undo record > Trx 105!

The Chain Reaction:

1. The Purge Thread inspects the oldest active Read View (Trx 105) and halts advancement at Trx 105.
2. Millions of committed update undo records created by Transactions 106 to 500,000 cannot be purged or reclaimed.
3. The Undo History List grows to millions of entries.
4. InnoDB is forced to allocate hundreds of new 16KB pages in disk files (undo_001, undo_002) to store accumulating undo records.
5. Secondary index data pages accumulate millions of un-purged deleted_flag records, causing index fragmentation and slowing down query execution across the entire database.

---

Best Practices for Undo Space Management

1. Avoid Long-Running Read Transactions: Break massive data export scripts into small, paginated batch transactions (WHERE id BETWEEN x AND y).
2. Monitor History List Length: Alert when SHOW ENGINE INNODB STATUS shows History list length exceeding 100,000 entries.
3. Configure Truncate Flags: Enable innodb_undo_log_truncate = ON (default in MySQL 8.0) to allow InnoDB to shrink undo tablespaces automatically once long transactions release.

---

Summary & Next Steps

InnoDB Undo Logs balance transactional rollback requirements against point-in-time snapshot isolation:

• Insert Undo Logs are reclaimed immediately upon transaction commit.
• Update Undo Logs form historical version chains and must be retained until all active Read Views referencing their transaction IDs complete.
• Purge Threads recycle undo pages and physically delete soft-deleted records.
• Long-running transactions freeze the Purge Thread pointer, causing massive undo tablespace disk bloat and secondary index fragmentation.

In the next article, we transition to Module 6 and compare MySQL vs PostgreSQL MVCC: Heap Tuple Versions vs Undo Log Segments.

References & Further Reading

1. Ongaro, D., & Ousterhout, J. (2014). In Search of an Understandable Consensus Algorithm (Raft). USENIX Annual Technical Conference (ATC), 305–319.
2. Lamport, L. (1998). The Part-Time Parliament (Paxos). ACM Transactions on Computer Systems (TOCS), 16(2), 133–169.
3. Kleppmann, M. (2017). Designing Data-Intensive Applications (Chapter 5: Replication). O’Reilly Media.

---