10bet网址
MySQL 8.0 Reference Manual
Related Documentation Download this Manual Excerpts from this Manual

26.4.30 The INFORMATION_SCHEMA INNODB_TRX Table

TheINNODB_TRXtable provides information about every transaction currently executing insideInnoDB, 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, seeSection 15.15.2.1, “Using InnoDB Transaction and Locking Information”.

TheINNODB_TRXtable has these columns:

  • TRX_ID

    A unique transaction ID number, internal toInnoDB. These IDs are not created for transactions that are read only and nonlocking. For details, seeSection 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,InnoDBselects the transaction with the smallest weight as thevictimto roll back. Transactions that have changed nontransactional tables are considered heavier than others, regardless of the number of altered and locked rows.

  • TRX_STATE

    事务执行状态。Permitted values areRUNNING,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, ifTRX_STATEisLOCK WAIT; otherwiseNULL. To obtain details about the lock, join this column with theENGINE_LOCK_IDcolumn of the Performance Schemadata_lockstable.

  • TRX_WAIT_STARTED

    The time when the transaction started waiting on the lock, ifTRX_STATEisLOCK WAIT; otherwiseNULL.

  • TRX_MYSQL_THREAD_ID

    The MySQL thread ID. To obtain details about the thread, join this column with theIDcolumn of theINFORMATION_SCHEMAPROCESSLISTtable, but seeSection 15.15.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; otherwiseNULL.

  • TRX_TABLES_IN_USE

    The number ofInnoDBtables used while processing the current SQL statement of this transaction.

  • TRX_TABLES_LOCKED

    The number ofInnoDBtables 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 theinnodb_concurrency_ticketssystem 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; otherwiseNULL.

  • 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 byinnodb_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 byinnodb_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 aSELECTstatement that does not use theFOR UPDATEorLOCK IN SHARED MODEclauses, and is executing withautocommitenabled so that the transaction contains only this one statement. When this column andTRX_IS_READ_ONLYare both 1,InnoDBoptimizes the transaction to reduce the overhead associated with transactions that change table data.

  • TRX_SCHEDULE_WEIGHT

    The transaction schedule weight assigned by the Contention-Aware Transaction Scheduling (CATS) algorithm to transactions waiting for a lock. The value is relative to the values of other transactions. A higher value has a greater weight. A value is computed only for transactions in aLOCK WAITstate, as reported by theTRX_STATEcolumn. A NULL value is reported for transactions that are not waiting for a lock. TheTRX_SCHEDULE_WEIGHTvalue is different from theTRX_WEIGHTvalue, which is computed by a different algorithm for a different purpose.

Example

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 trx_schedule_weight: NULL

Notes