insert duplicate key update 死锁分析

背景

线上发现mysql 数据库报错死锁日志,版本为5.7.25,默认隔离级别为rr,具体的错误日志就不贴出来了,原因是有同事使用了insert duplicate key update语法,进行存在就更新的操作

那么问题来了,应该从哪入手呢

先通过show engine innodb status看看死锁日志吧,我在这都加上注释了,并且去掉了无关信息

LATEST DETECTED DEADLOCK
------------------------
//死锁发生的时间 utc时间
2020-12-21 12:51:32 0x7f1bc0d79700
//事务1
*** (1) TRANSACTION:
// 事务1的事务编号和运行了时间
TRANSACTION 9181426, ACTIVE 33 sec inserting
//一张表被用到,一张表被锁
mysql tables in use 1, locked 1
//在等3个锁,为什么是三个锁呢  , 锁占用的内存, 两个行被锁
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
//正在等待获取的锁
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
//这里说的是record lock 以及位置内存信息等,关键是后面的 lock_mode X locks gap before rec insert intention waiting 后面会说到
RECORD LOCKS space id 55 page no 4 n bits 112 index orderid of table `db0`.`t_test` trx id 9181426 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 42 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000384; asc     ;;
 1: len 4; hex 800000d2; asc     ;;

//事务2
*** (2) TRANSACTION:
//~~~
TRANSACTION 9181425, ACTIVE 36 sec inserting
//一张表被用到,一张表被锁
mysql tables in use 1, locked 1
//等待四个锁,三行被锁
4 lock struct(s), heap size 1136, 3 row lock(s)
//持有的锁
*** (2) HOLDS THE LOCK(S):
//这里依然说的是record lock 并且说明了具体的位置,重点依然是后面的 lock_mode X locks gap before rec
Record lock
RECORD LOCKS space id 55 page no 4 n bits 112 index orderid of table `db0`.`t_test` trx id 9181425 lock_mode X locks gap before rec
Record lock, heap no 42 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000384; asc     ;;
 1: len 4; hex 800000d2; asc     ;;
//正在等待获取的锁
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
//这里跟事务1的一样,同样的跟上面持有的锁一样
RECORD LOCKS space id 55 page no 4 n bits 112 index orderid of table `db0`.`t_test` trx id 9181425 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 42 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000384; asc     ;;
 1: len 4; hex 800000d2; asc     ;;
//mysql回滚了事务1,发生死锁时,mysql会回滚开销更小的事务,这里开销一样,根据入栈顺序选择回滚后入栈的
*** WE ROLL BACK TRANSACTION (1)

大体看了下死锁日志,虽然有注释,相信大家依然会有疑问

1. 表锁哪里来的
2. 为什么是record lock
3. 事务1没有持有锁,死锁怎么发生的(死锁发生在于相互持有对方需要的资源,都在等待对方释放)
4. 重点写出的那个信息代表什么意思

接下来一一解释,可能会涉及到很多其他的知识,这里通过一个例子结合一起说明

创建一张表,给了一个order_id字段唯一索引,用于验证 insert duplicate key update语法

CREATE TABLE `t_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `order_id` int(11) NOT NULL,
  `desc` varchar(255) DEFAULT NULL,
  `remark` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `orderid` (`order_id`)
) ENGINE=InnoDB AUTO_INCREMENT=222 DEFAULT CHARSET=utf8;

1.表锁哪里来的

session1 执行

BEGIN;
INSERT into t_test (`name`,order_id,`desc`,remark)values('a11',999,'test','he') ON DUPLICATE key UPDATE remark='dead101';

这里有个细节,结果影响行数是1,我还没提交这个事务,行数已经显示了

BEGIN
> OK
> 时间: 0.037s

INSERT into t_test (`name`,order_id,`desc`,remark)values('a11',999,'test','he') ON DUPLICATE key UPDATE remark='dead101'
> Affected rows: 1
> 时间: 0.038s

然后在session2执行任意的操作表数据sql

begin;
INSERT into t_test (`name`,order_id,`desc`,remark)values('a41',10000,'test','he') ON DUPLICATE key UPDATE remark='dead100',`desc`='dead100';

返回结果只有开启事务成功了,事务2被阻塞了,未显示行数

begin
> OK
> 时间: 0.037s

同理在session3执行如下,结果和事务2一样,被阻塞

begin;
INSERT into t_test (`name`,order_id,`desc`,remark)values('a5',19,'test','he') ON DUPLICATE key UPDATE remark='dead101',`desc`='dead100';

可见,这个语法在操作索引不存在的列时,整个表都不让操作了,那顺带提一下这个语法的原理

如果存在要操作的行时(与已有行冲突),删除已有行,执行新增

如果不存在要操作的行时,并且表里无其他数据,锁的是全表(这里实际上是gap锁的范围是[-∞,+∞]),如果存在行索引为order_id=20那么范围是[当前index,+∞]

因为不存在的时候是新增,不确定新增到哪里,所以干脆把可能会造成幻读的都锁了,至于怎么gap锁解决幻读的,这里不做赘述了,原理很简单,就是当前事务可能用到的数据就不让其他事务修改了

获取到gap后IX锁也成功获取到了

所以这里的表锁是 IX锁,意向锁都是表锁

补充一下mysql锁相关的基础

• record lock（RK）锁直接加在索引记录上面，锁住的是key
• gap lock（GK） 间隙锁,锁定一个范围,但不包括记录本身,作用是防止同一个事务的两次当前读产生幻读
• next key lock（NK） 行锁和间隙锁组合,锁住记录行和间隙
• insert intention lock（IK）插入意向锁,如果在插入前,该间隙已经被gap锁,那么insert会申请 插入意向锁,为了避免幻读,当其他事务持有该间隙的间隙锁,插入意向锁会被阻塞(gap锁不互斥)

提一下insert唯一索引加锁的逻辑

1. 先做UK冲突检测，如果存在目标行，先对目标行加S NK（S lock中的next key lock，下同），这个锁如果最终插入成功（该记录在等待期间被其他事务删除，此锁被同时删除）
2. 如果1成功，对对应行加X IK
3. 如果2成功，插入记录，并对记录加X RK（有可能是隐式锁）

2.为什么是record lock

所以这里也知道了,这里的record lock指的是next key锁,包含record key 和gap,不然会明确表示not gap
比如执行

begin;
SELECT * from t_test where order_id=999 for update;

查看innodb引擎日志

3.事务1没有持有锁,死锁怎么发生的(死锁发生在于相互持有对方需要的资源,都在等待对方释放)

其实这里双方都持有gap锁, 也就是日志里面说的record lock, 实际上是record lock + gap
都在等对方释放,所以发生了死锁
被回滚的事务1,释放掉了持有的锁

4.重点写出的那个信息代表什么意思

lock_mode X locks gap before rec insert intention waiting插入意向锁需要等待gap锁释放
对于这个状态,官方的解释如下:

INSERT sets an exclusive lock on the inserted row. This lock is an index-record lock, not a next-key lock (that is, there is no gap lock) and does not prevent other sessions from inserting into the gap before the inserted row.Prior to inserting the row, a type of gap lock called an insertion intention gap lock is set. This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap.If a duplicate-key error occurs, a shared lock on the duplicate index record is set. This use of a shared lock can result in deadlock should there be multiple sessions trying to insert the same row if another session already has an exclusive lock.

翻译过来就是

在事务插入数据过程中，为防止其他事务向索引上该位置插入数据，会在插入之前先申请插入意向范围锁，而如果申请插入意向范围锁被阻塞，则事务处于gap before rec insert intention waiting的等待状态。

锁的模式

• LOCK_S（读锁，共享锁）
• LOCK_X（写锁，排它锁）

锁的属性

• LOCK_REC_NOT_GAP （锁记录）
• LOCK_GAP （锁记录前的GAP）
• LOCK_ORDINARY （同时锁记录+记录前的GAP 。传说中的Next Key锁）
• LOCK_INSERT_INTENTION （插入意向锁，其实是特殊的GAP锁）

锁的属性可以与锁模式任意组合, 比如

• locks gap before rec 表示gap 锁
• locks rec but not gap 表示记录锁,非gap锁
• insert intention 插入意向锁
• waiting 表示等待

推断

因为S锁和gap加的时候都是不互斥的
所以这里推断

1. 事务1 执行但是还未提交的时候.此时对于[index,+∞)加了next key (gap)锁(S),并且获取了插入意向锁
2. 此时事务2和事务3也都对于[index+1,+∞)加了next key (gap)锁(S),但是由于事务1还未提交,所以获取插入意向锁时阻塞
3. 事务1提交,释放gap锁,事务2和事务3都在获取插入意向锁时等待对方gap锁释放,导致死锁发生
4. mysql选择后加入的事务3回滚,

事务1 的该语法获取到了

验证

事务1

BEGIN;
INSERT into t_test (`name`,order_id,`desc`,remark)values('a11',10,'test','he') ON DUPLICATE key UPDATE remark='dead101';

事务2

begin;
INSERT into t_test (`name`,order_id,`desc`,remark)values('a41',11,'test','he') ON DUPLICATE key UPDATE remark='dead100',`desc`='dead100';

执行完事务2,可以发现,事务2在获取插入意向锁的时候被阻塞,需要等待事务1的gap锁释放

事务3

begin;
INSERT into t_test (`name`,order_id,`desc`,remark)values('a5',11,'test','he') ON DUPLICATE key UPDATE remark='dead101',`desc`='dead100';

可见,事务3也在等待事务1的gap锁释放

此时将事务1提交

COMMIT;

死锁发生

那么如果我手动在事务1里先加gap锁,后插入一条数据

begin;
SELECT * from t_test where order_id=101for update;
INSERT into t_test (`name`,order_id,`desc`,remark)values('a5',101,'test','he');

事务2

begin;
SELECT * from t_test where order_id=101 for update;
INSERT into t_test (`name`,order_id,`desc`,remark)values('a5',101,'test','he');

事务3

begin;
SELECT * from t_test where order_id=101 for update;
INSERT into t_test (`name`,order_id,`desc`,remark)values('a5',101,'test','he');

根据我们的猜测,那么会在提交第一个事务的时候发生死锁,为什么呢?

1. 事务1获取gap锁,并且获取了插入意向锁
2. 事务2和事务3获取了gap锁等待获取插入意向锁
3. 发生死锁

通过select * from information_schema.innodb_locks;也可以看到其实是next key锁(特殊的gap),

而如果把上面的gap锁换成S锁

SELECT * from t_test where order_id=101lock in SHARE MODE;

则不会发生死锁

所以验证了猜想是gap锁的问题,也和死锁日志相符合,

至于怎么解决这个问题,要么升级到5.7.3版本以上,要么避免gap锁加到同一个范围内

后面看下5.7.3官方是怎么解决的,再来补充

最后贴一个mysql各锁容斥关系表