Mysql从入门到精通- Mysql死锁问题排查命令汇总

InnoDB是一个支持行锁的存储引擎，锁的类型有：共享锁（S）、排他锁（X）、意向共享（IS）、意向排他（IX）

InnoDB还将锁细分为如下几种子类型：

• record lock（RK）
  锁直接加在索引记录上面，锁住的是key
• gap lock（GK）
  间隙锁，锁定一个范围，但不包括记录本身。GAP锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况
• next key lock（NK）
  RK+GK
• insert intention lock（IK）
  如果插入前，该间隙已经由gap锁，那么Insert会申请插入意向锁。因为了避免幻读，当其他事务持有该间隙的间隔锁，插入意向锁就会被阻塞。

锁的持有兼容程度如下表

那么再回到死锁日志，可以知道 :

事务1正在获取插入意向锁
事务2正在获取插入意向锁，持有排他gap锁

再看我们上面的锁兼容表格，可以知道，gap lock和insert intention lock是不兼容的
那么就可以推断出：事务1持有gap lock，等待事务2的insert intention lock释放；事务2持有gap lock，等待事务1的insert intention lock释放，从而导致死锁。
那么新的问题就来了，事务1的intention lock 为什么会和事务2的gap lock 有交集，或者说，事务1要插入的数据的位置为什么会被事务2给锁住？
让我回顾一下gap lock的定义：
间隙锁，锁定一个范围，但不包括记录本身。GAP锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况
那为什么是gap lock，gap lock到底是基于什么逻辑锁的记录？发现自己相关的知识储备还不够。那就开始调查。
调查后发现，当当前索引是一个普通索引的时候，会加一个gap lock来防止幻读，此gap lock 会锁住一个左开右闭的区间。假设索引为xx_idx（xx_id），数据分布为1,4,6,8,12，当更新xx_id=9的时候，这个时候gap lock的锁定记录区间就是(8，12]，也就是锁住了xxid in (9,10,11,12)的数据，当有其他事务要插入xxid in (9,10,11,12)的数据时，就会处于等待获取锁的状态。
ps：当前索引不是普通索引，而且是唯一索引等其他情况，请参考下面资料
MySQL 加锁处理分析

回到我自己的案例中，重新屡一下事务1的执行过程：

  

   

    

     

    
    

     

      UPDATE list_rate 
     
    
   

    

     

    
    

     

      SET is_deleted = 1, update_time = now(), operator_id = 1 
     
    
   

    

     

    
    

     

      WHERE hotel_id = 10007 AND room_type_id = 1 AND is_deleted = 0 AND rate_date IN ( '2018-11-10' , '2018-11-11' ) 
     
    
  
 

因为普通索引
KEYhotel_date_idx(hotel_id,rate_date)
的关系 这段sql会获取一个gap lock，范围(2,11111]

  

   

    

     

    
    

     

      INSERT INTO list_rate (hotel_id,rate_date,room_type_id,rate,create_time,update_time,operator_id,is_deleted) 
     
    
   

    

     

    
    

     

      VALUES (10007,'2018-11-10',1,144, now(),now(),1,0) , (10007,'2018-11-11',1,148, now(),now(),1,0) 
     
    
  
 

这段sql会获取一个insert intention lock （waiting）

再看事务2的执行过程

  

   

    

     

    
    

     

      UPDATE list_rate 
     
    
   

    

     

    
    

     

      SET is_deleted = 1, update_time = now(), operator_id = 1 
     
    
   

    

     

    
    

     

      WHERE hotel_id = 10009 AND room_type_id = 1 AND is_deleted = 0 AND rate_date IN ( '2018-11-10' , '2018-11-11' ) 
     
    
  
 

因为普通索引
KEYhotel_date_idx(hotel_id,rate_date)
的关系 这段sql也会获取一个gap lock，范围也是(2,11111]（根据前面的知识，gap lock之间会互相兼容，可以一起持有锁的）

  

   

    

     

    
    

     

      INSERT INTO list_rate (hotel_id,rate_date,room_type_id,rate,create_time,update_time,operator_id,is_deleted) 
     
    
   

    

     

    
    

     

      VALUES (10009,'2018-11-10',1,144, now(),now(),1,0) , (10009,'2018-11-11',1,148, now(),now(),1,0) 
     
    
  
 

这段sql也会获取一个insert intention lock （waiting）

看到这里，基本也就破案了。因为普通索引的关系，事务1和事务2的gap lock的覆盖范围太广，导致其他事务无法插入数据。
重新梳理一下：

所以从结果来看，一堆事务被回滚，只有10007数据被更新成功

结论

gap lock 导致了并发处理的死锁

处理

在mysql默认的事务隔离级别（repeatable read）下，无法避免这种情况。只能把并发处理改成同步处理。或者从业务层面做处理。

知识库levelup

共享锁、排他锁、意向共享、意向排他
record lock、gap lock、next key lock、insert intention lock
show engine innodb status

1、查询是否锁表

show OPEN TABLES where In_use > 0;

查询到相对应的进程 === 然后 kill    id

2、查询进程

    show processlist

补充：

查看正在锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS; 

查看等待锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS; 

1 show processlist;

SHOW PROCESSLIST显示哪些线程正在运行。您也可以使用mysqladmin processlist语句得到此信息。如果您有SUPER权限，您可以看到所有线程。否则，您只能看到您自己的线程（也就是，与您正在使用的MySQL账户相关的线程）。如果有线程在update或者insert 某个表，此时进程的status为updating 或者 sending data。

如果您得到“too many connections”错误信息，并且想要了解正在发生的情况，本语句是非常有用的。MySQL保留一个额外的连接，让拥有SUPER权限的账户使用，以确保管理员能够随时连接和检查系统（假设您没有把此权限给予所有的用户）。

大部分状态对应很快的操作，只要有一个线程保持同一个状态好几秒钟，那么可能是有问题发生了，需要检查一下。还有其他的状态没在上面中列出来，不过它们大部分只是在查看服务器是否有存在错误是才用得着。

2 show full processlist;

show processlist;只列出前100条，如果想全列出请使用show full processlist;

3 show open tables;

这条命令能够查看当前有那些表是打开的。In_use列表示有多少线程正在使用某张表，Name_locked表示表名是否被锁，这一般发生在Drop或Rename命令操作这张表时。所以这条命令不能帮助解答我们常见的问题：当前某张表是否有死锁，谁拥有表上的这个锁等。

show open tables from database;

4 show status like ‘%lock%’

查看服务器状态。

5 show engine innodb status\G;

MySQL 5.1之前的命令是：show innodbstatus\G;，MySQL 5.5使用上面命令即可查看innodb引擎的运行时信息。

6 show variables like ‘%timeout%’;

查看服务器配置参数。

参考资料

mysql5.0经常出现 err=1205 - Lockwait timeout exceeded; try restarting transaction

http://www.imysql.cn/node/165

mysql show processlist命令详解

http://www.cnblogs.com/JulyZhang/archive/2011/01/28/1947165.html

MySQL锁

http://blog.csdn.net/c__ilikeyouma/article/details/8541195

SHOW INNODB STATUS提示语法错误？

http://www.itpub.net/thread-1454597-1-1.html

SHOW OPEN TABLES – what is in your tablecache

http://blog.sina.com.cn/s/blog_4d1f40c00100rsse.html

学习分享

文章来源: blog.csdn.net，作者：隔壁老瓦，版权归原作者所有，如需转载，请联系作者。

原文链接：blog.csdn.net/wxb880114/article/details/105999777