MySQL找出鎖等待

1.服務器級別的鎖等待

可以通過show processlist看到等待鎖的線程id，但是無法知道究竟哪個線程持有鎖

可以通過mysqladmin debug

相關等待鎖的線程以及誰持有鎖可以在錯誤日志中找到

2.存儲引擎層的鎖等待則比較麻煩，以下是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_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 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\G

3.如果因為線程在一個事務中空閑而正在遭受大量的鎖操作，下面查詢顯示存儲引擎層有多少查詢被哪些線程阻塞。

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\G

參考資料

<<高性能mysql第三版>>