原创
MySql并发插入死锁：揭开锁知识点的神秘面纱！
一灰灰 2023年11月22日 14:48 阅读 591

并发插入死锁引入的MySql锁知识点介绍

最近遇到一个由于唯一性索引，导致并发插入产生死锁的场景，在分析死锁产生的原因时，发现这一块还挺有意思的，涉及到MySql中不少的知识点，特此总结记录一下

I. MySql常见的锁

谈到mysql的锁，可以说的就比较多了，比如行锁、表锁、页锁、元数据锁等，当然我们这里没打算把所有的都细列出来，我们这里主要针对行锁、gap锁进行拓展，以方便分析第二节中，为什么并发插入同样的数据会产生死锁的问题

0. 锁分类

我们最常说的锁，可以区分为共享锁(S)和排它锁(X)，在mysql的innodb引擎中，为了解决幻读问题，引入了gap锁以及next key lock；除此之外，还有一种意向锁的，比如插入意向锁

本文将主要介绍的以下几种锁

  • 行锁(record lock): 请注意它是针对索引的锁（所以如果没有索引时，最终行锁就会导致整个表都会被锁住）
  • 共享锁(S Lock): 也叫读锁，共享锁之间不会相互阻塞（顾名思义）
  • 排它锁(X Lock): 也叫写锁，排它锁一次只能有一个session（或者说事务？）持有
  • 间隙锁(gap lock): 针对索引之间的间隙
  • Next-key锁（Next-key lock)：可以简单理解为行锁 + 间隙锁

上面虽然介绍了几种锁的基本定义，但是什么时候是行锁，怎样获取共享锁，排它锁又是在哪些场景下会产生呢？gap lock/next key lock又是怎样解决幻读的呢？

下面所有的都是基于mysql5.7.22 innodb引擎，rr隔离级别进行说明

1 共享锁与排它锁

下表介绍我们的实际使用的sql中，是否会使用锁，以及会产生什么锁

共享锁与排他锁区分

2. 行锁、表锁、gap锁、next-key锁区分

这几个的区分，主要就是看我们最终锁住的效果，如

  • 没有索引，加S/X锁最终都是锁整表 （为啥？因为锁是针对索引而言的）
  • 根据主键/唯一键锁定确定的记录：行锁
  • 普通索引或者范围查询：gap lock / next key lock

行锁和gap锁之间最大的区别是：

  • 行锁针对确定的记录
  • 间隙锁的是两个确定记录之间的范围； next key lock则是除了间隙还包括确定的记录

3. 实例演示

看上面的两个说明，自然就想在实际的case中操刀分析一下，不同的sql会产生什么样的锁效果

  • 针对表中一条确定的记录加X锁，是只有行锁嘛？
  • 针对表中多条确定的记录加X锁，又会怎样？
  • 针对表中一条不存在的记录加X锁，会有锁产生嘛？如果是gap锁，那区间怎么定？
  • 针对范围加X锁，产生的gap锁范围怎么确定呢？

在分析上面几种case之前，我们得先有一个概念，锁是针对索引而言的，这一点非常非常重要

其次不同的索引，我们需要分别进行测试（其实就是唯一索引与普通索引）

3.1 表准备

接下来针对上面的四种场景，设计我们的测试用例，首先我们准备三张表

  • 无索引表 TN
  • 唯一索引表 TU
  • 普通索引表 TI

对应的表结构和初始化数据如下

CREATE TABLE `tn` (
  `id` int(11) unsigned NOT NULL,
  `uid` int(11) unsigned NOT NULL
) ENGINE=InnoDB;

CREATE TABLE `tu` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `uid` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `u_uid` (`uid`)
) ENGINE=InnoDB;

CREATE TABLE `ti` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `uid` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `u_uid` (`uid`)
) ENGINE=InnoDB;


INSERT INTO `tn` (`id`, `uid`) VALUES (1, 10), (5, 20), (10, 30);
INSERT INTO `tu` (`id`, `uid`) VALUES (1, 10), (5, 20), (10, 30);
INSERT INTO `ti` (`id`, `uid`) VALUES (1, 10), (5, 20), (10, 30);

3.2 精确匹配

即我们的sql可以精确命中某条记录时，锁情况如下

请注意上面的结论，无索引时锁全表好理解，但是普通索引的TI表，居然还有一个[10, 30)的gap锁就有点超乎我们的想象了；

接下来我们验证一下

上图基本流程如

精确命中

从上面的实测也可以看出，普通索引下添加x锁，居然会加一个gap锁，而且这个gap区间是前一个记录（并包含它），到下一个记录

如 uid = 20， 前后两个记录为(1, 10), (10, 30)

  • gap lock: 范围为 [10, 30)
  • 因此无法插入uid=[10,30)
  • 注意，uid=10上有gap锁只是不能插入记录，但是加X锁是没有问题的（有兴趣的可以测试一下）

3.3 精确查询未匹配

当我们锁的记录不存在时，锁情况如下

实测case如下(TN省略，锁全表的没啥测试必要性)

基本流程就不画图了，上面图中已经文字描述了

精确命中

从上面的测试也可以看出，uid=30没有被锁住，这里只在uid=(20, 30)这一区间添加了gap锁

唯一索引与普通索引表现一致，会阻塞insert的插入意向锁（后面说这个东西）

3.4 范围查询

当我们锁一段区间时，锁情况如下

简单来说，范围查询时，添加next key lock，根据我们的查询条件，找到最左边和最右边的记录区间

如 uid > 15 and uid < 25，找到的记录是 (1, 10), (10, 30)

  • gap锁为(10, 30)
  • next key lock会为右边添加行锁，即uid=30加X锁
  • 因此针对uid=30记录加锁会被阻塞（但是针对uid=28,29加x锁则不会被阻塞，插入会，有兴趣的小伙伴可以实测一下）

说明:范围加x锁时，可能锁住不再这个区间的记录，一不小心可能导致死锁哦

3.5 小结

在RR隔离级别中，我们一般认为可以产生锁的语句为:

  • SELECT ... FOR UPDATE: X锁
  • SELECT ... LOCK IN SHARE MODE: S锁
  • update/delete: X锁

4. 锁冲突

上面介绍了不同场景下会产生什么样的锁，但是看完之后会有一个疑问，针对行锁其他会话竞争的时候，可以按照X/S锁的规则来，但是这个GAP LOCK貌似只针对insert有效，insert除了加X锁之外是不是还有其他的特殊逻辑？

4.1 插入意向锁

插入意向锁其实是一种特殊的 gap lock，但是它不会阻塞其他锁。假设存在值为 4 和 7 的索引记录，尝试插入值 5 和 6 的两个事务在获取插入行上的排它锁之前使用插入意向锁锁定间隙，即在（4，7）上加 gap lock，但是这两个事务不会互相冲突等待；但是如果这个区间存在gap lock，则会被阻塞；如果多个事务插入相同数据导致唯一冲突，则在重复的索引记录上加读锁

简单来说，它的属性为：

  • 它不会阻塞其他任何锁；
  • 它本身仅会被 gap lock 阻塞

其次一个重要知识点：

  • 通常insert语句，加的是行锁，排它锁
  • 在insert之前，先通过插入意向锁，判断是否可以插入（仅会被gap lock阻塞）
  • 当插入唯一冲突时，在重复索引上添加读锁
    • 原因如下：
    • 事务1 插入成功未提交，获取了排它锁，但是事务1最终可能会回滚，所以其他重复插入事务不应该直接失败，这个时候他们改为申请读锁（疑问点：为什么要改成读锁呢？）

4.2 锁冲突矩阵

简单版矩阵

当我们将gap lock(间隙锁), next key lock(next-key锁), Insert Intention lock(插入意向锁)也加入矩阵时，就会复杂很多了

说明

  • not gap: 行锁
  • gap: gap lock
  • next-key: gap + 行锁

小结

针对上面的矩阵，理解下面几个原则即可推导上面矩阵

  • gap lock只会与插入意向锁冲突
  • X行锁会与行锁冲突
  • next key lock: 行锁 + gap锁
    • 锁区间内，插入冲突；
    • 行锁的X锁冲突

II. 并发插入死锁分析

上面属于基本知识点，接下来我们看一个实际导致死锁的case

  • 并发插入相同记录导致死锁

0. 表准备

创建一个最简单最基础的表，用于演示

CREATE TABLE `t` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

INSERT INTO `t` (`id`) VALUES (1);

1. 事务回滚的死锁问题

场景复现:

step1:

-- session1: 
begin; insert into t values (2);

-- session2:
begin; insert into t values (2);
-- 阻塞

-- session3:
begin; insert into t values (2);
-- 阻塞

step2:

-- session1:
rollback;

原因分析：

死锁日志查看

SHOW ENGINE INNODB STATUS;

  • step1:
    • session1: 插入(id=2)，会添加一个X + Next Lock锁
    • session2/3: 插入(id=2)，插入意向锁被阻塞，改为持有S + Next Lock锁
  • step2:
    • session1: 回滚，释放X锁
    • session2/3: 竞争X锁，只有对方释放S锁，才能竞争成功；相互等待，导致死锁

2. delete导致死锁问题

和前面操作基本一致，只是第一个会话是删除记录

step1:

-- session1: 
begin; delete from t where id=1;

-- session2:
begin; insert into t values (1);
-- 阻塞

-- session3:
begin; insert into t values (1);
-- 阻塞

step2:

-- session1:
commit;

原因分析和前面基本一致

3. insert加锁逻辑

insert中对唯一索引的加锁逻辑

  1. 先做UK冲突检测，如果存在目标行，先对目标行加S Next Key Lock（该记录在等待期间被其他事务删除，此锁被同时删除）
  2. 如果1成功，对对应行加X + 插入意向锁
  3. 如果2成功，插入记录，并对记录加X + 行锁（有可能是隐式锁）

根据上面这个的逻辑，那么就会有一个有意思的死锁场景

step1:

-- session1
begin; delete from t where id = 1;


-- session2
begin; delete from t where id = 1;

step2:

-- session1
insert into t values(1)

对应的死锁日志

关于这个场景详情博文可以参考: 记录一次Mysql死锁排查过程

4. 怎么避免死锁呢?

  • 将大事务拆成小事务
  • 添加合理的索引，走索引避免为每一行加锁，降低死锁的概率
  • 避免业务上的循环等待（如加分布式锁之类的）
  • 降低事务隔离级别（如RR -> RC 当然不建议这么干）
  • 并发插入时使用replace/on duplicate也可以避免死锁

II. 其他

1. 一灰灰Blog： https://liuyueyi.github.io/hexblog

一灰灰的个人博客，记录所有学习和工作中的博文，欢迎大家前去逛逛

相关博文

2. 声明

尽信书则不如，以上内容，纯属一家之言，因个人能力有限，难免有疏漏和错误之处，如发现bug或者有更好的建议，欢迎批评指正，不吝感激

3. 扫描关注

一灰灰blog

QrCode

2人已点赞

2 条评论

专注的板凳 2024年04月30日 17:21
好好好
点赞
回复 取消回复
远山隔海 2023年11月26日 20:05
111
点赞
回复 取消回复

相关推荐

作者介绍

一灰灰

已加入456天
教程

目录