MySQL next-key锁引发的死锁问题分析与处理

表结构

CREATE TABLE IF NOT EXISTS `t`  (
    `id` int(11) NOT NULL,
    `data_name_en` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
    `tenant_id` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '0',
    index `DE_DATADPEND_IDX`(`data_name_en`(191)) USING BTREE
    ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci comment '字典和进件参数关系' ROW_FORMAT = Dynamic;

问题现象

#有个方法A会在开启事务后顺序执行下面的2个sql
    <delete id="del" >
        delete from t where data_name_en=#{dataNameEn} and tenant_id in ('-1',#{tenantId})
    </delete>
   <insert id="add" databaseId="mysql" parameterType="java.util.List">
        insert into t
        (
        id,
        data_name_en,
        tenant_id
        ) values
        <foreach collection="dataDependList" separator="," index="index" item="item">
            (
            #{item.id,jdbcType=INTEGER},
            #{item.dataNameEn,jdbcType=VARCHAR},
            #{item.tenantId,jdbcType=VARCHAR}
            )
        </foreach>
    </insert>

当方法A被多线程并发执行的时候会偶现死锁问题，分析问题：

因为业务上有限制：每次并发执行方法A的时候，每个线程执行到sql里面的参数：data_name_en有可能是一样的，但是空间#{tenantId}肯定都是不一样的，也就是并发时候，线程1要删除和插入的数据一定和线程2要删除和插入的数据肯定是不一样的，那么为什么还会发生死锁呢。

结合以前看过的课程：极客时间《MySQL实战45讲：20 | 幻读是什么，幻读有什么问题？》《21 | 为什么我只改一行的语句，锁这么多？》，把这两篇完全看懂就能解释清楚问题原因了。MySQL在执行上述delete语句的时候，执行的是当前读，就是它读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁；也就是为了避免事务期间执行delete语句删除的数据前后不一致，MySQL肯定是要加锁才能保证的。观察delete语句的where条件，data_name_en字段有一个普通索引，tenant_id 没有带索引，不是唯一索引，MySQL无法采用行锁来解决事务执行过程中会有其他线程新insert进去的记录也是符合删除where条件的情况，那么必须加上某种锁来满足MySQL默认的事务隔离级别（RR可重复读），也就是加上锁那么在同一个事务中重复执行那条delete语句影响的结果集都是固定的，否则就会出现幻读问题。那么MySQL会加什么锁来解决这种问题呢？

InnoDB 存储引擎通过 next-key 锁（记录锁和间隙锁的组合）来锁住记录本身和记录之间的“间隙”，防止其他事务在这个记录之间插入新的记录，从而避免了幻读现象。对记录加锁带基本单位是 next-key 锁，但是会因为一些条件会退化成间隙锁，或者行锁。加锁的位置准确的说，锁是加在索引上的而非行上。

加锁规则

• 原则 1：加锁的基本单位是 next-key lock。next-key lock 是前开后闭区间。
• 原则 2：查找过程中访问到的对象才会加锁。
• 优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。
• 优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。
• 一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

加何种锁与where条件的字段是否有索引以及是否是唯一索引以及where条件的数据是否存在有关，比如：

在 update 语句的 where 条件使用了唯一索引，那么 next-key 锁会退化成记录锁，也就是只会给一行记录加锁。在 update 语句的 where 条件没有使用索引，就会对所有记录和间隙加上 next-key 锁（记录锁 + 间隙锁），相当于把整个表锁住了。

本例中，where条件有一个字段有普通索引，那么接下来使用工具观察sql是否加了锁，加的什么锁。

先保证t 表有初始数据，执行下面的sql：

insert into t(id,data_name_en,tenant_id )values(-1,'a','1');
insert into t(id,data_name_en,tenant_id )values(-2,'b','1');
insert into t(id,data_name_en,tenant_id )values(-3,'a','2');
insert into t(id,data_name_en,tenant_id )values(-4,'b','2');

然后使用Navicat打开两个窗口，

执行T3的时候会发现执行会卡住，在等待T2的事务提交，说明前面执行的的sql加了锁，然后执行：

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

可以观察到lock_mode列：X,GAP，说明等待的锁是X排他锁和GAP间隙锁；

如果继续执行t4，会报：Deadlock found when trying to get lock; try restarting transaction，说明发生了死锁

那么间隙锁是如何导致死锁现象发生的呢？

首先根据加锁规则，T1和T2的delete操作的where条件有非唯一索引，那么加的是next-key lock，其实接下来都不用仔细分析到底加锁的范围是什么，因为这是锁的一个范围，那么很明显就可能会与另一个线程的insert产生冲突了。

以下举例一种情况：并发执行期间，线程A执行T1 的delete的时候，间隙锁锁了数据范围A;线程B执行T2的delete的时候，间隙锁锁了范围B,间隙锁与间隙锁之间是不冲突的，所以都可以执行成功；然后A线程执行T3的insert的时候插入的数据（data_name_en=‘b’）正好满足线程B的锁范围B，要等待B提交事务释放锁，然后线程B执行T4的insert的时候插入的数据正好满足线程A的锁范围A，也要等待A提交事务释放锁；双方都在等待对方先释放锁导致死锁问题产生；当然，实际情况是多个线程执行上述业务，可能是A等待B，B等待C，C又等待A

解决方案

总体思路：解决死锁就要破坏产生死锁的必要条件

1.把delete语句修改为先根据where条件查询出主键id（id如果不是主键需要先改为主键），然后根据id来进行删除，由于是根据目前已经存在的id删除，根据上面加锁规则，主键id的等值查询加的是行锁。且业务逻辑决定了并发进行删除的id也一定是不一样的，而且由于insert的id是递增的主键，那么肯定和delete的id也是不同的，这样delete之间以及delete和insert之间都没有锁冲突，不存在争抢锁资源的情况，死锁问题得以解决文章来源地址https://www.toymoban.com/news/detail-511887.html

到了这里，关于MySQL next-key锁引发的死锁问题分析与处理的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！