Rechercher dans le manuel MySQL
25.39.29 The INFORMATION_SCHEMA INNODB_TRX Table
The INNODB_TRX
table provides
information about every transaction (excluding read-only
transactions) 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 15.14.2.1, “Using InnoDB Transaction and Locking Information”.
The INNODB_TRX
table has these
columns:
TRX_ID
A unique transaction ID number, internal to
InnoDB
. These IDs are not created for transactions that are read only and nonlocking. For details, see Section 8.5.3, “Optimizing InnoDB Read-Only Transactions”.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 roll back. Transactions that have changed nontransactional tables are considered heavier than others, regardless of the number of altered and locked rows.TRX_STATE
The transaction execution state. Permitted values are
RUNNING
,LOCK WAIT
,ROLLING BACK
, andCOMMITTING
.TRX_STARTED
The transaction start time.
TRX_REQUESTED_LOCK_ID
The ID of the lock the transaction is currently waiting for, if
TRX_STATE
isLOCK WAIT
; otherwiseNULL
. To obtain details about the lock, join this column with theENGINE_LOCK_ID
column of the Performance Schemadata_locks
table.TRX_WAIT_STARTED
The time when the transaction started waiting on the lock, if
TRX_STATE
isLOCK WAIT
; otherwiseNULL
.TRX_MYSQL_THREAD_ID
The MySQL thread ID. To obtain details about the thread, join this column with the
ID
column of theINFORMATION_SCHEMA
PROCESSLIST
table, but see Section 15.14.2.3, “Persistence and Consistency of InnoDB Transaction and Locking Information”.TRX_QUERY
The SQL statement that is being executed by the transaction.
TRX_OPERATION_STATE
The transaction's current operation, if any; otherwise
NULL
.TRX_TABLES_IN_USE
The number of
InnoDB
tables used while processing the current SQL statement of this transaction.TRX_TABLES_LOCKED
The number of
InnoDB
tables 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.)TRX_LOCK_STRUCTS
The number of locks reserved by the transaction.
TRX_LOCK_MEMORY_BYTES
The total size taken up by the lock structures of this transaction in memory.
TRX_ROWS_LOCKED
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.
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
system variable.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. For example, they might be turned off during a bulk data load.
TRX_FOREIGN_KEY_CHECKS
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.
TRX_LAST_FOREIGN_KEY_ERROR
The detailed error message for the last foreign key error, if any; otherwise
NULL
.TRX_ADAPTIVE_HASH_LATCHED
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 by
innodb_adaptive_hash_index_parts
, which is set to 8 by default.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 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
innodb_adaptive_hash_index_parts
), the value remains 0.TRX_IS_READ_ONLY
A value of 1 indicates the transaction is read only.
TRX_AUTOCOMMIT_NON_LOCKING
A value of 1 indicates the transaction is a
SELECT
statement that does not use theFOR UPDATE
orLOCK IN SHARED MODE
clauses, and is executing withautocommit
enabled so that the transaction contains only this one statement. When this column andTRX_IS_READ_ONLY
are both 1,InnoDB
optimizes the transaction to reduce the overhead associated with transactions that change table data.
Example
- *************************** 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_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
Notes
Use this table to help diagnose performance problems that occur during times of heavy concurrent load. Its contents are updated as described in Section 15.14.2.3, “Persistence and Consistency of InnoDB Transaction and Locking Information”.
You must have the
PROCESS
privilege to query this table.Use the
INFORMATION_SCHEMA
COLUMNS
table or theSHOW COLUMNS
statement to view additional information about the columns of this table, including data types and default values.
Document created the 26/06/2006, last modified the 26/10/2018
Source of the printed document:https://www.gaudry.be/en/mysql-rf-innodb-trx-table.html
The infobrol is a personal site whose content is my sole responsibility. The text is available under CreativeCommons license (BY-NC-SA). More info on the terms of use and the author.
References
These references and links indicate documents consulted during the writing of this page, or which may provide additional information, but the authors of these sources can not be held responsible for the content of this page.
The author This site is solely responsible for the way in which the various concepts, and the freedoms that are taken with the reference works, are presented here. Remember that you must cross multiple source information to reduce the risk of errors.