mysql锁表排查_mysql锁场景及排查

//锁表及慢sql排查

select distinct SUBSTRING_INDEX(host,':',1) as host_name,user,db,state,COMMAND,`TIME` from information_schema.processlist where user='newbaoxian' order by host_name;

select SUBSTRING_INDEX(host,':',1) as host_name,user,state,count(*) from information_schema.processlist group by user;

select SUBSTRING_INDEX(host,':',1) as host_name,user,state,count(*) from information_schema.processlist group by user,host_name;

select * from information_schema.processlist order by id;

//解除正在死锁的状态有两种方法：

//查询是否锁表

show OPEN TABLES where In_use > 0;

//查询进程(如果您有SUPER权限，您可以看到所有线程。否则，您只能看到您自己的线程)

show processlist

//杀死进程id(就是上面命令的id列)

kill 进程ID

//查看当前的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

//查看当前锁定的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

//查看当前等锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

//杀死进程

kill 进程ID

select * from performance_schema.events_statements_current where CURRENT_SCHEMA='newbaoxian';