The INNODB_TRX table indicates whether the transaction is waiting for a lock, when the transaction starts, the SQL statement that the transaction is executing (if any), etc. Currently executing inside InnoDB (except for read-only transactions) contains information about all transactions.
Unique transaction ID number within InnoDB. (As of Mysql5.6, these IDs are not created for read-only, non-locked transactions.)
Transaction weight. This reflects the number of rows changed by the transaction and the number of rows locked(but not necessarily exactly). To resolve deadlocks, InnoDB chooses the transaction with the lowest weight as the target to roll back. Transactions that have changed non-transactional tables are considered more heavily weighted than other transactions.
Transaction execution status. One of Running, lock wait, rolling back, or commiting.
Transaction start time.
Lock ID on which the transaction is currently waiting (if TRX_STATE is lock wait, otherwise NULL). Details about locks can be found by linking to INNODB_LOCKS in LOCK_ID.
The time when the transaction started waiting for the lock(if TRX_STATE is lock wait, otherwise null).
Mysql thread id. It can be used to combine with Processlist by id.
SQL query being executed by the transaction.
Transaction’s current operation or NULL.
Number of InnoDB tables used when processing the current SQL statement for this transaction.
The number of InnoDB tables for which the current SQL statement has a row lock.
Number of locks reserved in the transaction.
Total size used in memory by the lock structure for this transaction.
Approximate number of rows locked by this transaction. This value may include rows that are marked for deletion that physically exist but are not recognized by the transaction.
Number of rows changed and inserted in this transaction.
A value that indicates the amount of work that can be done in the current transaction before being swapped out, as specified by the innodb_concurrency_tickets option.
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX \G *************************** 1. row *************************** trx_id: 3298 trx_state: RUNNING trx_started: 2014-11-19 13:54:39 trx_requested_lock_id: NULL trx_wait_started: NULL trx_weight: 316436 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: 1621 trx_lock_memory_bytes: 243240 trx_rows_locked: 759343 trx_rows_modified: 314815 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
mysql> select * from information_schema.innodb_trx\G; Empty set (0.00 sec) ERROR: No query specified