INNODB_TRX table contains information about
every transaction (excluding read-only transactions) currently
InnoDB, including whether the
transaction is waiting for a lock, when the transaction started,
and the SQL statement the transaction is executing, if any.
Table 24.29 INNODB_TRX Columns
|Unique transaction ID number, internal to |
|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,
|Transaction execution state. Permitted values are
|Transaction start time.|
|ID of the lock the transaction is currently waiting for, if
|Time when the transaction started waiting on the lock, if
|MySQL thread ID. To obtain details about the thread, join this column
with the |
|The SQL statement that is being executed by the transaction.|
|The transaction's current operation, if any; otherwise
|The number of |
|Number of |
|The number of locks reserved by the transaction.|
|Total size taken up by the lock structures of this transaction in memory.|
|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.|
|The number of modified and inserted rows in this transaction.|
|A value indicating how much work the current transaction can do before
being swapped out, as specified by the
|The isolation level of the current transaction.|
|Whether unique checks are turned on or off for the current transaction. For example, they might be turned off during a bulk data load.|
|Whether foreign key checks are turned on or off for the current transaction. For example, they might be turned off during a bulk data load.|
|Detailed error message for the last foreign key error, if any; otherwise
|Whether the adaptive hash index is locked by the current transaction.
When the adaptive hash index search system is partitioned,
a single transaction does not lock the entire adaptive
hash index. Adaptive hash index partitioning is controlled
|Whether to relinquish the search latch immediately for the adaptive hash
index, or reserve it across calls from MySQL. When there
is no adaptive hash index 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. When the adaptive hash index search system is
partitioned (controlled by
|A value of 1 indicates the transaction is read only.|
|A value of 1 indicates the transaction is a
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX\G *************************** 1. row *************************** trx_id: 1510 trx_state: RUNNING trx_started: 2014-11-19 13:24:40 trx_requested_lock_id: NULL trx_wait_started: NULL trx_weight: 586739 trx_mysql_thread_id: 2 trx_query: DELETE FROM employees.salaries WHERE salary > 65000 trx_operation_state: updating or deleting trx_tables_in_use: 1 trx_tables_locked: 1 trx_lock_structs: 3003 trx_lock_memory_bytes: 450768 trx_rows_locked: 1407513 trx_rows_modified: 583736 trx_concurrency_tickets: 0 trx_isolation_level: REPEATABLE READ trx_unique_checks: 1 trx_foreign_key_checks: 1 trx_last_foreign_key_error: NULL trx_adaptive_hash_latched: 0 trx_adaptive_hash_timeout: 10000 trx_is_read_only: 0 trx_autocommit_non_locking: 0
Use this table to help diagnose performance problems that occur during times of heavy concurrent load. Its contents are updated as described in Section 126.96.36.199, “Persistence and Consistency of InnoDB Transaction and Locking Information”.
You must have the
PROCESSprivilege to query this table.
For usage information, see Section 188.8.131.52, “Using InnoDB Transaction and Locking Information”.