The INNODB_TRX table contains
information about every transaction currently executing inside
InnoDB, including whether the transaction is
waiting for a lock, when the transaction started, and the SQL
statement the transaction is executing.
Table 20.3. INNODB_TRX Columns
| Column name | Description |
|---|---|
TRX_ID | Unique transaction ID number, internal to InnoDB. |
TRX_WEIGHT | The weight of a transaction, reflecting (but not necessarily the exact
count of) the number of rows altered and the number of rows
locked by the transaction. To resolve a deadlock,
InnoDB selects the transaction with the
smallest weight as the “victim” to rollback.
Transactions that have changed non-transactional tables are
considered heavier than others, regardless of the number of
altered and locked rows. |
TRX_STATE | Transaction execution state. One of RUNNING,
LOCK WAIT, ROLLING BACK
or COMMITTING. |
TRX_STARTED | Transaction start time. |
TRX_REQUESTED_LOCK_ID | ID of the lock the transaction is currently waiting for (if
TRX_STATE is LOCK WAIT,
otherwise NULL). Details about the lock can
be found by joining with INNODB_LOCKS on
LOCK_ID. |
TRX_WAIT_STARTED | Time when the transaction started waiting on the lock (if
TRX_STATE is LOCK WAIT,
otherwise NULL). |
TRX_MYSQL_THREAD_ID | MySQL thread ID. Can be used for joining with
PROCESSLIST on ID. See
Section 13.7.6.3.3, “Possible Inconsistency with PROCESSLIST”. |
TRX_QUERY | The SQL query that is being executed by the transaction. |
TRX_OPERATION_STATE | The transaction's current operation, or NULL. |
TRX_TABLES_IN_USE | The number of InnoDB tables used while processing the current SQL statement of this transaction. |
TRX_TABLES_LOCKED | Number of InnoDB tables that currently have any locks. (Because these are row locks, not table locks, the tables can usually still be read from and written to by multiple transactions, despite some rows being locked.) |
TRX_LOCK_STRUCTS | The number of locks reserved by the transaction. |
TRX_LOCK_MEMORY_BYTES | Total size taken up by the lock structures of this transaction in memory. |
TRX_ROWS_LOCKED | Approximate number or rows locked by this transaction. The value might include delete-marked rows that are physically present but not visible to the transaction. |
TRX_ROWS_MODIFIED | The number of modified and inserted rows in this transaction. |
TRX_CONCURRENCY_TICKETS | A value indicating how much work the current transaction can do before
being swapped out, as specified by the
innodb_concurrency_tickets option. |
TRX_ISOLATION_LEVEL | The isolation level of the current transaction. |
TRX_UNIQUE_CHECKS | Whether unique checks are turned on or off for the current transaction. (They might be turned off during a bulk data load, for example.) |
TRX_FOREIGN_KEY_CHECKS | Whether foreign key checks are turned on or off for the current transaction. (They might be turned off during a bulk data load, for example.) |
TRX_LAST_FOREIGN_KEY_ERROR | Detailed error message for last FK error, or NULL. |
TRX_ADAPTIVE_HASH_LATCHED | Whether or not the adaptive hash index is locked by the current transaction. (Only a single transaction at a time can modify the adaptive hash index.) |
TRX_ADAPTIVE_HASH_TIMEOUT | Whether to relinquish the search latch immediately for the adaptive hash index, or reserve it across calls from MySQL. When there is no AHI contention, this value remains zero and statements reserve the latch until they finish. During times of contention, it counts down to zero, and statements release the latch immediately after each row lookup. |
Notes:
Use this table to help diagnose performance problems that occur during times of heavy concurrent load. For usage information, see Section 13.7.6.2.4, “Using the Transaction Information Schema Tables”.