Documentation Home
MySQL 5.6 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 31.5Mb
PDF (A4) - 31.6Mb
PDF (RPM) - 30.1Mb
EPUB - 7.7Mb
HTML Download (TGZ) - 7.5Mb
HTML Download (Zip) - 7.5Mb
HTML Download (RPM) - 6.5Mb
Eclipse Doc Plugin (TGZ) - 8.2Mb
Eclipse Doc Plugin (Zip) - 10.1Mb
Man Pages (TGZ) - 181.3Kb
Man Pages (Zip) - 292.5Kb
Info (Gzip) - 2.8Mb
Info (Zip) - 2.8Mb
Excerpts from this Manual

MySQL 5.6 Reference Manual  /  ...  /  The INFORMATION_SCHEMA INNODB_TRX Table


The INNODB_TRX table contains 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.

Table 21.4 INNODB_TRX Columns

Column nameDescription
TRX_IDUnique transaction ID number, internal to InnoDB. (Starting in MySQL 5.6, these IDs are not created for transactions that are read-only and non-locking. See Section 8.5.3, “Optimizing InnoDB Read-Only Transactions” for details.)
TRX_WEIGHTThe 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 rollback. Transactions that have changed non-transactional tables are considered heavier than others, regardless of the number of altered and locked rows.
TRX_STATETransaction execution state. One of RUNNING, LOCK WAIT, ROLLING BACK or COMMITTING.
TRX_STARTEDTransaction start time.
TRX_REQUESTED_LOCK_IDID of the lock the transaction is currently waiting for (if TRX_STATE is LOCK WAIT, otherwise NULL). Details about the lock can be found by joining with INNODB_LOCKS on LOCK_ID.
TRX_WAIT_STARTEDTime when the transaction started waiting on the lock (if TRX_STATE is LOCK WAIT, otherwise NULL).
TRX_MYSQL_THREAD_IDMySQL thread ID. Can be used for joining with PROCESSLIST on ID. See Section, “Potential Inconsistency with PROCESSLIST Data”.
TRX_QUERYThe SQL query that is being executed by the transaction.
TRX_OPERATION_STATEThe transaction's current operation, or NULL.
TRX_TABLES_IN_USEThe number of InnoDB tables used while processing the current SQL statement of this transaction.
TRX_TABLES_LOCKEDNumber 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_STRUCTSThe number of locks reserved by the transaction.
TRX_LOCK_MEMORY_BYTESTotal size taken up by the lock structures of this transaction in memory.
TRX_ROWS_LOCKEDApproximate 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_MODIFIEDThe number of modified and inserted rows in this transaction.
TRX_CONCURRENCY_TICKETSA value indicating how much work the current transaction can do before being swapped out, as specified by the innodb_concurrency_tickets option.
TRX_ISOLATION_LEVELThe isolation level of the current transaction.
TRX_UNIQUE_CHECKSWhether unique checks are turned on or off for the current transaction. (They might be turned off during a bulk data load, for example.)
TRX_FOREIGN_KEY_CHECKSWhether foreign key checks are turned on or off for the current transaction. (They might be turned off during a bulk data load, for example.)
TRX_LAST_FOREIGN_KEY_ERRORDetailed error message for last FK error, or NULL.
TRX_ADAPTIVE_HASH_LATCHEDWhether or not the adaptive hash index is locked by the current transaction. (Only a single transaction at a time can modify the adaptive hash index.)
TRX_ADAPTIVE_HASH_TIMEOUTWhether to relinquish the search latch immediately for the adaptive hash index, or reserve it across calls from MySQL. When there is no AHI 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.

A value of 1 indicates the transaction is read-only. (5.6.4 and up.)


A value of 1 indicates the transaction is a SELECT statement that does not use the FOR UPDATE or LOCK IN SHARED MODE clauses, and is executing with the autocommit setting turned on so that the transaction will only contain this one statement. (5.6.4 and up.) When this column and TRX_IS_READ_ONLY are both 1, InnoDB optimizes the transaction to reduce the overhead associated with transactions that change table data.


*************************** 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


User Comments
Sign Up Login You must be logged in to post a comment.