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%';