mysql查询锁表排查策略

本文章来源于同事的代码是php fpm运行的，数据库访问量不大的时候，接口去阻塞了，所以怀疑是有死锁,最终发现问题果然是有表被锁住，存储过程锁表，导致对该表的更新操作阻塞，由于php fpm是进程方式运行，一个更新阻塞，导致服务进程阻塞，本人是java开发，不会讲php的东西，在这儿只是列出本次排查问题的思路，方便以后大家有例可循

1. 三张主要的系统表

#当前运行的所有事务
select * from information_schema.innodb_trx;

#当前出现的锁
select * from information_schema.innodb_locks

#锁等待的对应关系
select * from information_schema.innodb_lock_waits

2. 查询哪些sql在等待锁
   #查询事务正在等待的锁和执行的sql
   select it.trx_query,ilw.* from information_schema.innodb_trx it,information_schema.innodb_lock_waits ilw WHERE it.trx_id=ilw.requesting_trx_id

3. 当前的事务阻塞在哪个sql事务上
   SELECT it2.trx_query, it2.* from information_schema.innodb_trx it2 where it2.trx_id in (
   select ilw.blocking_trx_id from information_schema.innodb_trx it,information_schema.innodb_lock_waits ilw WHERE it.trx_id=ilw.requesting_trx_id
   )

4. 查询当前事务正在等待什么锁
   #查询当前事务正在等待什么锁
   SELECT * from information_schema.innodb_locks il where il.lock_trx_id in (
   select ilw.blocking_trx_id from information_schema.innodb_trx it,information_schema.innodb_lock_waits ilw WHERE it.trx_id=ilw.requesting_trx_id
   )

引用博客，关于上面系统表的介绍，可以参考下面这个连接

https://blog.csdn.net/J080624/article/details/80596958

show status like ‘%lock%’

show OPEN TABLES where In_use > 0

#查询是否锁表
show OPEN TABLES where In_use > 0;

#查看正在锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

#查看等待锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

//查询正在执行的进程
show processlist;

//显示所有正在执行的进程
show full processlist;

#查看innodb引擎的运行时信息
show engine innodb status;

#查看服务器状态
show status like ‘%lock%’;

show engine innodb status\G;

#看超时时间：
show variables like ‘%timeout%’;

#查询当前ip所占用连接
select substring_index(host,’:’,1),count(*) from processlist group by substring_index(host,’:’,1);

#查询当前用户,ip，连接数
SELECT USER,SUBSTRING_INDEX(HOST,’:’,1) AS IP,COUNT(1) AS Total FROM INFORMATION_SCHEMA.PROCESSLIST GROUP BY IP ORDER BY Total DESC;

#查询正在执行sql的进程
SELECT * from processlist WHERE INFO is not null;