innodb_transactions
NAME
innodb_transactions: Listing of open (InnoDB Plugin) transactionsTYPE
ViewDESCRIPTION
innodb_transactions is a simplification of INFORMATION_SCHEMA.INNODB_TRX.
CHANGE: up to common_schema 1.1, this view would only show transactions that are executing statements. It now lists all open transactions, and offers the trx_idle_seconds columns to help finding sleeping open transactions.
The connection calling upon this view is never listed.
STRUCTURE
mysql> DESC common_schema.innodb_transactions; +-----------------------+---------------------+------+-----+---------------------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------------+---------------------+------+-----+---------------------+-------+ | trx_id | varchar(18) | NO | | | | | trx_state | varchar(13) | NO | | | | | trx_started | datetime | NO | | 0000-00-00 00:00:00 | | | trx_requested_lock_id | varchar(81) | YES | | NULL | | | trx_wait_started | datetime | YES | | NULL | | | trx_weight | bigint(21) unsigned | NO | | 0 | | | trx_mysql_thread_id | bigint(21) unsigned | NO | | 0 | | | trx_query | varchar(1024) | YES | | NULL | | | INFO | longtext | YES | | NULL | | | trx_runtime_seconds | bigint(21) | YES | | NULL | | | trx_wait_seconds | bigint(21) | YES | | NULL | | | trx_idle_seconds | bigint(11) | YES | | NULL | | | sql_kill_query | varbinary(31) | NO | | | | | sql_kill_connection | varbinary(25) | NO | | | | +-----------------------+---------------------+------+-----+---------------------+-------+
SYNOPSIS
Structure of this view derives from that of INFORMATION_SCHEMA.INNODB_TRX table.
Additional columns are:
- INFO: Query being executed right now by this transaction, as seen on PROCESSLIST.
- trx_runtime_seconds: number of seconds elapsed since beginning of this transaction.
- trx_wait_seconds: number of seconds this transaction is waiting on lock, or NULL if not currently waiting.
- trx_idle_seconds: number of seconds this transaction is idle. 0 if not idle.
-
sql_kill_query: a KILL QUERY statement for current thread.
Use with eval() to apply statement. -
sql_kill_connection: a KILL statement for current thread.
Use with eval() to apply statement.
EXAMPLES
Show all active transactions:
In the above no transaction is waiting and no transaction is idle.mysql> SELECT * FROM common_schema.innodb_transactions; +-----------+-----------+---------------------+-----------------------+------------------+------------+---------------------+-----------+-----------------------------------------------------------------------------------+---------------------+------------------+------------------+-------------------+---------------------+ | trx_id | trx_state | trx_started | trx_requested_lock_id | trx_wait_started | trx_weight | trx_mysql_thread_id | trx_query | INFO | trx_runtime_seconds | trx_wait_seconds | trx_idle_seconds | sql_kill_query | sql_kill_connection | +-----------+-----------+---------------------+-----------------------+------------------+------------+---------------------+-----------+-----------------------------------------------------------------------------------+---------------------+------------------+------------------+-------------------+---------------------+ | 9AA6213B4 | RUNNING | 2012-09-27 15:46:36 | NULL | NULL | 13 | 858223 | NULL | DELETE FROM tbl_lock WHERE id = 'planner' AND expiryTime < '2012-09-27 15:46:36' | 0 | NULL | 0 | KILL QUERY 858223 | KILL 858223 | | 9AA6213B2 | RUNNING | 2012-09-27 15:46:36 | NULL | NULL | 3 | 858216 | NULL | NULL | 0 | NULL | 0 | KILL QUERY 858216 | KILL 858216 | | 9AA6213B2 | RUNNING | 2012-09-27 15:46:36 | NULL | NULL | 3 | 858219 | NULL | UPDATE tbl_scount SET count = count + 1 WHERE element='php' | 0 | NULL | 0 | KILL QUERY 858219 | KILL 858219 | +-----------+-----------+---------------------+-----------------------+------------------+------------+---------------------+-----------+-----------------------------------------------------------------------------------+---------------------+------------------+------------------+-------------------+---------------------+
Kill transactions idle for 30 seconds or more:
mysql> CALL eval("SELECT sql_kill_query FROM common_schema.innodb_transactions WHERE trx_idle_seconds >= 30");