1. Server-level lock waits
the thread ID of the wait lock can be seen through show processlist, but there is no way to know exactly which thread holds the lock
can be mysqladmin by debug
The associated thread waiting for the lock and who holds the lock can be found in the error log#以下是innodb存储引擎中锁等待以及哪个线程持有锁的查找sql
SELECT r.trx_id as waiting_trx_id, r.trx_mysql_thread_id as Waiting_thread, Timestampdiff (SECOND, r.trx_wait_started, Current_timestamp) as Wait_time, r.trx_query as Waiting_query, l.lock_table as Waiting_table_lock, b.trx_id as blocking_t rx_id, b.trx_mysql_thread_id as Blocking_thread, SUBSTRING (p.host,1, INSTR (P.host, ': ')-1) as Blocking_host, SUBSTRING ( P.host, INSTR (P.host, ': ') + 1) as Block_port, IF (p.command= "Sleep", p.time,0) as Idle_in_trx, b.trx_query as Blcoking_ Query
From Information_schema.innodb_lock_waits as W
INNER JOIN Information_schema.innodb_trx as B on b.trx_id=w.blocking_trx_id
INNER JOIN Information_schema.innodb_trx as r on r.trx_id = w.requesting_trx_id
INNER JOIN information_schema.innodb_locks as l on w.requested_lock_id = l.lock_id
Left JOIN information_schema.processlist as P on p.id = b.trx_mysql_thread_id
ORDER by Wait_time DESC;
#下面查询显示存储引擎层有多少查询被哪些线程阻塞
SELECT CONCAT (' thread ', b.trx_mysql_thread_id, ' from ', p.host) as Who_blocks, IF (P.command = "Sleep", P.time, 0) as Idle _in_trx, MAX (Timestampdiff (second,r.trx_wait_started, now)) as Max_wait_time, COUNT (*) as Num_waiters
From Information_schema.innodb_lock_waits as W
INNER JOIN Information_schema.innodb_trx as B on b.trx_id = w.blocking_trx_id
INNER JOIN Information_schema.innodb_trx as r on r.trx_id = w.requesting_trx_id
Left JOIN information_schema.processlist as P on p.id = b.trx_mysql_thread_id
GROUP by Who_blocks
ORDER by Num_waiters DESC;
MySQL Find lock wait
