innodb_plugin_views
SYNOPSIS
InnoDB Plugin views: informational views on InnoDB Plugin.
- innodb_locked_transactions: List locked transactions, the locks they are waiting on and the transactions holding those locks.
- innodb_simple_locks: Listing of locks, simplifying INFORMATION_SCHEMA.INNODB_LOCKS
- innodb_transactions: Listing of active (InnoDB Plugin) transactions, which are currently performing queries
- innodb_transactions_summary: A one line summary of InnoDB's transactions: count, state, locks
DESCRIPTION
All views in this category require InnoDB Plugin to be installed along with all related INFORMATION_SCHEMA plugins.
If you are using MySQL >= 5.5, then you are using InnoDB plugin (and it is simply refered to as plain InnoDB).
If you are using MySQL 5.1 then you are either using the "old", built-in InnoDB, or, upon proper configuration, the InnoDB Plugin. See this page from the manual for InnoDB Plugin setup for MySQL 5.1.
EXAMPLES
Show info on locked transactions:
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 | +---------------+---------------------+-------------------------+----------------------------+--------------------------------------------------+-------------------------+-------------------------+----------------+---------------------+--------------------------+-----------------------------+-------------------+------------------+-------------------------+------------------------------+
Kill transactions blocking cause 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');
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");