Mysql sql执行阻塞、死锁、锁超时排查
Mysql sql执行阻塞、死锁、锁超时排查
1、简介
出现sql执行阻塞、死锁、锁超时问题分析：
- 执行DML操作没有commit，再执行删除操作就会锁表；
- 在同一事务内先后对同一条数据进行插入和更新操作；
- 表索引设计不当，导致数据库出现死锁；
- 长事物，阻塞DDL，继而阻塞所有同表的后续操作。
解决方案：
- 找出出现锁表的事务进程杀死。
- 进行sql语句分析，优化慢sql。
- 把事务等待时间延长。
2、排查
方式一
过滤出来哪些是正在干活的sql，然后按照消耗时间倒叙展示，排在最前面的，极大可能就是有问题的链接了，然后查看 info 一列，就能看到具体执行的什么 SQL 语句了。
-- 查询非 Sleep 状态的链接，按消耗时间倒序展示
select * from information_schema.processlist where command != 'Sleep' order by time desc
- Id：链接mysql 服务器线程的唯一标识，可以通过kill来终止此线程的链接。
- User：当前线程链接数据库的用户
- Host：显示这个语句是从哪个ip 的哪个端口上发出的。可用来追踪出问题语句的用户
- db: 线程链接的数据库，如果没有则为null
- Command: 显示当前连接的执行的命令，一般就是休眠或空闲（sleep），查询（query），连接（connect）
- Time: 线程处在当前状态的时间，单位是秒
- State：显示使用当前连接的sql语句的状态，很重要的列，state只是语句执行中的某一个状态，一个 sql语句，已查询为例，可能需要经过copying to tmp table，Sorting result，Sending data等状态才可以完成。
- Info: 线程执行的sql语句，如果没有语句执行则为null。这个语句可以使客户端发来的执行语句也可以是内部执行的语句。
kill 使用
通过前面的查询，我们查到了问题sql，通常会kill掉这个链接的线程。
-- 查询执行时间超过2分钟的线程，然后拼接成 kill 语句
select
concat('kill ', id, ';')
from information_schema.processlist
where command != 'Sleep' and time > 2*60 order by time desc
方式二、 调整锁超时参数
innodb_lock_wait_timeout是动态参数,默认值50秒，最小值是1秒，最大值是1073741824；
# 查询锁等待超时时间
show variables like 'innodb_lock_wait_timeout';
# 修改锁等待超时时间（单位秒）
set GLOBAL innodb_lock_wait_timeout=150;
方式三、查询产生锁的具体sql
根据具体的sql，就能看出是不是死锁了，并且可以确定具体是执行了什么业务，是否可以kill;
select
a.trx_id 事务id ,
a.trx_mysql_thread_id 事务线程id,
a.trx_query 事务sql
from
INFORMATION_SCHEMA.INNODB_LOCKS b,
INFORMATION_SCHEMA.innodb_trx a
where
b.lock_trx_id=a.trx_id;
杀掉死锁的事务： 查询出所有有锁的事务对应的线程ID（注意是线程id,不是事务id）
select concat('KILL ',a.trx_mysql_thread_id ,';') from INFORMATION_SCHEMA.INNODB_LOCKS b,INFORMATION_SCHEMA.innodb_trx a where b.lock_trx_id=a.trx_id;
