Mysql锁等待排查

锁等待监控涉及到的命令

1）看有没有锁等待
show status like "innodb_row_lock%";
Innodb_row_lock_current_waits:  当前有多少锁等待
Innodb_row_lock_waits:  一共发生过多少锁等待(每次重启Mysql重新开始计算)


2）被阻塞的事务（这一步可以忽略）
select * from information_schema.innodb_trx where trx_state="lock wait";


3) 查看锁源，谁锁的我
select * from sys.innodb_lock_waits; 

select 
locked_table, 
locked_type,
waiting_trx_id,
waiting_pid,
waiting_query,
waiting_lock_mode,
blocking_trx_id,
blocking_pid,
sql_kill_blocking_connection
from sys.innodb_lock_waits;

locked_table    产生锁等待的表 
locked_type    锁类型(record, gaplock,nextlock)
waiting_trx_id    等待的事务ID
waiting_pid    等待的事务连接线程ID
waiting_query    等待的事务语句
waiting_lock_mode    等待锁的类型(X,S)
blocking_trx_id    锁源的事务ID
blocking_pid    锁源的事务连接线程ID
sql_kill_blocking_connection    处理建议，可以通过执行这个语句杀死锁源，释放锁


4) 根据锁源的连接线程ID,找到锁源SQL的线程ID（一个连接线程下，可能有很多sql线程）
select * from performance_schema.threads  where processlist_id=(上面查到的blocking_pid);

select 
thread_id,
name,
processlist_id
from performance_schema.threads where processlist_id=(上面查到的blocking_pid);

thread_id    真正执行语句的ID


5) 根据锁源的SQL线程的ID，找到锁源的SQL语句
select * from performance_schema.events_statements_current where thread_id=(上面查到的thread_id);

select 
thread_id,
event_name,
sql_text 
from performance_schema.events_statements_current where thread_id=(上面查到的thread_id);

sql_text    锁源的SQL语句


6）查看锁源SQL语句历史
select * from performance_schema.events_statements_history;