INNODB_TRX table provides
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, if any.
For usage information, see Section 188.8.131.52, “Using InnoDB Transaction and Locking Information”.
INNODB_TRX table has these
A unique transaction ID number, internal to
InnoDB. (Starting in MySQL 5.6, these IDs are not created for transactions that are read only and nonlocking. See Optimizing InnoDB Read-Only Transactions for details.)
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,
InnoDBselects the transaction with the smallest weight as the “victim” to roll back. Transactions that have changed nontransactional tables are considered heavier than others, regardless of the number of altered and locked rows.
The transaction execution state. Permitted values are
ROLLING BACK, and
The transaction start time.
The ID of the lock the transaction is currently waiting for, if
LOCK WAIT; otherwise
NULL. To obtain details about the lock, join this column with the
LOCK_IDcolumn of the
The time when the transaction started waiting on the lock, if
LOCK WAIT; otherwise
The MySQL thread ID. To obtain details about the thread, join this column with the
IDcolumn of the
PROCESSLISTtable, but see Section 184.108.40.206, “Persistence and Consistency of InnoDB Transaction and Locking Information”.
The SQL statement that is being executed by the transaction.
The transaction's current operation, if any; otherwise
The number of
InnoDBtables used while processing the current SQL statement of this transaction.
The number of
InnoDBtables that the current SQL statement has row locks on. (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.)
The number of locks reserved by the transaction.
The total size taken up by the lock structures of this transaction in memory.
The 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.
The detailed error message for the last foreign key error, if any; otherwise
Whether the adaptive hash index is locked by the current transaction. (Only a single transaction at a time can modify the adaptive hash index.)
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.
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX\G *************************** 1. row *************************** trx_id: 3B7 trx_state: RUNNING trx_started: 2014-11-19 14:33:45 trx_requested_lock_id: NULL trx_wait_started: NULL trx_weight: 1 trx_mysql_thread_id: 2 trx_query: SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX trx_operation_state: NULL trx_tables_in_use: 0 trx_tables_locked: 0 trx_lock_structs: 1 trx_lock_memory_bytes: 376 trx_rows_locked: 0 trx_rows_modified: 0 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 *************************** 2. row *************************** trx_id: 3B6 trx_state: RUNNING trx_started: 2014-11-19 14:32:38 trx_requested_lock_id: NULL trx_wait_started: NULL trx_weight: 94055 trx_mysql_thread_id: 1 trx_query: DELETE FROM employees.salaries WHERE salary > 75000 trx_operation_state: updating or deleting trx_tables_in_use: 1 trx_tables_locked: 1 trx_lock_structs: 841 trx_lock_memory_bytes: 129464 trx_rows_locked: 392752 trx_rows_modified: 93214 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
Use this table to help diagnose performance problems that occur during times of heavy concurrent load. Its contents are updated as described in Section 220.127.116.11, “Persistence and Consistency of InnoDB Transaction and Locking Information”.
You must have the
PROCESSprivilege to query this table.