您当前的位置:首页 > 电脑百科 > 数据库 > MYSQL

一次并发插入死锁带来的“教训”,我才清楚这些MySQL锁知识

时间:2021-02-19 10:48:47  来源:  作者:

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

 

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

共享锁与排他锁区分

一次并发插入死锁带来的“教训”,我才清楚这些MySQL锁知识

 

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

一次并发插入死锁带来的“教训”,我才清楚这些MySQL锁知识

 

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

接下来我们验证一下

一次并发插入死锁带来的“教训”,我才清楚这些MySQL锁知识

 

上图基本流程如下:

一次并发插入死锁带来的“教训”,我才清楚这些MySQL锁知识

 

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

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

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

3.3 精确查询未匹配

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

一次并发插入死锁带来的“教训”,我才清楚这些MySQL锁知识

 

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

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

一次并发插入死锁带来的“教训”,我才清楚这些MySQL锁知识

 

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

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

3.4 范围查询

当我们锁一段区间时,锁的情况如下:

一次并发插入死锁带来的“教训”,我才清楚这些MySQL锁知识

 


一次并发插入死锁带来的“教训”,我才清楚这些MySQL锁知识

 

简单来说,范围查询时,添加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锁
一次并发插入死锁带来的“教训”,我才清楚这些MySQL锁知识

 

| 普通索引 | 精确匹配,且命中 | 行锁 + 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 锁冲突矩阵

简单版矩阵

一次并发插入死锁带来的“教训”,我才清楚这些MySQL锁知识

 

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

一次并发插入死锁带来的“教训”,我才清楚这些MySQL锁知识

 

说明:

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

小结:

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

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

二、并发插入死锁分析

上面属于基本知识点,接下来我们看一个实际导致死锁的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;
一次并发插入死锁带来的“教训”,我才清楚这些MySQL锁知识

 

原因分析:

死锁日志查看

SHOW ENGINE INNODB STATUS;
一次并发插入死锁带来的“教训”,我才清楚这些MySQL锁知识

 

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;
一次并发插入死锁带来的“教训”,我才清楚这些MySQL锁知识

 

原因分析和前面基本一致

一次并发插入死锁带来的“教训”,我才清楚这些MySQL锁知识

 

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锁知识

 

对应的死锁日志

一次并发插入死锁带来的“教训”,我才清楚这些MySQL锁知识

 

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

4. 怎么避免死锁呢?

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

三、总结

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

作者:一灰灰
链接:https://juejin.cn/post/6927197371227095047
来源:掘金



Tags:MySQL锁   点击:()  评论:()
声明:本站部分内容及图片来自互联网,转载是出于传递更多信息之目的,内容观点仅代表作者本人,如有任何标注错误或版权侵犯请与我们联系(Email:2595517585@qq.com),我们将及时更正、删除,谢谢。
▌相关推荐
查看mysql执行的线程 show full processlist; 查看mysql 事务处理列表 select * from information_schema.INNODB_TRX 杀死事务进程 kill 16490...【详细内容】
2021-04-30  Tags: MySQL锁  点击:(192)  评论:(0)  加入收藏
最近遇到一个由于唯一性索引,导致并发插入产生死锁的场景,在分析死锁产生的原因时,发现这一块还挺有意思的,涉及到MySql中不少的知识点,特此总结记录一下。 一、MySql常见的锁谈...【详细内容】
2021-02-19  Tags: MySQL锁  点击:(168)  评论:(0)  加入收藏
前言今天就为大家介绍一下MySQL中锁相关的知识。本文在没有特别声明的情况下,均是默认InnoDB引擎,如涉及到其他引擎或者数据库则会特别指出。什么是锁锁是一种用于保证在并发...【详细内容】
2020-10-19  Tags: MySQL锁  点击:(122)  评论:(0)  加入收藏
【51CTO.com原创稿件】随着 IT 技术的飞速发展,各种技术层出不穷,让人眼花缭乱。尽管技术在不断更新换代,但是有些技术依旧被一代代 IT 人使用至今。 图片来自 PexelsMySQL...【详细内容】
2020-02-18  Tags: MySQL锁  点击:(74)  评论:(0)  加入收藏
本文章向大家介绍MySQL锁详细讲解,包括数据库锁基本知识、表锁、表读锁、表写锁、行锁、MVCC、事务的隔离级别、悲观锁、乐观锁、间隙锁GAP、死锁等等,需要的朋友可以参考一下...【详细内容】
2019-12-24  Tags: MySQL锁  点击:(67)  评论:(0)  加入收藏
▌简易百科推荐
作者:雷文霆 爱可生华东交付服务部 DBA 成员,主要负责Mysql故障处理及相关技术支持。爱好看书,电影。座右铭,每一个不曾起舞的日子,都是对生命的辜负。 本文来源:原创投稿 *爱可生...【详细内容】
2021-12-24  爱可生    Tags:MySQL   点击:(6)  评论:(0)  加入收藏
生成间隙(gap)锁、临键(next-key)锁的前提条件 是在 RR 隔离级别下。有关Mysql记录锁、间隙(gap)锁、临键锁(next-key)锁的一些理论知识之前有写过,详细内容可以看这篇文章...【详细内容】
2021-12-14  python数据分析    Tags:MySQL记录锁   点击:(17)  评论:(0)  加入收藏
binlog 基本认识 MySQL的二进制日志可以说是MySQL最重要的日志了,它记录了所有的DDL和DML(除了数据查询语句)语句,以事件形式记录,还包含语句所执行的消耗的时间,MySQL的二...【详细内容】
2021-12-14  linux上的码农    Tags:mysql   点击:(13)  评论:(0)  加入收藏
为查询优化你的查询 大多数的MySQL服务器都开启了查询缓存。这是提高性最有效的方法之一,而且这是被MySQL的数据库引擎处理的。当有很多相同的查询被执行了多次的时候,这些查...【详细内容】
2021-12-09  元宇宙iwemeta    Tags:mysql   点击:(15)  评论:(0)  加入收藏
测试的目的和原因,公司有很多程序员,每个程序员对数据库和表结构都有自己的理解。而且每个程序员的理解往往是以效率考虑。既然都是为了效率考虑,那么我就来测试一下究竟哪种使...【详细内容】
2021-12-08  吴彬的分享    Tags:Mysql数据库   点击:(14)  评论:(0)  加入收藏
当你们考虑项目并发的时候,我在部署环境,当你们在纠结使用ArrayList还是LinkedArrayList的时候,我还是在部署环境。所以啊,技术不止境,我在部环境。今天这篇文章缕一下在同一台服...【详细内容】
2021-12-08  秃头码哥    Tags:MySQL数据库   点击:(16)  评论:(0)  加入收藏
对于数据分析来说,MySQL使用最多的是查询,比如对数据进行排序、分组、去重、汇总及字符串匹配等,如果查询的数据涉及多个表,还需要要对表进行连接,本文就来说说MySQL中常用的查询...【详细内容】
2021-12-06  笨鸟学数据分析    Tags:MySQL   点击:(19)  评论:(0)  加入收藏
在学习SQL语句之前,首先需要区分几个概念,我们常说的数据库是指数据库软件,例如MySQL、Oracle、SQL Server等,而本文提到的数据库是指数据库软件中的一个个用于存储数据的容器。...【详细内容】
2021-11-24  笨鸟学数据分析    Tags:SQL语句   点击:(23)  评论:(0)  加入收藏
概述以前参加过一个库存系统,由于其业务复杂性,搞了很多个应用来支撑。这样的话一份库存数据就有可能同时有多个应用来修改库存数据。比如说,有定时任务域xx.cron,和SystemA域...【详细内容】
2021-11-05  Java云海    Tags:分布式锁   点击:(31)  评论:(0)  加入收藏
MySQL的进阶查询 一、 按关键字排序 使用ORDERBY语句来实现排序排序可针对一个或多个字段ASC:升序,默认排序方式 【升序是从小到大】DESC:降序 【降序是从大到小】ORDER BY的...【详细内容】
2021-11-05  Java热点    Tags:SQL语句   点击:(27)  评论:(0)  加入收藏
最新更新
栏目热门
栏目头条