Mysql并发插入死锁解决方案

一、问题描述

MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction?

在并发场景下sql先删除后批量插入形成死锁

二、行锁类型

2.1记录锁（RECORD LOCK）

包含共享锁和独占锁

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

独占锁：简称X锁，排他锁，如果有一条记录持有X锁，其他事务既不可以获取该记录的S锁，也不能获取该记录的X锁。

2.2间隙锁 （GAP LOCK）

一种在记录前面添加的锁，该锁阻止新记录插入到当前记录的前面，直到当前记录的间隙锁释放后，新记录才能正常插入。

2.3NEXT-KEY锁

相当于RECORD LOCK + GAP LOCK

2.4插入意向锁（INSERT INTENTION LOCK）

GAP锁的一种，在执行INSERT前，如果待插入记录的下一条记录被加了GAP锁，则INSERT语句被阻塞，且生成一个插入意向锁。

2.5隐式锁

新插入的记录，不生成锁结构，但由于事务ID的存在，相当于加了隐式锁；别的事务要对这条记录加锁前，先帮助其生成一个锁结构，然后再进入等待状态。

三、Insert加锁

1.当其他事务想获取该记录的S锁或X锁时且该记录所在的聚簇索引中的事务属于活跃状态时，

• 每条记录的聚簇索引中会有一个隐藏字段存储该记录被最后修改时所在的事务id
• 已开始但未commit的事务称为活跃的事务），在其他事务中会为该事务（指的是记录所在的聚簇索引中存储的事务）生成X锁，并将其置为not waitting（持有）状态，而将自己的锁状态标记为waitting（阻塞）状态。

2.对于insert语句，当遇到唯一二级索引重复时，无论事务处于什么隔离级别都会为记录添加S型锁和Next-key锁，而对于insert...on?duplicate?key这样的语句，当遇到唯一二级索引重复时，无论事务处于什么隔离级别都会为记录添加X型锁和next-key锁

四、索引分析

1.查询事务加锁日志

#下面的语句只显示最后一次死锁日志，如果要显示所有发生的死锁日志，需要将系统变量:innodb_print_all_deadlocks设置为ON
SHOW ENGINE INNODB STATUS

2.事务日志分析

------------------------
LATEST DETECTED DEADLOCK
------------------------
2023-12-01 19:09:23 140126396188416
*** (1) TRANSACTION:
TRANSACTION 51320163, ACTIVE 1 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 1
MySQL thread id 140744, OS thread handle 140126094022400, query id 4649053  root update
INSERT INTO xxx

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 8310 page no 46 n bits 624 index express_order_idx of table xxx trx id 51320163 lock_mode X
Record lock, heap no 1

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 8310 page no 46 n bits 624 index express_order_idx of table xxx trx id 51320163 lock_mode X insert intention waiting
Record lock, heap no 1

*** (2) TRANSACTION:
TRANSACTION 51320168, ACTIVE 1 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 1
MySQL thread id 140741, OS thread handle 140130690950912, query id 4649121  root update
INSERT INTO t_express_price 

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 8310 page no 46 n bits 624 index express_order_idx of table xxx trx id 51320168 lock_mode X
Record lock, heap no 1

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 8310 page no 46 n bits 624 index express_order_idx of table xxx trx id 51320168 lock_mode X insert intention waiting
Record lock, heap no 1
*** WE ROLL BACK TRANSACTION (2)

a.LATEST DETECTED DEADLOCK

表示最新检测到的死锁，下方为死锁的事务日志

b.(x)TRANSACTION

表示第几个事务，(1) TRANSACTION为第一个，(2) TRANSACTION为第二个

c.WAITING FOR THIS LOCK TO BE GRANTED

表示当前事务正在对某条记录加某种类型的锁，但由于其他事务已经对该记录持有某种类型的锁而导致阻塞，自己处于等待状态中，一旦其他事务释放锁，该事务就可以加锁成功。

RECORD LOCKS...：表示要添加的、处于阻塞中的锁，其中lock_mode X insert intention waiting表示正在等待加X型意向锁

Record lock：表示要加的、处于等待中的锁作用在哪些记录上，可能会有多条。其下方的hex中数据为编码后的数据，如果真实数据为字符串则编码格式为十六进制uft8，如果真实数据为整形则编码格式为十六进制，我们可以将其解码得到真实的数据。

通过解码后的数据我们就能知道锁作用于哪些记录了，这对我们分析死锁是非常有用的。

d.HOLDS THE LOCK(S)

表示当前事务持有哪些锁

RECORD LOCKS：表示持有X型排他锁

Record lock：表示持有的锁作用在哪些记录上

e.在(x) TRANSACTION下方和WAITING FOR THIS LOCK TO BE GRANTED或HOLDS THE LOCK(S)上方之间出现的sql语句

f.WE ROLL BACK TRANSACTION (2)：表示死锁发生时回滚哪个事务，这里回滚的是第二个事务，Mysql会将受影响的数据最少的事务回滚

通过日志分析发现，TRANSACTION(1)和TRANSACTION(2)都在等待排他插入意向锁（关键字：WAITING FOR THIS LOCK TO BE GRANTED），在这个等待之前TRANSACTION(1)和TRANSACTION(2)持有一个排他记录锁（关键字：HOLDS THE LOCK(S)）

日志中没有打印事务1和事务2在等待插入意向锁前获得了什么锁，但是通过代码分析，事务1和事务2也应该是持有了一个排他记录锁（delete语句）；

这样双方都在等待对方释放锁，导致了死锁

五、解决方案

1.删除时不要通过某个索引进行删除，先获取对应的id，再进行删除

2.修改事务隔离级别

参考：

mysql 死锁(先delete 后insert)日志分析_mysql先删再新增死锁-CSDN博客

MySQL 如何避免 RC 隔离级别下的 INSERT 死锁？ - 知乎

mysql 并发 删除新增数 锁等待 mysql先删再新增死锁_mob6454cc6c40c9的技术博客_51CTO博客

MySQL 案例：Update 死锁详解-腾讯云开发者社区-腾讯云