mysql-死锁

死锁的出现

执行MySQL左连接的时候,发现执行的特别慢,然后发现没有对右表的关联字段建立索引,这时候通过客户端将这个查询断开,而后去建立索引,然后发现索引建立也特别慢,1万条数据,执行了10s左右还是没建立成功。而后再去查询单表,发现单表记录也无法正常查询。

思路

出现这个问题,首先我去考虑的是,是不是机器资源占满了,然后查询发现,只是cpu利用率为100%,内存资源还有很大的空余。不是机器的原因。

MySQL本身提供了一个命令,SHOW PROCESSLIST用来查看当前正在运行的进程。

通过该命令,发现多个sql的state显示为:”Waiting for table metadata lock”。形成死锁了。

解决

MySQL提供了一个kill命令,语法如下:

1
KILL [CONNECTION | QUERY] processlist_id

默认为 CONNECTION。会将整个连接杀掉。

通过SHOW PROCESSLIST显示的ID是CONNECTION ID。

发出命令之后,会给相应的线程设置一个kill flag,大部分情况下,线程需要花点时间才能终止,因为kill flag是以固定的周期来检查的。

杀掉相关进程ID后,再次连接后,可以正常查询了。

扩展

  1. mysql会把当前正在运行的mysql线程信息实时更新到processlist表中。

  2. show processlist : 如果当前用户没有process 的privilege,只能看到属于自己的threads info。如果有process权限,则可以看到其他用户的线程信息。

    1
    show full processlist == select * from information_schema.processlist;

processlist字段

1
desc information_schema.PROCESSLIST;
  1. id - 线程ID。对于慢查询,可以使用kill id来终端该慢sql。
  2. user - 用户eg:system user用于主从复制,maxscale用于mysql proxy代理,另外我们自己创建的用户root、admin、reader等用户。
  3. db - 当前线程连接的数据库。
  4. host - 客户端的IP(直连的ip,比如使用了代理,则显示的是代理的ip)。
  5. command - 当前执行的命令,比如最常见的:Sleep,Query,Connect 等,sleep说明线程是空闲的状态,会有一个默认的超时时间。
  6. time - 消耗时间,单位秒。(query说明执行查询的时间)。
  7. state - 执行状态。
  8. info - 执行的SQL语句。

慢查询

  1. 查找执行超过1分钟的线程信息。

    1
    select * from information_schema.processlist where command != 'Sleep' and time > 60 order by time desc;
  2. 不要轻易kill,注意看是否是system user或者是maxscale等代理监控的用户。

  3. 确定是慢查询的时候,可以执行kill id的操作。

0%