一个真实的现网mySql死锁问题处理

前言

笔者最近支持的项目现网频繁出现Mysql死锁问题，给系统的稳定运营带来较大风险，领导非常重视，要求从根因上分析问题并解决。

本文将定位解决的过程进行下梳理，一方面用于总结，自我提升，另一方面也给后续处理类似问题的同事提供一些经验参考。

问题处理过程

问题现象

监控系统检测到现网系统出现mysql死锁问题时，会通过告警平台给相关责任人发送告警信息，此告警信息有时非常频繁(每天十几次甚至更多)，告警信息如下：

分析：此告警信息包含了一些系统的基本信息：系统名称、负责人、mysql版本、服务器IP等信息，除此之外再无其他有用信息，还需要让运维提供进一步信息，首先需要的是死锁检测信息。

死锁检测

与运维同事沟通，让其帮忙提供死锁检查的结果：

完整的deadlock检测信息如下：

------------------------
LATEST DETECTED DEADLOCK
------------------------
190501 11:42:25
*** (1) TRANSACTION:
TRANSACTION E162CD59, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 1
MySQL thread id 8449185, OS thread handle 0x7f33af071700, query id 10168457281 10.95.59.103 nsfsmmusr update
INSERT INTO PIS_RANGE( SHOP_CODE,CMMDTY_CODE,MODE,SUPPLIER_CODE,DELETE_FLAG, PHYSICAL_SHOP_CODE, PRICE_FILE_NO,ONLINE_SALE_STATUS, OFFLINE_SALE_ST
ATUS,UUID ) VALUES ('971K','11072232442','01','','', '', '20190501114223082','', '', '6de03e30cd9949bb9773ad8217c549ab')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 465 page no 103854 n bits 320 index `idx_pis_shopcode_cmmdtycode` of table `nsfsmmprd02`.`pis_range` trx id E162CD59 lock_mode X locks gap before rec insert intention waiting
*** (2) TRANSACTION:
TRANSACTION E162CD5B, ACTIVE 0 sec inserting, thread declared inside InnoDB 500
mysql tables in use 1, locked 13 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 1
MySQL thread id 8449234, OS thread handle 0x7f33ae526700, query id 10168457283 10.95.59.100 nsfsmmusr update
INSERT INTO PIS_RANGE( SHOP_CODE,CMMDTY_CODE,MODE,SUPPLIER_CODE,DELETE_FLAG, PHYSICAL_SHOP_CODE, PRICE_FILE_NO,ONLINE_SALE_STATUS, OFFLINE_SALE_ST
ATUS,UUID ) VALUES ('971K','11072232449','01','','', '', '20190501114223087','', '', '3ff912d6e83c424285bfd3312a295f58')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 465 page no 103854 n bits 312 index `idx_pis_shopcode_cmmdtycode` of table `nsfsmmprd02`.`pis_range` trx id E162CD5B lock mode S
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 465 page no 103854 n bits 320 index `idx_pis_shopcode_cmmdtycode` of table `nsfsmmprd02`.`pis_range` trx id E162CD5B lock_mode X locks gap before rec insert intention waiting
*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------

从日志中可以发现，产生死锁的是“PIS_RANGE”表,是在执行插入操作时发生死锁。接下来就要分析下为什么会出现此问题，我们先来看下产生死锁的必要条件。

死锁产生的条件

• 多个并发事务（2个或者以上）；

• 每个事务都持有锁（或者是已经在等待锁）;

• 每个事务都需要再继续持有锁；

• 事务之间产生加锁的循环等待，形成死锁。

了解了锁产生的条件,接下来需要了解下相关的业务场景，看下为什么会出现此问题。

业务场景分析

通过与研发人员沟通，了解到，项目中共有两个场景会向PIS_RANGE表插入数据：

1. MQ 消息队列监听

   分条处理，满足某些条件时，直接入库，否则现将消息暂存，待定时任务定时处理，相关代码业务处理如下（已省略部分分支流程，保留主干）：

    @Override
   public NullResponseDto handleBizz(PisCmmdtyRangeRequestDto bizzRequestObject) {
   List<PisRangeInfo> pisRangeInfos = bizzRequestObject.getPisRangeInfos();
        for (PisRangeInfo rangeInfo : pisRangeInfos) {
            PisRangeInfoVo pisRangeInfoVo = convertParams(rangeInfo);
            try {
               ……
                if (!StringUtils.equals("D", pisRangeInfoVo.getDeleteFlag())) {
                    //保存数据(操作pis_range表)
                    pisRangeService.addPisRagngeByVersion(pisCmRangeEty);
                    continue;
                }
                //插入待处理表，等待job执行
                pisRangeService.insertPisRange(pisRangeInfoVo);
            } catch (Exception e1) {…… }
          }
         return null;
   }

2. Job 定时任务

public void process(String dataSource,String shopCode, int count) {
    List<PisCmRangeEty> pisCmRangeEtyList =           pisRangeService.queryInitData(dataSource,shopCode, count);//分库分表
    for (PisCmRangeEty pisCmRangeEty : pisCmRangeEtyList) {
        try {
            ……
            if(……){
                //保存数据(操作pis_range表)
                pisRangeService.addPisRagngeByVersion(pisCmRangeEty);
                ……
                continue;
            }
        }catch (Exception e){
           ……
        }
    }
}

通过以上源码分析，可以发现，MQ和job最终调用的都是同一个方法 pisRangeService.addPisRagngeByVersion(pisCmRangeEty); 满足死锁的产生的必要条件之一——存在多个并发事务，但并发的事务是否存在相互等待锁，锁的又是什么呢？为解答这个问题，需要理解下mysql中的各种锁。

MySql中的锁

基本锁

InnoDB实现了标准的行级锁，分为共享锁（S）和独占锁（X）

• 共享锁（Sharaed Locks: S锁）

• mysql允许拿到S锁的事务读一行

• 加了S锁记录，允许其他事务再加S锁，不允许其他事务再加X锁

• 语法：select ... lock in share mode;

• 独占锁（Exclusive Locks:X锁）

• mysql允许拿到X锁的事务更新或删除一行

• 加了X锁的记录，不允许其他事务再加X锁或S锁

• 语法：select … for update;

意向锁

InnoDB为了支持多粒度（表锁和行锁）的锁并存，引入意向锁。意向锁是表级锁，分为IS锁和IX锁。

• 意向共享锁（IS）

  事务在请求S锁前，需要先获得对应的IS锁

• 意向排他锁 （IX）

  事务在请求X锁前，需要先获得对应的IX锁

表锁的兼容矩阵

表注：横向是已经持有的锁，纵向是正在请求的锁。

分析兼容矩阵可以得出如下几个结论：

• X与谁都不不兼容（X-X X-S X-IX X-IS）

• S与S,IS相互兼容;S与X,IX 冲突

• IX与IX,IS兼容(意向锁相互兼容)，IX与X,S冲突

• IS与IS,IX，S兼容，与X冲突

自增锁（AUTO-INC Locks）

AUTO-INC锁是事务中的一种特殊的表级锁，通过AUTO_INCREMENT的列来实现。

记录锁 record Lock（RK）

记录锁，行锁，仅仅锁住索引记录的一行。

单条索引记录上加锁，record lock锁住的永远是索引，而非记录本身，即使该表上没有任何索引，那么innodb会在后台创建一个隐藏的聚集主键索引，那么锁住的就是这个隐藏的聚集主键索引。所以说当一条 sql 没有走任何索引时，那么将会在每一条聚集索引后面加X锁，这个类似于表锁，但原理上和表锁应该是完全不同的。

间隙锁 （gap lock:GK）

区间锁, 仅仅锁住一个索引区间(开区间)。 在索引记录之间的间隙中加锁，或者是在某一条索引记录之前或者之后加锁，并不包括该索引记录本身。GAP锁的目的是为了防止同一事务的两次当前读，出现幻读的情况。

临键锁 （Next key Lock：NK）

临键锁，行锁和间隙锁组合起来就叫Next-Key-Lock，左开右闭区间。默认情况下，innodb使用next-key locks来锁定记录。但当查询的索引含有唯一属性的时候，Next-Key Lock 会进行优化，将其降级为Record Lock，即仅锁住索引本身，不是范围。

插入意向锁（insert intention lock：IK）

Gap Lock中存在一种插入意向锁（Insert Intention Lock），在insert操作时产生。在多事务同时写入不同数据至同一索引间隙的时候，并不需要等待其他事务完成，不会发生锁等待。       假设有一个记录索引包含键值4和7，不同的事务分别插入5和6，每个事务都会产生一个加在4-7之间的插入意向锁，获取在插入行上的排它锁，但是不会被互相锁住，因为数据行并不冲突。

注：插入意向锁并非意向锁，而是一种特殊的间隙锁。

如果插入前，该间隙已经有gap锁，那么insert 会申请插入意向锁。因为了避免幻读，当其他事务持有该间隙的间隔锁，插入意向锁就会被阻塞（不用直接用gap锁，是因为gap锁不互斥）

行锁的兼容矩阵

表注：横向是已经持有的锁，纵向是正在请求的锁。

分析兼容矩阵可以得出如下几个结论：

• Record和Record、Next-Key之间相互冲突。

• GAP兼容所有（Record,Gap,Next-key,Insert Intention）

• Next-Key和Record,Next-Key之间相互冲突

• INSERT操作之间不会有冲突。已有的Insert锁不阻止任何准备加的锁。

了解了mySql中锁之后，还需要了解下mysql中在插入过程中都做了些啥，如此案例中的现象，并发中的两个insert语句产生了死锁，为什么会出现此现象呢？

mysql insert操作涉及的锁操作

参考mysql官方文档，MySQL数据库在进行插入时做了如下处理:

insert 会对插入成功的行加上排他锁。这个锁是索引记录锁，不是next-key lock（更不是gap lock），不会阻止其他并发事务往这条记录之前插入记录。

在插入之前，mysql设置了一个叫做insert intention gap lock（插入意向间隙锁）的间隙锁类型。此锁表示插入这样的意图：即插入到同一索引间隙中的多个事务如果不在间隙内的同一位置插入，则不需要彼此等待。假设有一个记录索引包含键值4和7，不同的事务分别插入5和6，每个事务都会产生一个加在4-7之间的插入意向锁，获取在插入行上的排它锁，但是不会被互相锁住，因为数据行并不冲突。

假设发生了一个唯一键冲突错误，那么将会在重复的索引记录上加读锁（如果多事务insert 插入导致了duplicate-key ，那么索引记录被设置为 shared lock  锁）。在多并发情况下,如果一个session 已经有了x-lock，其他并发事务插入同一行记录，那么这个记录上的s-lock将导致死锁。

注：官方文档

https://dev.mysql.com/doc/refman/5.7/en/innodb-locks-set.html

弄清楚了Mysql innodb引擎中的各种锁，并了解了insert操作中涉及的锁处理流程后，接下来我们就可以回到问题的本身，分析下这个问题产生的原因

问题根因分析

首先查看下 pis_range的表信息

表信息

从死锁日志中可以发现，发生死锁的原因是命中的了idx_pis_shopcode_cmmdtycode索引。

事务操作示意

在高并发场景下T1,T2同时进行插入操作

• 事务T1在索引shop_code = '971K' 上加上排他Next-key锁(LOCK_X | LOCK_ORDINARY),会锁住idx_pis_shopcode_cmmdtycode索引范围A  

• 事务T2在索引shop_code = '971K' 上加上排他Next-key锁(LOCK_X | LOCK_ORDINARY),会锁住idx_pis_shopcode_cmmdtycode索引范围A  ，由于请求的GAP锁与已持有的GAP锁是兼容的，因此，事务T2也能加锁成功

• 事务T1执行插入语句，会先加排他Insert Intention锁。由于请求的Insert Intention锁与已有的GAP锁不兼容，则事务T1等待T2释放GAP锁。

• 事务T2执行插入语句，也会等待T1释放GAP锁。 于是，死锁便产生了。

分析出问题的根因后，接下来需要做的事情是看下该如何解决此问题？对于死锁问题，最好的处理方式是避免，我们看下常用的避免死锁的方式有哪些。

死锁的避免

• 类似的业务逻辑以固定的顺序访问表和行。

• 大事务拆小。大事务更倾向于死锁，如果业务允许，将大事务拆小。

• 在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁概率。

• 降低隔离级别，如果业务允许，将隔离级别调低也是较好的选择

• 为表添加合理的索引。可以看到如果不走索引将会为表的每一行记录添 加上锁（或者说是表锁）

解决方案

通过以上分析，结合业务场景，最终的处理方案是，采取死锁避免的第一种方式：“减少并发，尽量以固定的数据访问表和行”:

• 将MQ Listener中直接向PIS_RANGE表中插入数据的操作去掉，改为插入预处理表，统一走定时任务处理

• 定时任务为单线程执行，不会存在并发，可将此类问题出现的概率降低到现有频率90%以上

• 为防止job在指定时间内未跑完，新的任务又开始，导致集群中同时有多个任务同时执行，可采取的方式是在UTS(任务统一调度系统)中配置job为不支持并发处理，同时，因数据库采取了分库分表处理，按照门店编码进行分库，避免了多任务并发执行的场景。

• 通过以上处理，可以有效解决目前发生的频繁死锁问题

• 代码已修改，等待发布生产验证

总结

关于mysql insert导致死锁的问题，网上的材料还是比较多的，笔者在分析这个问题时，参考了较多相关资料，也查阅了mysql官网中的相关章节，对于死锁的问题解决，主要需以预防为主，其背后涉及的mysql中内容非常广泛，如：各种锁、索引、innodb存储引擎、事务的隔离级别、脏读、不可重复读、幻读、当前读与快照读、MVCC等，这些都需要重点去研究。改改代码，解决问题可能很快，但想真正弄懂其背后的原理，还是要下一番功夫。

数据库问题不仅仅是DBA的事情，我们还需要更多地去学习，加油。    

参考文档

https://dev.mysql.com/doc/refman/5.7/en/innodb-locking-transaction-model.html

https://dev.mysql.com/doc/refman/5.7/en/innodb-locks-set.html https://blog.csdn.net/qq_16681169/article/details/73359670

https://blog.csdn.net/varyall/article/details/80219459

http://hedengcheng.com/?p=844

http://hedengcheng.com/?p=771

http://www.cnblogs.com/jiangxu67/p/4242346.html

https://zhuanlan.zhihu.com/p/64435240