How to monitor DB for locks?

[ylluminate]

I am needing to monitor a database for all locks that occur in a verbose manner. We are having some issues from a php application that is getting some errors and there is a chance that it is from (a) table(s) being locked.

How can we continually keep track of when tables are being locked and what query is locking them?

We are aware that you have introduced a couple of new variables that may be used for this (http://www.percona.com/doc/percona-server/5.5/diagnostics/innodb_show_status.html), but we’re not precisely sure how to use these properly to accomplish our goal.

[niljoshi]

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