Purge, History List, Long Transactions, MVCC Cleanup, and Storage Health

Learning outcomes

MVCC lets readers see consistent row versions while writers continue to commit. That flexibility has a cost: InnoDB cannot discard an old version if an active read view may still need it. A forgotten transaction can therefore turn ordinary writes into growing undo history even when the transaction itself is “only reading.”

Create a small MVCC target

USE servicehub_innodb_lab;DROP TABLE IF EXISTS mvcc_health_demo;CREATE TABLE mvcc_health_demo (  id INT NOT NULL,  status VARCHAR(20) NOT NULL,  version_no INT NOT NULL,  detail VARCHAR(200) NOT NULL,  PRIMARY KEY(id)) ENGINE=InnoDB;INSERT INTO mvcc_health_demo VALUES (1,'open',0,'initial state');

Open three mysql sessions: A holds a snapshot, B generates committed versions, and C observes. Keeping observation separate prevents your diagnostic query from accidentally changing the teaching transaction.

Session A: hold a consistent snapshot open

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;START TRANSACTION WITH CONSISTENT SNAPSHOT;SELECT CONNECTION_ID() AS session_a, id,status,version_no,detailFROM mvcc_health_demo WHERE id=1;-- Keep this transaction open; do not COMMIT yet.

Session A’s read view must continue to produce a result consistent with its snapshot. It does not need to block Session B’s ordinary update to achieve that. Instead, InnoDB retains sufficient undo history so A can reconstruct the older version.

Session B: create committed history

USE servicehub_innodb_lab;UPDATE mvcc_health_demo SET version_no=version_no+1, detail='writer version 1' WHERE id=1;COMMIT;UPDATE mvcc_health_demo SET version_no=version_no+1, detail='writer version 2' WHERE id=1;COMMIT;UPDATE mvcc_health_demo SET version_no=version_no+1, detail='writer version 3' WHERE id=1;COMMIT;SELECT * FROM mvcc_health_demo WHERE id=1;

Session B sees the newest committed version. Return to Session A and issue the same SELECT: under its existing REPEATABLE READ snapshot it should continue to see the older snapshot-compatible version. That difference is MVCC, not replication lag or a stale client cache.

SELECT id,status,version_no,detailFROM mvcc_health_demo WHERE id=1;

Session C: find the transaction holding history alive

SELECT TRX_MYSQL_THREAD_ID, TRX_ID, TRX_STATE, TRX_STARTED,       TIMESTAMPDIFF(SECOND,TRX_STARTED,NOW()) AS age_seconds,       TRX_ISOLATION_LEVEL, TRX_ROWS_MODIFIED, TRX_QUERYFROM information_schema.INNODB_TRXORDER BY TRX_STARTED;SHOW ENGINE INNODB STATUS\G

A read-only, nonlocking transaction may not always receive a conventional transaction ID immediately, but the diagnostic views can still reveal relevant active transaction/read-view state depending on execution. In SHOW ENGINE INNODB STATUS, locate the TRANSACTIONS section and its History list length. Do not memorize one “safe” number: workload write rate and duration matter. The important pattern is sustained growth that correlates with old transactions or snapshots.

What purge does—and what it does not do

Purge processes undo history for committed changes after those versions are no longer required for rollback or MVCC visibility. It can physically remove delete-marked records and free undo history for reuse. Purge is not the same as:

The history list can grow under a write-heavy workload even without a single pathological transaction; the production signal is growth rate, persistence, transaction age, undo/tablespace pressure, and application behavior together.

Release the snapshot and watch cleanup become possible

COMMIT;SELECT @@autocommit AS autocommit_after_commit;

Once Session A ends, its old read view no longer needs those earlier versions. Purge is asynchronous, so history-list length may not drop to a particular value immediately. Re-check after a short interval while no other old transaction is holding history:

SELECT NOW(3) AS captured_at;SELECT TRX_MYSQL_THREAD_ID,TRX_STARTED,TRX_STATE,TRX_ROWS_MODIFIEDFROM information_schema.INNODB_TRXORDER BY TRX_STARTED;SHOW ENGINE INNODB STATUS\G

Failure drill: the “idle” transaction that is not harmless

An application checks out a pooled connection, runs START TRANSACTION, performs one SELECT, then waits for user input or an external API call for ten minutes. The database session may appear idle at the SQL layer, but its transaction/read view can remain open and preserve old versions while unrelated writers keep updating rows.

Killing a suspicious production session is not the first diagnostic step. Identify the owning application, capture transaction age/query/account/host evidence, determine whether it is safe to abort, and understand rollback cost for write-heavy transactions.

Observe purge and undo configuration without cargo-cult tuning

SHOW VARIABLES WHERE Variable_name IN ('innodb_purge_threads','innodb_purge_batch_size',  'innodb_max_purge_lag','innodb_max_purge_lag_delay',  'innodb_undo_log_truncate','innodb_max_undo_log_size');SELECT NAME, COUNT, STATUS, COMMENTFROM information_schema.INNODB_METRICSWHERE NAME LIKE '%purge%' OR NAME LIKE '%undo%'ORDER BY NAME;

Do not immediately raise purge threads or set purge-lag delays because the history list is high once. If an hour-old transaction is holding a read view, adding background capacity does not remove the correctness requirement to preserve versions that transaction can still see. Fix the transaction lifecycle first.

Storage-health runbook

Long readers and long writers create different kinds of pressure

A long read transaction can retain an old read view without changing rows itself. That primarily affects MVCC cleanup: newer writers can commit, but their superseded versions may need to remain reconstructable. A long write transaction can add a second class of cost: it can retain locks, accumulate its own undo, increase rollback work, and still contribute to history pressure after it commits. Transaction age is therefore useful, but you also need rows modified, lock state, and application purpose.

Purge cleanup and tablespace truncation are different

Purge makes obsolete row versions and delete-marked records no longer needed by MVCC. Undo tablespaces have their own truncation/reuse behavior controlled by separate mechanisms such as innodb_undo_log_truncate. A user table’s file-per-table .ibd file does not necessarily shrink just because purge removed obsolete records; free space may remain reusable inside the tablespace. Treat “history is cleaned” and “filesystem bytes decreased” as separate acceptance criteria.

Monitoring should detect trends, not worship one threshold

There is no portable magic History list length that means “incident.” A write-heavy server may naturally have a higher steady-state value than a lightly used application. Useful alerts combine sustained upward trend, transaction age, undo/disk growth, DML throughput, purge activity, and user-visible latency. The goal is to catch a cleanup system that is falling behind or being blocked, not to force every server to the same number.

Chapter 07 summary and bridge to index engineering

You now have one connected InnoDB mental model. Tables and indexes live in tablespaces organized into pages and allocation structures. One clustered index stores each table’s rows, while secondary indexes carry the clustered key. The buffer pool caches those pages and accumulates dirty state. Redo protects durable changes across crashes; undo supports rollback and older read views; doublewrite protects page integrity; checkpoints bound redo reuse/recovery work; purge removes history only when MVCC no longer needs it.

Chapter 08 turns that mechanism-based foundation into Index Engineering and Access Path Design: selectivity, composite-key ordering, covering indexes, functional/multi-valued/full-text/spatial indexes, and how to design an index portfolio from workload evidence rather than intuition.

Authoritative references

• MySQL 8.4 Reference Manual — InnoDB Multi-Versioning
• MySQL 8.4 Reference Manual — Purge Configuration
• MySQL 8.4 Reference Manual — INNODB_TRX
• MySQL 8.4 Reference Manual — InnoDB Standard Monitor
• MySQL 8.4 Reference Manual — Undo Tablespaces