Long-running transactions and history list length (purge lag)

Command

-- Undo records waiting for purge (millions = trouble)
SELECT NAME, COUNT AS history_list_length
FROM information_schema.INNODB_METRICS WHERE NAME = 'trx_rseg_history_len';

-- Oldest transactions, their owner and last SQL
SELECT trx.trx_id, trx.trx_mysql_thread_id AS conn_id, trx.trx_state,
       TIMESTAMPDIFF(SECOND, trx.trx_started, NOW()) AS age_sec,
       trx.trx_rows_modified, trx.trx_rows_locked, trx.trx_isolation_level,
       p.USER, p.HOST, p.COMMAND, p.TIME AS state_sec,
       LEFT(COALESCE(trx.trx_query, esc.SQL_TEXT), 80) AS current_or_last_sql
FROM information_schema.INNODB_TRX trx
JOIN performance_schema.processlist p ON p.ID = trx.trx_mysql_thread_id
LEFT JOIN performance_schema.threads th ON th.PROCESSLIST_ID = trx.trx_mysql_thread_id
LEFT JOIN performance_schema.events_statements_current esc ON esc.THREAD_ID = th.THREAD_ID
ORDER BY trx.trx_started
LIMIT 10;

What it does

Shows how far behind purge is (history list length) and the oldest transactions holding it back: owner, age, rows modified and the last SQL they ran.

When to use it

When the undo tablespace grows, when the same queries get slower over time (they scan old row versions), or on disk space alerts.

Caution

The most common cause is a session in Sleep with an open transaction, or a consistent snapshot forgotten under REPEATABLE READ (e.g. an abandoned mysqldump / report). After KILL, rollback takes as long as the rows the transaction changed; killing a transaction with a high trx_rows_modified means a long, IO-heavy rollback, and a restart does not shorten it. Undo is truncated automatically once purge catches up (innodb_undo_log_truncate=ON).