21.29.4 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.

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 rollback.
Transactions that have changed non-transactional tables
are considered heavier than others, regardless of the
number of altered and locked rows.

TRX_STATE

Transaction execution state. One of RUNNING,
LOCK WAIT, ROLLING
BACK or COMMITTING.

TRX_STARTED

Transaction start time.

TRX_REQUESTED_LOCK_ID

ID 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_STARTED

Time when the transaction started waiting on the lock (if
TRX_STATE is LOCK
WAIT, otherwise NULL).

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

TRX_TABLES_LOCKED

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

Total size taken up by the lock structures of this transaction in
memory.

TRX_ROWS_LOCKED

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 option.

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.
(They might be turned off during a bulk data load, for
example.)

TRX_FOREIGN_KEY_CHECKS

Whether 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_ERROR

Detailed error message for last FK error, or NULL.

TRX_ADAPTIVE_HASH_LATCHED

Whether 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_TIMEOUT

Whether 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.

TRX_IS_READ_ONLY

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

TRX_AUTOCOMMIT_NON_LOCKING

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.