Hi,
If you want to monitor locks for InnoDB tables, Manually you can check “show engine innodb status” as well as below tables of information_schema.
information_schema.innodb_trx
information_schema.innodb_locks
information_schema.innodb_lock_waits
i.e you can check for transactions in “LOCK WAIT” state and check for how long it’s locked using timestamp in trx_wait_started of information_schema.innodb_trx table.
More information explained here.
http://dev.mysql.com/doc/refman/5.5/…trx-table.html
http://dev.mysql.com/doc/refman/5.5/…cks-table.html
http://dev.mysql.com/doc/refman/5.5/…its-table.html
Below query will help you to find out the blocked and blocking transactions.
SELECT requesting_trx_id, r.trx_mysql_thread_id as requesting_process_id,
r.trx_started as requesting_trx_started, r.trx_query as requesting_trx_query,
r.trx_wait_started as requesting_wait_started,
blocking_trx_id, b.trx_mysql_thread_id as blocking_process_id,
b.trx_started as blocking_trx_started, b.trx_query as blocking_trx_query,
b.trx_wait_started as blocking_wait_started
FROM information_schema.INNODB_LOCK_WAITS
INNER JOIN information_schema.INNODB_TRX as r on requesting_trx_id=r.trx_id
INNER JOIN information_schema.INNODB_TRX as b on blocking_trx_id=b.trx_id;
If you want to monitor it by some monitoring tool like with Cacti or Nagios, you can use Percona Monitoring Plugins, explained here:
http://www.percona.com/doc/percona-m…templates.html
http://www.percona.com/doc/percona-m…ql-innodb.html