1. Check if a lock exists. You’re looking for “TRX HAS BEEN WAITING * SEC FOR THIS LOCK TO BE GRANTED.” or “WAITING FOR THIS LOCK TO BE GRANTED:”
    • mysql -e’SHOW ENGINE INNODB STATUS \G’
  2. Get additional lock information from INFORMATION_SCHEMA
    • mysql -e’select * from INFORMATION_SCHEMA.innodb_lock_waits\G’
    • blocking_trx_id is the ID of the transaction that holds the lock
    • requesting_trx_id is the ID of the ‘hanging’ transaction
  3. Once you have the ID of the transaction holding the lock find out which query it relates to
    • mysql -e’select * from INFORMATION_SCHEMA.innodb_trx\G’
    • The trx_id you obtained in step 2 will correlate with the trx_mysql_thread_id
  4. Now that you have the mysql_thread_id you can run ‘show processlist’ and find the query that is locking the DB
    • mysql -e’SHOW PROCESSLIST\G’

Alternatively (if slow logs are enabled)

  1. Show queries that have been locking the table for 10+ seconds:
    • grep -B1 -A3 “Lock_time: [1-9].*\.” /var/lib/*slow.log

Leave a Reply

Your email address will not be published. Required fields are marked *