Decoding the InnoDB TRANSACTIONS Section
The TRANSACTIONS section is the most operationally critical part of SHOW ENGINE INNODB STATUS output for diagnosing live production issues. It begins with three header lines — the transaction ID counter (indicating overall TPS), the purge state (showing MVCC health and History List Length), and the current transaction count. Below the header, every active transaction is listed with its state, age, associated MySQL thread ID, and optionally its current query and lock details. For incident responders, the TRANSACTIONS section answers the three most common emergency questions: (1) Is there a transaction that has been running for an unusually long time? (2) Is there a transaction stuck in LOCK WAIT or ROLLING BACK? (3) Is the purge thread keeping pace with write throughput? This guide decodes every line of the TRANSACTIONS section.
Transaction Counter and Purge State
Trx id counter and Monotonic Growth Rate
The "Trx id counter N" line shows the next available transaction ID — a monotonically increasing 64-bit integer that InnoDB allocates to every read-write transaction. The rate of increase in Trx id counter between two SHOW ENGINE INNODB STATUS captures equals the TPS rate. A server processing 1,000 TPS will advance the Trx id counter by roughly 60,000 between two captures 60 seconds apart. Read-only transactions optimised by InnoDB (those that do not acquire any row locks) may not consume a transaction ID at all in MySQL 8.0, which can make the Trx id counter advance more slowly than the actual query rate. Compare Trx id counter rate against Com_insert + Com_update + Com_delete from SHOW GLOBAL STATUS to cross-validate the TPS measurement.
Purge Done for Trx n:o and Running State Interpretation
The "Purge done for trx's n:o < N undo n:o < M" line shows the oldest transaction ID whose undo records have been fully processed by the purge thread. The gap between N and the current Trx id counter value is approximately the History List Length — the number of undo log record versions awaiting purge. "undo n:o < M" is an internal InnoDB undo log sequence number, less directly interpretable but useful for tracking purge progress over time. The "History list length K" line that follows directly reports the HLL as a clean integer. A running state of "running but idle" in the purge state indicates the purge thread is caught up and waiting for new work — healthy. "running" without "idle" during high write activity means purge is actively processing a backlog.
Active Transaction List Analysis
ACTIVE vs LOCK WAIT vs ROLLING BACK States
Each transaction in the TRANSACTIONS section shows one of several states: "ACTIVE N sec" means the transaction has been running for N seconds and is currently executing or has executed at least one statement. "ACTIVE N sec starting index read" or "ACTIVE N sec fetching rows" provides more detail about what the transaction is currently doing. "LOCK WAIT N lock struct(s)" means the transaction is blocked waiting for a row lock held by another transaction. "ROLLING BACK N lock struct(s)" means InnoDB is undoing the transaction's changes. "not started" means the session is in a transaction context (BEGIN executed) but no DML has been issued yet — an idle-in-transaction state. Alert on transactions with state "ACTIVE" for more than 60 seconds and on any "LOCK WAIT" transaction exceeding innodb_lock_wait_timeout.
MySQL Thread, OS Thread, and Query Association
Each transaction block in the TRANSACTIONS section includes "MySQL thread id N, OS thread handle M, query id P host user" followed optionally by the SQL text of the current or most recently executed query. The MySQL thread id corresponds directly to the connection ID visible in SHOW PROCESSLIST and information_schema.PROCESSLIST — use KILL N with this ID to immediately terminate the connection and roll back its transaction. The OS thread handle is the pthread ID, useful for correlating with OS-level thread profiling tools like perf or gdb. The host and user fields identify which application user and host originated the transaction, enabling rapid attribution during an incident: "which service is holding this long-running transaction?"
Lock Details Within the Transactions Section
TABLE LOCK and ROW LOCK Display Format
When a transaction holds or waits for locks, SHOW ENGINE INNODB STATUS displays them in the transaction block. TABLE LOCK entries appear as "TABLE LOCK table `db`.`table` trx id N lock mode M" where M is the intention lock mode (IS for shared, IX for exclusive). Row lock details appear as "RECORD LOCKS space id N page no M n bits K index `idx_name` of table `db`.`table` trx id P lock mode S/X [GAP] [NOT GAP] [INSERT INTENTION]". The "lock mode S" / "lock mode X" designation followed by optional qualifiers (GAP, NOT GAP, INSERT INTENTION) uniquely identifies the lock type. Counting "lock struct(s)" in the transaction header gives the total number of lock structures (page-level groupings), while counting individual RECORD LOCKS entries gives the exact row lock count.
Heap No, Physical Record, and Index Name in Lock Lines
Below each RECORD LOCKS header, SHOW ENGINE INNODB STATUS prints the physical record data for each locked row. The format is "heap no N PHYSICAL RECORD: n_fields M; compact format; info bits K" followed by a hex dump of the field values. "heap no N" is the record's position within the page's heap (physical page layout), not the row's logical position in the index. The field values in the hex dump correspond to the index columns in their stored binary encoding (big-endian integers, variable-length strings with length prefixes). For primary key indexes, the first field is the primary key value; for secondary indexes, the fields are the secondary index key columns followed by the primary key value. Decoding these hex dumps lets you identify exactly which primary key values are locked — essential for correlating locks with application data.