innodb_locked_transactions
NAME
innodb_locked_transactions: List locked transactions, the locks they are waiting on and the transactions holding those locks.TYPE
ViewDESCRIPTION
InnoDB Plugin provides with easy to query INFORMATION_SCHEMA tables. In particular, it offers information on running transactions, locked transactions and locks.
innodb_locked_transactions makes the obvious connection for blocked transactions: it lists blocked transactions, what they attempt to do, the locks on which they block, the transactions holding those locks and the queries they are executing.
The view makes for a simple analysis of "Why do I seem to have so many locks? What's locking what?"
STRUCTURE
mysql> DESC common_schema.innodb_locked_transactions; +------------------------------+---------------------+------+-----+---------------------+-------+ | Field | Type | Null | Key | Default | Extra | +------------------------------+---------------------+------+-----+---------------------+-------+ | locked_trx_id | varchar(18) | NO | | | | | locked_trx_started | datetime | NO | | 0000-00-00 00:00:00 | | | locked_trx_wait_started | datetime | YES | | NULL | | | locked_trx_mysql_thread_id | bigint(21) unsigned | NO | | 0 | | | locked_trx_query | varchar(1024) | YES | | NULL | | | requested_lock_id | varchar(81) | NO | | | | | blocking_lock_id | varchar(81) | NO | | | | | locking_trx_id | varchar(18) | NO | | | | | locking_trx_started | datetime | NO | | 0000-00-00 00:00:00 | | | locking_trx_wait_started | datetime | YES | | NULL | | | locking_trx_mysql_thread_id | bigint(21) unsigned | NO | | 0 | | | locking_trx_query | varchar(1024) | YES | | NULL | | | trx_wait_seconds | bigint(21) | YES | | NULL | | | sql_kill_blocking_query | varbinary(31) | NO | | | | | sql_kill_blocking_connection | varbinary(25) | NO | | | | +------------------------------+---------------------+------+-----+---------------------+-------+
SYNOPSIS
Columns of this view:
- locked_trx_id: InnoDB locked transaction ID
- locked_trx_started: time at which locked transaction started
- locked_trx_wait_started: time at which locked transaction got blocked on this lock
- locked_trx_mysql_thread_id: thread ID (mapped to PROCESSLIST)
- locked_trx_query: current blocked query
- requested_lock_id: ID of lock on which transaction is blocked
- blocking_lock_id: ID of lock preventing transaction from getting requested lock
- locking_trx_id: InnoDB blocking transaction ID
- locking_trx_started: time at which blocking transaction started
- locking_trx_wait_started: time at which blocking transaction got blocked (it, too, may be blocked)
- locking_trx_mysql_thread_id: blocking thread ID (mapped to PROCESSLIST)
- locking_trx_query: current blocking query
- trx_wait_seconds: number of seconds blocked transaction is waiting
-
sql_kill_blocking_query: a KILL QUERY statement for the blocking transaction.
Use with eval() to apply statement. -
sql_kill_blocking_connection: a KILL statement for the blocking transaction.
Use with eval() to apply statement.
EXAMPLES
Show info on locked transactions:
In the above example we are unable to catch the query blocking the 2rd transaction. In the first transaction we are also unable to realize the blocked query. We don't always get all we want...mysql> SELECT * FROM common_schema.innodb_locked_transactions; +---------------+---------------------+-------------------------+----------------------------+--------------------------------------------------+-------------------------+-------------------------+----------------+---------------------+--------------------------+-----------------------------+-------------------+------------------+-------------------------+------------------------------+ | locked_trx_id | locked_trx_started | locked_trx_wait_started | locked_trx_mysql_thread_id | locked_trx_query | requested_lock_id | blocking_lock_id | locking_trx_id | locking_trx_started | locking_trx_wait_started | locking_trx_mysql_thread_id | locking_trx_query | trx_wait_seconds | sql_kill_blocking_query | sql_kill_blocking_connection | +---------------+---------------------+-------------------------+----------------------------+--------------------------------------------------+-------------------------+-------------------------+----------------+---------------------+--------------------------+-----------------------------+-------------------+------------------+-------------------------+------------------------------+ | 9AD2D1811 | 2012-09-28 10:40:25 | 2012-09-28 10:40:25 | 609205 | NULL | 9AD2D1811:499850:82:113 | 9AD2D0E1A:499850:82:113 | 9AD2D0E1A | 2012-09-28 10:40:01 | NULL | 609159 | NULL | 1 | KILL QUERY 609159 | KILL 609159 | | 9AD2D0FBA | 2012-09-28 10:40:03 | 2012-09-28 10:40:03 | 609196 | UPDATE events SET ts = NOW() WHERE alias = 'all' | 9AD2D0FBA:499850:88:108 | 9AD2D0E1A:499850:88:108 | 9AD2D0E1A | 2012-09-28 10:40:01 | NULL | 609159 | NULL | 23 | KILL QUERY 609159 | KILL 609159 | +---------------+---------------------+-------------------------+----------------------------+--------------------------------------------------+-------------------------+-------------------------+----------------+---------------------+--------------------------+-----------------------------+-------------------+------------------+-------------------------+------------------------------+
See which transactions are blocking, and how many are being blocked:
mysql> SELECT locking_trx_id, COUNT(*) FROM innodb_locked_transactions GROUP BY locking_trx_id; +----------------+----------+ | locking_trx_id | COUNT(*) | +----------------+----------+ | 9AD30296C | 2 | | 9AD30296E | 1 | +----------------+----------+
Kill transactions causing other transactions to block for 30 seconds or more:
mysql> CALL eval('SELECT sql_kill_blocking_query FROM innodb_locked_transactions WHERE trx_wait_seconds >= 30 GROUP BY sql_kill_blocking_query');