MYSQL 8.0 -- 事务中删除不存在的记录导致死锁_supremum pseudo-record

最近开发的某个功能中，线上偶尔会爆出死锁异常。再大佬同事的帮助下，最终排查出了原因，在此记录一下。

业务描述

  在业绩信息维护中，可以维护相关人员列表，相关人员的信息在数据库中是单独存放的，通过业绩ID关联。
  
  为了防止并发情况的出现，每个业绩操作时会加锁，其他人不能操作。
  这个功能一直没有什么问题，新增的时候插入人员信息，修改的时候先删除再插入人员信息，上线后运行的很正常。
  直到有一天添加了暂存功能，暂存分两种情况：一种是第一次维护的业绩直接暂存，另一种是之前暂存的业绩调整后继续暂存，分别对应着新增业绩和修改业绩的功能。
  当时心想新增和修改的校验都是一样的，索性直接把新增和修改功能的代码直接搬了过来放到了一个接口里面。然而修改业绩时处理人员部分的逻辑会先清空对应人员表，新增业绩时则是直接插入，当时的我认为，新增的时候去清空一下对应表应该也没什么关系吧，顶多清不掉任何数据，多做一个无意义的操作，应该没什么问题，测试了一下功能正常，部署上线，然后人就麻了。

事务中删除行时锁的表现

先创建一个测试表 test_table1

我们先在一个事务里面删除一行存在的记录，但不提交，别执行 commit;

-- 开启事务
start transaction;
-- 不自动提交
set autocommit = 0;
delete from test_table1 where id = 8;

-- 提交事务
commit;
1
2
3
4
5
6
7
8

然后执行以下命令查看事务对应的锁信息

select * from performance_schema.data_locks;
1

可以看到，有一行对主键为 8 的行锁写锁。此时当我们在其他事务中执行 delete from test_table1 where id = 8; 时会一直阻塞直到超时。
当我们第一个事务 commit 之后，对应的行锁会释放，事务锁信息被清空了

当我们不按照主键删除，而是根据查询条件删除，又是另一种情况：

-- 开启事务
start transaction;
-- 不自动提交
set autocommit = 0;
delete from test_table1 where name = 'name3';
-- 提交事务
commit;
1
2
3
4
5
6
7

会显示锁住了多行，这其实是邻键锁的表现，具体情况可参考该文章 https://cloud.tencent.com/developer/article/1971381

而我的情况要比上面表现得更简单点，我在新增的时候触发了删除操作，删除了不存在的记录。
在事务中删除不存在的行：

delete from test_table1 where id = 500;
1

查看事务锁

supremum pseudo-record ，相当于比索引中所有值都大,但却不存在索引

比如在如下表中，一共三行记录，最大主键是13，会给主键大于 13 的所有行加锁，这个时候我们再其他事物中以 id 自增的方式插入信息时就会阻塞，但是当我们指定 ID 为 1（小于等于13） 时，插入不受影响。

场景重现

到这里，我们在实现思索场景就比较简单了。
1、开启一个事务一，删除不存在的行信息（不提交）
2、开启一个事务二，删除不存在的行信息（不提交），锁的状态如下，两个事务都持有 supremum pseudo-record 这个锁

3、在事务一中插入数据 ，不提交，再次查看锁状态。事务一多了一条锁等待，等待事务二释放该锁。

insert into test_table1(name)values('name3');
1

4、此时在事务二中在执行插入语句，则会直接爆出异常 Deadlock found when trying to get lock; try restarting transaction，
原因是事务一和事务二等待彼此的 supremum pseudo-record 锁释放，造成死锁。

问题处理

我的处理方式比较简单，因为每个业绩操作时加了锁，所以业绩信息在同一时刻只能有一个人操作，不用考虑同一业绩的并发。我只要在删除人员表之前先查询一下表中存在该业绩的相关人员即可，有则删除，没有不做处理。