MySQL Find lock wait

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

2. The storage engine layer lock waits is troublesome, the following is the lock waits in the InnoDB storage engine and which thread holds the lock the lookup 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 BL ocking_trx_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 BLC Oking_query from Information_schema.innodb_lock_waits as W INNER joins Information_schema.innodb_trx as B on B.TRX_ID=W.BL ocking_trx_id INNER join Information_schema.innodb_trx as r on r.trx_id = w.requesting_trx_id INNER Join Information_schem A.innodb_locks as L on w.requested_lock_id = l.lock_id left joins Information_schema.processlist as P on p.id = B.trx_mysql _thread_id ORDER by Wait_time desc\g

3. The following query shows how many queries are blocked by the storage engine layer if the thread is being subjected to a large number of lock operations because it is idle in one transaction.
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 in NER 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\g

Source: http://www.cnblogs.com/gomysql/p/3608466.html

MySQL Find lock wait