# How to monitor DB for locks?

**URL:** <https://forums.percona.com/t/how-to-monitor-db-for-locks/2751>\
**Category:** Other MySQL® Questions\
**Created:** [June 11, 2013, 7:08pm UTC](https://forums.percona.com/t/how-to-monitor-db-for-locks/2751 "2013-06-11T19:08:08Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![ylluminate](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/ylluminate/32/933_2.png) [@ylluminate](https://forums.percona.com/u/ylluminate)\
**Post date:** [June 11, 2013, 7:08pm UTC](https://forums.percona.com/t/how-to-monitor-db-for-locks/2751/1 "2013-06-11T19:08:08Z")

</div>

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 ([url][http://www.percona.com/doc/percona-server/5.5/diagnostics/innodb\_show\_status.html[/url]](http://www.percona.com/doc/percona-server/5.5/diagnostics/innodb_show_status.html%5B/url%5D)), but we’re not precisely sure how to use these properly to accomplish our goal.

---

<div class="post-metadata">

**Author:** ![niljoshi](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/niljoshi/32/16889_2.png) [@niljoshi](https://forums.percona.com/u/niljoshi)\
**Post date:** [June 12, 2013, 12:39am UTC](https://forums.percona.com/t/how-to-monitor-db-for-locks/2751/2 "2013-06-12T00:39:14Z")

</div>

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.

[URL=“[http://dev.mysql.com/doc/refman/5.5/en/innodb-trx-table.html](http://dev.mysql.com/doc/refman/5.5/en/innodb-trx-table.html)”][http://dev.mysql.com/doc/refman/5.5/...trx-table.html[/URL]](http://dev.mysql.com/doc/refman/5.5/...trx-table.html%5B/URL%5D)  
[URL=“[http://dev.mysql.com/doc/refman/5.5/en/innodb-locks-table.html](http://dev.mysql.com/doc/refman/5.5/en/innodb-locks-table.html)”][http://dev.mysql.com/doc/refman/5.5/...cks-table.html[/URL]](http://dev.mysql.com/doc/refman/5.5/...cks-table.html%5B/URL%5D)  
[URL=“[http://dev.mysql.com/doc/refman/5.5/en/innodb-lock-waits-table.html](http://dev.mysql.com/doc/refman/5.5/en/innodb-lock-waits-table.html)”][http://dev.mysql.com/doc/refman/5.5/...its-table.html[/URL]](http://dev.mysql.com/doc/refman/5.5/...its-table.html%5B/URL%5D)

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:

[URL=“[Percona Monitoring Plugins](http://www.percona.com/doc/percona-monitoring-plugins/cacti/mysql-templates.html)”][http://www.percona.com/doc/percona-m...templates.html[/URL]](http://www.percona.com/doc/percona-m...templates.html%5B/URL%5D)  
[URL=“[Percona Monitoring Plugins](http://www.percona.com/doc/percona-monitoring-plugins/nagios/pmp-check-mysql-innodb.html)”][http://www.percona.com/doc/percona-m...ql-innodb.html[/URL]](http://www.percona.com/doc/percona-m...ql-innodb.html%5B/URL%5D)