Mysql sql执行阻塞、死锁、锁超时排查

Mysql  sql执行阻塞、死锁、锁超时排查

1、简介

出现sql执行阻塞、死锁、锁超时问题分析：

  • 执行DML操作没有commit，再执行删除操作就会锁表；
  • 在同一事务内先后对同一条数据进行插入和更新操作；
  • 表索引设计不当，导致数据库出现死锁；
  • 长事物，阻塞DDL，继而阻塞所有同表的后续操作。

解决方案：

  1. 找出出现锁表的事务进程杀死。
  2. 进行sql语句分析，优化慢sql。
  3. 把事务等待时间延长。

 

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;