mysql 鎖分析相關命令

1.服務器級別的鎖等待

可以通過show （full） 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;

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;

查詢 information_schema.innodb_trx 看到有長時間未完成的事務， 使用 kill 命令終止該查詢。

select concat('kill ',i.trx_mysql_thread_id,';') from information_schema.innodb_trx i,(select id, time from information_schema.processlist where time = (select max(time) from information_schema.processlist where state = 'Waiting for table metadata lock' and substring(info, 1, 5) in ('alter' , 'optim', 'repai', 'lock ', 'drop ', 'creat'))) p where timestampdiff(second, i.trx_started, now()) > p.time and i.trx_mysql_thread_id  not in (connection_id(),p.id);

如果上面兩個檢查沒有發現，或者事務過多，建議使用下面的查詢將相關庫上的會話終止  

-- RDS for MySQL 5.6

select concat('kill ', a.owner_thread_id, ';') from information_schema.metadata_locks a left join (select b.owner_thread_id from information_schema.metadata_locks b, information_schema.metadata_locks c where b.owner_thread_id = c.owner_thread_id and b.lock_status = 'granted' and c.lock_status = 'pending') d ON a.owner_thread_id = d.owner_thread_id

where a.lock_status = 'granted' and d.owner_thread_id is null;

-- 請根據具體的情景修改查詢語句 -- 如果導致阻塞的語句的用戶與當前用戶不同，請使用導致阻塞的語句的用戶登錄來終止會話

-- RDS for MySQL 5.5

select concat('kill ', p1.id, ';') from information_schema.processlist p1,(select id, time from information_schema.processlist where time = (select max(time) from information_schema.processlist where state = 'Waiting for table metadata lock' and substring(info, 1, 5) in ('alter' , 'optim', 'repai', 'lock ', 'drop ', 'creat', 'trunc'))) p2

where p1.time >= p2.time and p1.command in ('Sleep' , 'Query') and p1.id not in (connection_id() , p2.id); -- RDS for MySQL 5.5 語句請根據具體的 DDL 語句情況修改查詢的條件； -- 如果導致阻塞的語句的用戶與當前用戶不同，請使用導致阻塞的語句的用戶登錄來終止會話

查看隔離機制

select @@session.tx_isolation

查看鎖阻塞

SELECT t3.trx_id waiting_trx_id,t3.trx_mysql_thread_id waiting_thread,t3.trx_query waiting_query,t2.trx_id blocking_trx_id,t2.trx_mysql_thread_id blocking_thread,t2.trx_query blocking_query

FROM information_schema.innodb_lock_waits t1,information_schema.innodb_trx t2,information_schema.innodb_trx t3 WHERE t1.blocking_trx_id = t2.trx_id AND t1.requesting_trx_id = t3.trx_id;

SELECT p2.`HOST` Blockedhost,p2.`USER` BlockedUser,r.trx_id BlockedTrxId,r.trx_mysql_thread_id BlockedThreadId,TIMESTAMPDIFF(SECOND,r.trx_wait_started,CURRENT_TIMESTAMP) WaitTime,r.trx_query BlockedQuery,l.lock_table BlockedTable,m.`lock_mode` BlockedLockMode,m.`lock_type` BlockedLockType,m.`lock_index` BlockedLockIndex,m.`lock_space` BlockedLockSpace,m.lock_page BlockedLockPage,m.lock_rec BlockedLockRec,m.lock_data BlockedLockData,p.`HOST` blocking_host,p.`USER` blocking_user,b.trx_id BlockingTrxid,b.trx_mysql_thread_id BlockingThreadId,b.trx_query BlockingQuery,l.`lock_mode` BlockingLockMode,l.`lock_type` BlockingLockType,l.`lock_index` BlockingLockIndex,l.`lock_space` BlockingLockSpace,l.lock_page BlockingLockPage,l.lock_rec BlockingLockRec,l.lock_data BlockingLockData,IF (p.COMMAND = 'Sleep', CONCAT(p.TIME,' seconds'), 0) idel_in_trx

FROM information_schema.INNODB_LOCK_WAITS w

INNER JOIN information_schema.INNODB_TRX b ON b.trx_id = w.blocking_trx_id

INNER JOIN information_schema.INNODB_TRX r ON r.trx_id = w.requesting_trx_id

INNER JOIN information_schema.INNODB_LOCKS l ON w.blocking_lock_id = l.lock_id  AND l.`lock_trx_id`=b.`trx_id`

INNER JOIN information_schema.INNODB_LOCKS m ON m.`lock_id`=w.`requested_lock_id` AND m.`lock_trx_id`=r.`trx_id`

INNER JOIN information_schema. PROCESSLIST p ON p.ID = b.trx_mysql_thread_id

INNER JOIN information_schema. PROCESSLIST p2 ON p2.ID = r.trx_mysql_thread_id

ORDER BY WaitTime DESC;

select l.* from (

select 'Blocker' role,p.id,p.user,left(p.host, locate(':', p.host) - 1) host,tx.trx_id,tx.trx_state,tx.trx_started,timestampdiff(second, tx.trx_started, now()) duration,lo.lock_mode,lo.lock_type,lo.lock_table,lo.lock_index,tx.trx_query,lw.requesting_thd_id Blockee_id,lw.requesting_trx_id Blockee_trx

from information_schema.innodb_trx tx,information_schema.innodb_lock_waits lw,information_schema.innodb_locks lo,information_schema.processlist p

where lw.blocking_trx_id = tx.trx_id and p.id = tx.trx_mysql_thread_id and lo.lock_id = lw.blocking_lock_id

union

select 'Blockee' role,p.id,p.user,left(p.host, locate(':', p.host) - 1) host,tx.trx_id,tx.trx_state,tx.trx_started,timestampdiff(second, tx.trx_started, now()) duration,lo.lock_mode,lo.lock_type,lo.lock_table,lo.lock_index,tx.trx_query,null,null

from information_schema.innodb_trx tx,information_schema.innodb_lock_waits lw,information_schema.innodb_locks lo,information_schema.processlist p

where lw.requesting_trx_id = tx.trx_id and p.id = tx.trx_mysql_thread_id and lo.lock_id = lw.requested_lock_id

) l

order by role desc, trx_state desc;

SELECT p2.`HOST` 被阻塞方host,p2.`USER` 被阻塞方用戶,r.trx_id 被阻塞方事務id,r.trx_mysql_thread_id 被阻塞方線程號,TIMESTAMPDIFF(SECOND,r.trx_wait_started,CURRENT_TIMESTAMP) 等待時間,r.trx_query 被阻塞的查詢,l.lock_table 阻塞方鎖住的表,m.`lock_mode` 被阻塞方的鎖模式,m.`lock_type`  "被阻塞方的鎖類型(表鎖還是行鎖)",m.`lock_index` 被阻塞方鎖住的索引,m.`lock_space` 被阻塞方鎖對象的space_id,m.lock_page 被阻塞方事務鎖定頁的數量,m.lock_rec 被阻塞方事務鎖定行的數量,m.lock_data  被阻塞方事務鎖定記錄的主鍵值,p.`HOST` 阻塞方主機,p.`USER` 阻塞方用戶,b.trx_id 阻塞方事務id,b.trx_mysql_thread_id 阻塞方線程號,b.trx_query 阻塞方查詢,l.`lock_mode` 阻塞方的鎖模式,l.`lock_type` "阻塞方的鎖類型(表鎖還是行鎖)",l.`lock_index` 阻塞方鎖住的索引,l.`lock_space` 阻塞方鎖對象的space_id,l.lock_page 阻塞方事務鎖定頁的數量,l.lock_rec 阻塞方事務鎖定行的數量,l.lock_data 阻塞方事務鎖定記錄的主鍵值,IF (p.COMMAND = 'Sleep', CONCAT(p.TIME,' 秒'), 0) 阻塞方事務空閑的時間

FROM information_schema.INNODB_LOCK_WAITS w

INNER JOIN information_schema.INNODB_TRX b ON b.trx_id = w.blocking_trx_id

INNER JOIN information_schema.INNODB_TRX r ON r.trx_id = w.requesting_trx_id

INNER JOIN information_schema.INNODB_LOCKS l ON w.blocking_lock_id = l.lock_id  AND l.`lock_trx_id`=b.`trx_id`

INNER JOIN information_schema.INNODB_LOCKS m ON m.`lock_id`=w.`requested_lock_id` AND m.`lock_trx_id`=r.`trx_id`

INNER JOIN information_schema. PROCESSLIST p ON p.ID = b.trx_mysql_thread_id

INNER JOIN information_schema. PROCESSLIST p2 ON p2.ID = r.trx_mysql_thread_id

ORDER BY 等待時間 DESC \G;

查看正在鎖的事務

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;  

查看等待鎖的事務

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;  

show engine innodb status \G;

show status like'innodb_row_lock%';