玖叶教程网

MySQL快速定位锁

1、锁等待查询

select * from sys.innodb_lock_waits\G;

select * from INNODB_TRX\G; 检查大事务

waiting_pid 等待事务线程pid

waiting_query等待锁释放的语句

blocking_pid 阻塞的事务pid

blocking_query 阻塞的事务sql语句

2、锁环境MDL开启

修改 performance_schema.setup_instrume nts 表，但实例重启后，又会恢复为默认值。

UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES' WHERE NAME = 'wait/lock/metadata/sql/mdl';

永久生效：

在配置文件中设置

[mysqld]

performance-schema-instrument='wait/lock/metadata/sql/mdl=ON'

3、SHOW PROCESSLIST;

但是进程的id怎么和会话SQL关联，查询具体语句呢

select SQL_TEXT from performance_schema.events_statements_current where

THREAD_ID in (select THREAD_ID from performance_schema.threads where PROCESSLIST_ID=590503);

4、mysql查看被锁住的表，查询是否锁表

show open tables where in_use>0;

5、快速杀掉，恢复业务

select concat('KILL ',id,';') from information_schema.processlist where user='lesuser' into outfile '/tmp/a.txt';

#锁##mysql##死锁##mysql锁##锁查询##锁等待##会话##批量杀进程#