A simple MySQL monitor for detecting InnoDB X-Locks

To monitor blocking transactions and lock waits on MySQL I use the following simple script:

root@my-db-master:~[1400]#cat monitor-x-locks.sh
#!/bin/sh
######################
# MySQL Monitoring script
# Use me when you need to log MySQL txns and detect x-locs
#####################
DBPASS=mypass

while true
do
 date +'%Y-%m-%d %H:%M:%S,%3N' >> locks.txt
 date +'%Y-%m-%d %H:%M:%S,%3N' >> lock_waits.txt
 date +'%Y-%m-%d %H:%M:%S,%3N' >> tables.txt
 echo "select * from information_schema.INNODB_LOCK_WAITS " | mysql -u root -p$DBPASS >> lock_waits.txt
 echo "select lock_id,Lock_trx_id,lock_mode,lock_table
        from information_schema.INNODB_LOCKS where lock_mode='X'" | mysql -u root -p$DBPASS >>  tables.txt
 echo "select r.trx_id waiting_trx_id,
       r.trx_mysql_thread_id waiting_thread,
       r.trx_query waiting_query,
       b.trx_id blocking_trx_id,
       b.trx_mysql_thread_id blocking_thread,
       b.trx_query blocking_query
   from information_schema.innodb_lock_waits w
   inner join information_schema.innodb_trx b  on
    b.trx_id = w.blocking_trx_id
   inner join information_schema.innodb_trx r  on
    r.trx_id = w.requesting_trx_id;"  | mysql -u root -p$DBPASS >>  locks.txt
 sleep 1 ;
done

This script creates three files and updates them with a timestamp every second.  Those files are:

locks.txt : shows which locs occurred by showing the transaction ID, thread ID from the blocked transaction and  transaction ID, thread ID from the blocking transaction

tables.txt Shows which transactions have placed an exclusive lock

lock_waits.txt: Shows transactions waiting for a lock over a table

 Use it as follows:

root@my-db-master:~[1402]#./monitor-x-locks.sh &
[1] 23184
root@my-db-master:~[1403]#fg
./monitor-x-locks.sh
^C
root@my-db-master:~[1396]#ls -l
total 60
-rw-r--r--  1 root root   216 Nov  5 20:39 locks.txt
-rw-r--r--  1 root root   216 Nov  5 20:39 lock_waits.txt
-rwxr--r--  1 root root  1230 Nov  5 20:38 monitor-x-locks.sh
-rw-r--r--  1 root root   216 Nov  5 20:39 tables.txt