How Long-Running Transactions Impact MySQL Undo Logs

Long-running transactions in MySQL can severely degrade database performance and stability by preventing the cleanup of the undo log. This article explains how open transactions force the InnoDB storage engine to retain historical data versions, leading to massive undo log accumulation, disk space exhaustion, increased I/O overhead, and a significant drop in overall query performance.

In MySQL’s InnoDB engine, the undo log is critical for Multi-Version Concurrency Control (MVCC). When a transaction modifies a row, the old version of the data is written to the undo log so that other concurrent transactions can read a consistent snapshot of the database. Once a transaction commits, its corresponding undo log records are normally marked for deletion.

However, the cleanup of these records is managed by a background process called the purge thread. The purge thread can only safely delete undo log records that are older than the oldest active read view in the system. If a single transaction remains open—whether it is actively running queries or simply sitting idle—the purge thread cannot remove any undo logs created after that transaction started.

This blockage leads to several critical system consequences:

1. Exponential Growth of History List Length (HLL)

The History List Length is a metric that tracks the number of unpurged undo log pages. When long-running transactions block the purge process, the HLL grows rapidly. A high HLL forces MySQL to traverse long, complex pointer chains in the undo log to reconstruct older versions of rows for read operations, causing a major slow down in select queries.

2. Disk Space Inflation and Bloat

Because undo logs cannot be purged, they continue to consume physical storage. In environments with auto-extending undo tablespaces, this can quickly fill up the disk. Furthermore, even after the long-running transaction finally commits and the undo logs are eventually purged, the physical disk space allocated to the undo tablespaces is not automatically returned to the operating system, requiring manual intervention to shrink them.

3. Buffer Pool Pollution and Cache Eviction

To traverse the long chains of undo records, InnoDB must load undo log pages into the buffer pool. This process evicts frequently accessed data and index pages from the memory cache, leading to increased physical disk I/O, lower cache hit ratios, and system-wide latency spikes for unrelated queries.

4. Transaction Throughput Bottlenecks

As the undo log subsystem struggles with resource exhaustion, write latency increases. In severe cases, MySQL may experience page allocation bottlenecks, slow checkpoints, and overall performance degradation, which can eventually lead to database hangs or unexpected crashes due to out-of-disk-space errors.