Find locks in MySQL

In MySQL sometimes one transaction blocks another. The tables that contain information about InnoDB transactions and data locks enable you to determine which transaction is waiting for another, and which resource is being requested. Table used to check locks is information_schema.

We can get locks information using following two methods:

Method1: Using the SHOW FULL PROCESSLIST;

SHOW FULL PROCESSLIST;

mysql> SHOW FULL PROCESSLIST; +----+------+-----------+-------+---------+------+----------+-----------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+-------+---------+------+----------+-----------------------+ | 26 | root | localhost | mysql | Query | 0 | starting | SHOW FULL PROCESSLIST | +----+------+-----------+-------+---------+------+----------+-----------------------+

This will show you a list of all current processes, their SQL query and state. Now usually if a single query is causing many others to lock then it should be easy to identify. The affected queries will have a status of Locked and the offending query will be sitting out by itself, possibly waiting for something intensive, like a temporary table.

Method2: Running inner join on the table information_schema table

SELECT PL.id ,PL.user ,PL.state ,IT.trx_id ,IT.trx_mysql_thread_id ,IT.trx_query AS query ,IT.trx_id AS blocking_trx_id ,IT.trx_mysql_thread_id AS blocking_thread ,IT.trx_query AS blocking_query FROM information_schema.processlist AS PL INNER JOIN information_schema.innodb_trx AS IT ON PL.id = IT.trx_mysql_thread_id INNER JOIN information_schema.innodb_lock_waITs AS ILW ON IT.trx_id = ILW.requesting_trx_id AND IT.trx_id = ILW.blocking_trx_id

Responses are currently closed, but you can trackback from your own site.

Comments are closed.

Powered by k2schools