Mysql insert也会发生死锁？

peachesTao

已于 2023-10-07 18:34:37 修改

阅读量1.5k

点赞数 2

分类专栏： MySql 文章标签： mysql

于 2023-10-07 11:04:11 首次发布

本文链接：https://blog.csdn.net/taoerchun/article/details/133634769

版权

MySql 专栏收录该内容

11 篇文章 0 订阅

订阅专栏

大家好，我是peachesTao，今天给大家分享我们前几天线上遇到的一个Mysql死锁的案列，希望在大家碰到类似的问题时有所帮助。

9月28号下午我们线上钉钉报警群报了一个“Error 1213: Deadlock found when trying to get lock”的错误，第一次线上发生数据库死锁，当时感觉事态严重。

来不急多想，马上通过错误日志堆栈找到了发生死锁的sql语句，竟然是一条insert语句：“insert into ... on duplicate key update ...”，这直接戳中了我的盲区：insert也会导致死锁？

在正式介绍案例前我们先来看一下前置知识，这有助于后面的理解。

前置知识

记录锁
包含共享锁和独占锁

共享锁：简称S锁，当事务读取一条记录时需要先获取改记录的S锁，如果一条记录持有S锁，其他事务可以继续获取该记录的S锁，但不能获取X锁。

独占锁：也成排他锁，简称X锁，如果一条记录持有X 锁，其他事务既不可以获取该记录的S锁，也不能获取该记录的X锁。
间隙锁，简称gap锁
一种在记录前面添加的锁，该锁阻止新记录插入到当前记录的前面，直到当前记录的间隙锁释放后新记录才能正常插入。
next-key锁
本质是记录锁+间隙锁的组合
插入意向锁
新记录在被间隙锁阻塞时会生成插入意向锁，间隙锁释放后插入意向锁也会释放
隐式锁
Mysql 为了节省锁的开销，insert语句执行时记录是不会生成锁的，只有在满足下面条件时insert语句执行后的记录才会生成锁：

当其他事务想获取该记录的S锁或X锁时且该记录所在的聚簇索引中的事务属于活跃状态时（1、每条记录的聚簇索引中会有一个隐藏字段存储该记录被最后修改时所在的事务id 2、已开始但未commit的事务称为活跃的事务），在其他事务中会为该事务（指的是记录所在的聚簇索引中存储的事务）生成X锁，并将其置为not waitting（持有）状态，而将自己的锁状态标记为waitting（阻塞）状态。（这段比较难理解，不要着急，后面会通过案例详细说明）

而其他情况则可以正常读取，不需要生成锁。

我们将insert时不生成锁，等到满足条件时才生成的锁称为隐式锁，从这里可以看出隐式锁实际上不是一种新锁，而是一种特殊的记录锁。
对于insert语句当遇到唯一二级索引重复时无论事务处于什么隔离级别都会为记录添加S型锁和next-key锁，而对于insert...on duplicate key...这样的语句当遇到唯一二级索引重复时无论事务处于什么隔离级别都会为记录添加X型锁和next-key锁

（主键重复的场景这里不做介绍，在后面的推荐资料中大家可以自行了解）

所有的锁在内存中都表现为一个锁结构，锁结构中有一个等待的属性，如果为true,表示当前事务获取到锁成功，如果为false,表示当前事务尚未获取到锁，处于等待状态。

死锁分析

接着排查问题，通过SHOW ENGINE INNODB STATUS语句查事务加锁的日志，里面就有最近一次的死锁记录。（因为数据的敏感性和便于分析，我将数据做了替换、删除了对分析无关的字段）

SHOW ENGINE INNODB STATUS只会显示最后一次死锁日志，如果要显示所有发生的死锁日志则需要将系统变量：innodb_print_all_deadlocks设置为ON

下面为事务的死锁日志，其中标注的①②③④⑤⑥为6个关键点

------------------------
LATEST DETECTED DEADLOCK
------------------------
2023-09-28 14:56:20 0x7fb14a2bd700
*** (1) TRANSACTION:
TRANSACTION 1374635254, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 2045802, OS thread handle 140399504230144, query id 12689481084 192.168.0.1 account_001 update
①发生死锁时此事务正在执行的语句
insert into course_member_statics(course_id,uid) values('20230928145601000001',222222) on duplicate key update member_delete_flag=0
②此事务正在等待其他事务对记录course_id:20230928145601000001、uid:222222释放X型记录锁
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1753 page no 659149 n bits 360 index idx_courseid_uid of table `uclass`.`course_member_statics` trx id 1374635254 lock_mode X waiting
Record lock, heap no 58 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 22; hex 3230323330393238313435363031303030303031; asc 20230928145601000001;; # 3230323330393238313435363031303030303031是20230928145601000001的utf8编码,这里是course_id字段的值
 1: len 4; hex 0003640E; asc GD ;;# 0003640E是222222十六进制编码,这里是uid字段的值【下同】
 2: len 8; hex 8000000000a66c9d; asc l ;; # 8000000000a66c9d是10906781十六进制编码,这里是主键id字段的值（存储的是有符号数，前面的8要改成0）【下同】

*** (2) TRANSACTION:
TRANSACTION 1374634984, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
15 lock struct(s), heap size 1136, 160 row lock(s), undo log entries 669
MySQL thread id 2045822, OS thread handle 140399430326016, query id 12689481315 192.168.0.2 account_001 update
③发生死锁时此事务正在执行的语句
insert ignore into course_member_statics(course_id,uid) values 
('20230928145601000001',222222),
('20230928145601000001',111111)


*** (2) HOLDS THE LOCK(S):
④此事务对记录course_id:20230928145601000001、uid:222222持有X型记录锁
RECORD LOCKS space id 1753 page no 659149 n bits 312 index idx_courseid_uid of table `uclass`.`course_member_statics` trx id 1374634984 lock_mode X locks rec but not gap
Record lock, heap no 38 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 22; hex 3230323330393238313435363031303030303031; asc 20230928145601000001;;
 1: len 4; hex 0003640E; asc ;;
 2: len 8; hex 8000000000a66c9d; asc ;;


*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
⑤此事务对记录course_id:20230928145601000001、uid:222222持有插入意向锁，正在等待其他事务对该记录释放间隙锁
RECORD LOCKS space id 1753 page no 659149 n bits 472 index idx_courseid_uid of table `uclass`.`course_member_statics` trx id 1374634984 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 58 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 22; hex 3230323330393238313435363031303030303031; asc 20230928145601000001;;
 1: len 4; hex 0003640E; asc  GD ;;
 2: len 8; hex 8000000000a66c9d; asc ;;
⑥最后决定回滚事务1 
*** WE ROLL BACK TRANSACTION (1)

我们从上述日志中摘取下面几个关键信息进行说明：

LATEST DETECTED DEADLOCK：表示最新检测到的死锁，下方为死锁的事务日志
(x) TRANSACTION：表示第几个事务，(1) TRANSACTION为第一个，(2) TRANSACTION为第二个
WAITING FOR THIS LOCK TO BE GRANTED：表示当前事务正在对某条记录加某种类型的锁，但由于其他事务已经对该记录持有某种类型的锁而导致阻塞，自己处于等待状态中，一旦其他事务释放锁，该事务就可以加锁成功。
- RECORD LOCKS...：表示要添加的、处于阻塞中的锁，其中lock_mode X waiting表示正在等待加X型next-key锁，lock_mode X locks gap before rec insert intention waiting表示想在某条记录前面插入记录，由于该记录持有间隙锁，正在等待间隙锁释放，此时持有插入意向锁
- Record lock：表示要加的、处于等待中的锁作用在哪些记录上，可能会有多条。其下方的hex中数据为编码后的数据，如果真实数据为字符串则编码格式为十六进制uft8，如果真实数据为整形则编码格式为十六进制，我们可以将其解码得到真实的数据。（下同）
  
  通过解码后的数据我们就能知道锁作用于哪些记录了，这对我们分析死锁是非常有用的。
HOLDS THE LOCK(S)：表示当前事务持有哪些锁
- RECORD LOCKS...：表示已经持有的锁，其中lock_mode X locks rec but not gap表示持有记录的X型记录锁，不持有间隙锁
- Record lock：表示持有的锁作用在哪些记录上，可能会有多条。
在(x) TRANSACTION下方和WAITING FOR THIS LOCK TO BE GRANTED或HOLDS THE LOCK(S)上方之间出现的sql语句为导致出现死锁的sql语句，像日志中标出的①和③就是导致死锁的sql语句
WE ROLL BACK TRANSACTION (1)：表示死锁发生时回滚哪个事务，这里回滚的是第一个事务，Mysql会将受影响的数据最少的事务回滚

下面我们对这次死锁做一次完整的分析：

通过日志可以知道，
事务1执行的语句为：

insert into course_member_statics(course_id,uid) values('20230928145601000001',222222) on duplicate key update member_delete_flag=0;

事务2执行的语句为：

insert ignore into course_member_statics(course_id,uid) values 
('20230928145601000001',222222),
('20230928145601000001',111111);

其中course_id和uid为唯一索引。

1、事务2执行插入222222这条数据

insert ignore into course_member_statics(course_id,uid) values ('20230928145601000001',222222);

（这里怎么是单条insert，不是批量insert吗？虽然sql语法是批量insert但实际到存储引擎执行的时候是一条条进行的），因为是普通的insert语句所以不会加锁

2、事务1执行

insert into course_member_statics(course_id,uid) values('20230928145601000001',222222) on duplicate key update member_delete_flag=0;

发现事务2已经插入了一个相同的记录，于是事务1要对该记录添加X型next-key锁。

3、根据前面的知识我们知道，对一条insert的数据，如果其他事务要对其加S型或X型锁，且该记录对应的聚簇索引中存储的事务id处于活跃状态时，就会触发这条记录上的隐式锁升级为显示锁。

在这里就是事务1给事务2在222222记录增加X型记录锁，并将其状态置于持有状态，同时将自己置于阻塞状态

4、事务2执行插入111111这条数据

insert ignore into course_member_statics(course_id,uid) values ('20230928145601000001',111111);

按照二级索引存储的特点，记录111111要插在记录222222的前面，这时出现了插入意向锁阻塞，按照我们前面的说的，在某条记录前面插入数据只有在该记录持有间隙锁时才会阻塞，问题是事务1对记录222222并没有持有间隙锁，怎么会阻塞呢？

Mysql规定，只要别的事务对记录生成了一个显式的间隙锁的锁结构，不论那个事务是已经获取到了该锁(granted)，还是正在等待获取(waiting)，当前事务要在该记录前面插入新记录都会被阻塞。

回到该例，因为事务1已经为记录222222生成了一个X型的next-key锁结构（next-key锁包含间隙锁），虽然该锁的状态是在阻塞等待中，但事务2在该记录前插入记录仍然会被阻塞。

这时事务1在等待事务2释放记录222222上的X型记录锁，同时事务2也在等待事务1在记录222222上的间隙锁释放，出现了互相等待的现象，导致了死锁发生。

最后由于死锁导致事务1被回滚了，事务2执行成功，因为事务2包含事务1的数据，所有没有对线上的数据造成影响，就算最后回滚的是事务2也没问题，因为insert ignore into语句代码做了错误重试处理。

下面我们通过例子还原上述死锁，并对每条sql语句的执行进行加锁分析

还原死锁

建表sql语句

DROP TABLE IF EXISTS `course_member_statics`;
CREATE TABLE `course_member_statics` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `course_id` varchar(40) NOT NULL DEFAULT '' COMMENT '课程ID',
  `uid` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '用户UID',
  `delete_flag` tinyint(2) NOT NULL DEFAULT '0' COMMENT '是否被删除 状态 0：未删除  1：已删除',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_courseid_uid` (`course_id`,`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='课程成员表';

事务1要执行的语句：

START TRANSACTION;
a、insert into course_member_statics(course_id,uid) values ('20230928145601000001',222222) on duplicate key update delete_flag=0;
COMMIT;

事务2要执行的语句：（为了每次都出现死锁，这里将批量插入改成了单独的两条insert）

START TRANSACTION;
b、insert ignore into course_member_statics(course_id,uid) values 
('20230928145601000001',222222);
c、insert ignore into course_member_statics(course_id,uid) values 
('20230928145601000001',111111);
COMMIT;

我们按照b，a，c的顺序逐步在终端执行（事务开始前最好要先执行START TRANSACTION语句，如果不执行同时系统变量autocommit=ON时每执行一条sql都会认为是一个单独的事务，无法看到死锁效果），

并通过SHOW ENGINE INNODB STATUS来查看加锁情况（注意：开始执行sql语句前还需要将系统变量innodb_status_output_locks打开（set GLOBAL innodb_status_output_locks = 1），否则日志中不会出现任何加锁信息）

1、先执行事务2的b语句，执行SHOW ENGINE INNODB STATUS看日志

------------
TRANSACTIONS
------------
---TRANSACTION 1864, ACTIVE 5 sec
1 lock struct(s), heap size 1128, 0 row lock(s), undo log entries 1
MySQL thread id 22, OS thread handle 6129594368, query id 125 localhost 127.0.0.1 root
TABLE LOCK table `test`.`course_member_statics` trx id 1864 lock mode IX

看TRANSACTIONS段落，可以看出语句执行完后事务2只持有表的意向X型锁，没有持有记录的任何锁

2、再执行事务1的a语句，执行SHOW ENGINE INNODB STATUS看日志

------------
TRANSACTIONS
------------
---TRANSACTION 1865, ACTIVE 20 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1128, 1 row lock(s), undo log entries 1
MySQL thread id 23, OS thread handle 6131822592, query id 127 localhost 127.0.0.1 root update
insert into course_member_statics(course_id,uid) values ('20230928145601000001',222222) on duplicate key update delete_flag=0
------- TRX HAS BEEN WAITING 20 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 3 page no 5 n bits 72 index idx_courseid_uid of table `test`.`course_member_statics` trx id 1865 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 20; hex 3230323330393238313435363031303030303031; asc 20230928145601000001;;
 1: len 4; hex 0003640e; asc   d ;;
 2: len 8; hex 800000000000000b; asc         ;;

------------------
TABLE LOCK table `test`.`course_member_statics` trx id 1865 lock mode IX
RECORD LOCKS space id 3 page no 5 n bits 72 index idx_courseid_uid of table `test`.`course_member_statics` trx id 1865 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 20; hex 3230323330393238313435363031303030303031; asc 20230928145601000001;;
 1: len 4; hex 0003640e; asc   d ;;
 2: len 8; hex 800000000000000b; asc         ;;

---TRANSACTION 1864, ACTIVE 58 sec
2 lock struct(s), heap size 1128, 1 row lock(s), undo log entries 1
MySQL thread id 22, OS thread handle 6129594368, query id 125 localhost 127.0.0.1 root
TABLE LOCK table `test`.`course_member_statics` trx id 1864 lock mode IX
RECORD LOCKS space id 3 page no 5 n bits 72 index idx_courseid_uid of table `test`.`course_member_statics` trx id 1864 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 20; hex 3230323330393238313435363031303030303031; asc 20230928145601000001;;
 1: len 4; hex 0003640e; asc   d ;;
 2: len 8; hex 800000000000000b; asc         ;;

看TRANSACTIONS段落，可以看到事务2本来是没有持有记录222222的X型记录锁的，在执行这条语句后就有了，并且事务1自己对该记录的X型next-key锁置于等待中。这正是隐式锁升级为显示锁的效果

3、最后执行事务2的c语句，执行SHOW ENGINE INNODB STATUS看日志

------------------------
LATEST DETECTED DEADLOCK
------------------------
2023-10-06 15:35:02 0x16c617000
*** (1) TRANSACTION:
TRANSACTION 1865, ACTIVE 36 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1128, 1 row lock(s), undo log entries 1
MySQL thread id 23, OS thread handle 6131822592, query id 127 localhost 127.0.0.1 root update
insert into course_member_statics(course_id,uid) values ('20230928145601000001',222222) on duplicate key update delete_flag=0

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 3 page no 5 n bits 72 index idx_courseid_uid of table `test`.`course_member_statics` trx id 1865 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 20; hex 3230323330393238313435363031303030303031; asc 20230928145601000001;;
 1: len 4; hex 0003640e; asc   d ;;
 2: len 8; hex 800000000000000b; asc         ;;


*** (2) TRANSACTION:
TRANSACTION 1864, ACTIVE 74 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 2
MySQL thread id 22, OS thread handle 6129594368, query id 129 localhost 127.0.0.1 root update
insert ignore into course_member_statics(course_id,uid) values 
('20230928145601000001',111111)

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 3 page no 5 n bits 72 index idx_courseid_uid of table `test`.`course_member_statics` trx id 1864 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 20; hex 3230323330393238313435363031303030303031; asc 20230928145601000001;;
 1: len 4; hex 0003640e; asc   d ;;
 2: len 8; hex 800000000000000b; asc         ;;


*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 3 page no 5 n bits 72 index idx_courseid_uid of table `test`.`course_member_statics` trx id 1864 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 20; hex 3230323330393238313435363031303030303031; asc 20230928145601000001;;
 1: len 4; hex 0003640e; asc   d ;;
 2: len 8; hex 800000000000000b; asc         ;;

*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
------------
---TRANSACTION 1864, ACTIVE 92 sec
3 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 2
MySQL thread id 22, OS thread handle 6129594368, query id 129 localhost 127.0.0.1 root
TABLE LOCK table `test`.`course_member_statics` trx id 1864 lock mode IX
RECORD LOCKS space id 3 page no 5 n bits 72 index idx_courseid_uid of table `test`.`course_member_statics` trx id 1864 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 20; hex 3230323330393238313435363031303030303031; asc 20230928145601000001;;
 1: len 4; hex 0003640e; asc   d ;;
 2: len 8; hex 800000000000000b; asc         ;;

RECORD LOCKS space id 3 page no 5 n bits 72 index idx_courseid_uid of table `test`.`course_member_statics` trx id 1864 lock_mode X locks gap before rec insert intention
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 20; hex 3230323330393238313435363031303030303031; asc 20230928145601000001;;
 1: len 4; hex 0003640e; asc   d ;;
 2: len 8; hex 800000000000000b; asc         ;;

当执行这条语句后事务1的终端出现了死锁的错误提示：“Deadlock found when trying to get lock; try restarting transaction”

Deadlock found when trying to get lock; try restarting transaction

先看TRANSACTIONS段落，可以看出事务2分别对记录222222持有X型记录锁和插入意向锁，持有插入意向锁是因为在记录222222插入插入111111时被间隙锁阻塞了。

再看LATEST DETECTED DEADLOCK段落，可以看到事务1在等待事务2释放记录222222上的X型记录锁，同时事务2也在等待事务1在记录222222上的间隙锁释放，出现了互相等待的现象，导致了死锁发生。因为事务1只影响1条记录，而事务2影响两条记录，所以将事务1回滚。

如果将执行顺序改成a,b,c也会出现死锁，死锁原因跟上面的类似，至于其他组合：a,c,b、b,c,a、c,a,b、c,b,a都不会出现死锁，至于原因大家可以自己分析一下。

上面所有的分析都是基于REPEATABLE READ隔离级别分析的，如果换成READ UNCOMMITTED，READ COMMITTED，SERIALIZABLE隔离级别还会出现死锁吗？

答案是会的，因为无论是哪种事务隔离级别，insert遇到唯一二级索引重复时都会给记录添加next-key锁（包含间隙锁），且会触发隐式锁升级为显示锁，而这两者正是导致出现死锁的条件。

如何避免死锁

既然存在死锁的问题，那么死锁能避免吗？避免死锁的方法：

改变事务执行语句的顺序
在确保业务功能正确的情况下，可以通过改变语句的执行顺序避免死锁。当然前提是得知道是什么原因导致的死锁

但很多时候语句的执行顺序会随着数据的变化而变化的，无法人为控制，像上面死锁的问题insert ignore实际上有上千条数据批量插入，无法知道存储引擎到底先执行哪条后执行哪条
给记录添加合适的索引
建立合适的索引，缩小锁作用的范围和减少事务的执行的时间，这样能减少事务之间争抢锁的概率

虽然死锁可以一定程度的减少，但无法完全避免，当出现死锁时也不必过于担心，Mysql会以最小的代价回滚事务，只要我们做了合理的重试机制（要注意重试的频率，过快可能会导致进一步死锁），

比如对异步的操作要做重试处理，因为发生错误无法直接反馈给操作人，同步操作还好，发生死锁会收到报错信息，重新执行即可。

总结

Mysql insert 语句在特定的并发场景下也是会出现死锁的，当我们能分析出死锁的原因，就能做到有的放矢。以下为本篇文章主要内容

记录锁、间隙锁、插入意向锁、next-key锁、隐式锁的定义以及作用
隐式锁在特定的条件下会升级为显示锁
insert语句在遇到唯一二级索引重复时会为记录添加S型的next-key锁，而insert... on duplicate key...则会添加X型的next-key锁
只要别的事务对记录生成了一个显式的间隙锁的锁结构，不论那个事务是已经获取到了该锁，还是正在等待获取，当前事务要在该记录前面插入新记录都会被阻塞。
通过SHOW ENGINE INNODB STATUS查看、分析死锁和加锁过程
在能确定语句的执行顺序且保证业务功能正确的情况下可以通过改变语句的执行顺序避免死锁。死锁不能完全避免，要有合理的重试机制