210207-MySql并发插入死锁引来的锁分析
by 一灰灰

2021-02-07

DB / Mysql

23 分钟读完 (大约 3506 个字) 0次访问

210207-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中，是否会使用锁，以及会产生什么锁

共享锁与排他锁区分

sql	示例	说明
`select ... where`	`select * from table limit 1`	基于MVCC，快照读，不加锁
`select ... for update`	`select * from table where id=1 for update`	排他锁
`select ... lock in share mode`	`select * from table where id=1 lock in share mode`	共享锁
`update ... where`	`update table set xx=xx where id=1`	排他锁
`delete ... where`	`delete table where id=1`	排它锁

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可以精确命中某条记录时，锁情况如下

实例	TN	TU	TI
`select * from tx where uid=20 for update`	锁全表	行锁 uid=20	行锁uid=20, gap锁uid=[10, 30)

请注意上面的结论，无索引时锁全表好理解，但是普通索引的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 精确查询未匹配

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

实例	TN	TU	TI
`select * from tx where uid=25 for update`	锁全表	`gap lock uid=(20,30)`	`gap lock uid=(20, 30)`

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

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

精确命中

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

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

3.4 范围查询

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

实例	TN	TU	TI
`select * from tx where uid>15 and uid<25 for update`	锁全表	`next key lock uid=(10,30]`	`next key lock uid=(10, 30]`

简单来说，范围查询时，添加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锁

索引	场景	锁范围
无索引	S/X锁	锁全表
唯一索引	精确匹配，且命中	行锁
唯一索引	精确匹配，未命中	gap lock
唯一索引	范围查询	next key lock (上个记录下个记录的区间，左闭右开）右边记录行锁
普通索引	精确匹配，且命中	行锁 + gap lock (上一个记录和下个记录区间，左闭右开，左边记录非行锁)
普通索引	精确匹配，未命中	gap lock
普通索引	范围查询	next key lock

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 锁冲突矩阵

简单版矩阵

	共享锁（S）	排他锁（X）
共享锁（S）	兼容	冲突
排他锁（X）	冲突	冲突

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

行：待加锁；列：存在锁	S(not gap)	S(gap)	S(next key)	X(not gap)	X(gap)	X(next key)	Insert Intention
S(not gap)	-	-	-	冲突	-	冲突	-
S(gap)	-	-	-	-	-	-	冲突
S(next-key)	-	-	-	冲突	-	冲突	冲突
X(not gap)	冲突	-	冲突	冲突	-	冲突	-
X(gap)	-	-	-	-	-	-	冲突
X(next-key)	冲突	-	冲突	冲突	-	冲突	冲突
Insert Intention	-	冲突	冲突	-	冲突	冲突	-

说明

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:

1 2	-- session1: rollback;

原因分析：

死锁日志查看

1	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:

1 2	-- session1: commit;

原因分析和前面基本一致

3. insert加锁逻辑

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

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

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

step1:

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


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

step2:

1 2	-- session1 insert into t values(1)

对应的死锁日志

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

4. 怎么避免死锁呢?

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

II. 其他

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

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

相关博文

2. 声明

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

微博地址: 小灰灰Blog
QQ：一灰灰/3302797840

3. 扫描关注

一灰灰blog

QrCode

# Mysql

支付宝

微信

一灰灰Blog

Java,Python,DBA, 主旨服务器，兼职全栈

Wuhan, China

文章

523

分类

101

标签

122

关注我

210207-MySql并发插入死锁引来的锁分析

I. MySql常见的锁

0. 锁分类

1 共享锁与排它锁

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

3. 实例演示

3.1 表准备

3.2 精确匹配

3.3 精确查询未匹配

3.4 范围查询

3.5 小结

4. 锁冲突

4.1 插入意向锁

4.2 锁冲突矩阵

II. 并发插入死锁分析

0. 表准备

1. 事务回滚的死锁问题

2. delete导致死锁问题

3. insert加锁逻辑

4. 怎么避免死锁呢?

II. 其他

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

2. 声明

3. 扫描关注

喜欢这篇文章？打赏一下作者吧

评论

链接

最新文章

标签云

标签

分类

归档

分类

归档

Your browser is out-of-date!