1、情况描述
生产环境中的 MySQL8.0 数据库出现死锁告警。起初并未引起注意，但后续频繁发生了相同的死锁，开始着手进行分析。
因为生产的保密性，所以创建测试表，在这里模拟生产发生的死锁。
创建测试表
--创建评分表
create table pfb(
pk_id varchar(32) default(REPLACE(UUID(),'-','')),
task_id varchar(32) ,
score varchar(32) ,
type varchar(32) ,
primary key (pk_id)
);
--创建历史日志表
create table log_pf_his(
pk_id varchar(32) default(REPLACE(UUID(),'-','')),
task_id varchar(32) ,
item varchar(32) ,
primary key (pk_id)
);
对于产生死锁的语句，在后文逐步揭示。
2、排查分析
2.1、拆解死锁信息
show engine innodb status \G;
（1）事务开始顺序
如上图所示， (1) TRANSACTION 的事务ID是 2070，(2) TRANSACTION 的事务ID是 2064；说明 (2) TRANSACTION 先于 (1) TRANSACTION 开始。
（2）死锁发生时，正在执行的操作
如上图所示，死锁发生时，2064事务与2070事务正在执行的操作是一致的。都是在对log_pf_his表执行 insert into … values(… select …) 操作
insert into log_pf_his(task_id,item)
values('a12052219',
(select concat(type,':',score)
from pfb where task_id='a12052219'))
（3）两个事务持有的锁信息
如上图所示，2064事务与2070事务都持有对于 tq.pfb 表的非间隙 X 锁 。
RECORD LOCKS space id 2 page no 4 n bits 72 index PRIMARY \ of table `tq`.`pfb` trx id 2064 lock_mode X locks rec but not gap
RECORD LOCKS space id 2 page no 4 n bits 72 index PRIMARY \ of table `tq`.`pfb` trx id 2070 lock_mode X locks rec but not gap
（4）两个事务等待的锁的信息
如上图所示，2064事务与2070事务都在请求对于 tq.pfb 表的非间隙 S 锁。
RECORD LOCKS space id 2 page no 4 n bits 72 index PRIMARY \ of table `tq`.`pfb` trx id 2064 lock mode S locks rec but not gap waiting
RECORD LOCKS space id 2 page no 4 n bits 72 index PRIMARY \ of table `tq`.`pfb` trx id 2070 lock mode S locks rec but not gap waiting
2.2、分析死锁信息
如果对MySQL各种操作产生的锁模式不了解，可能会有疑问：两个事务正在对 log_pf_his 表进行insert操作，为什么持有的锁和请求的锁都是在pfb表上？下面进行分析回答。
（1）请求的锁为什么在pfb上
上文中死锁发生时，事务正在执行的操作是 insert into … values(… select …) 操作。
MySQL数据库 在RC隔离级别下，insert … select 操作会对insert的表请求表级别的【IX锁】，对select的表请求表界别的【IS锁】、行级别的【 S 锁,REC_NOT_GAP】 。
（2）为什么持有对pfb的X锁
这个问题，也是这次死锁的关键点。
我们可以猜想，事务中可能并不只有对log_pf_his表的insert into … values(… select …) 操作。
既然X锁在pfb表上，那么事务中大概率是对pfb表做了操作，而且这个操作必须发生在insert into … values(… select …) 操作之前，且未提交。
2.3、业务沟通
在分析完后，与研发沟通此问题，询问在这两个事务中除了对log_pf_his表的insert into … values(… select …) 操作，是否还有其他操作，尤其是对pfb的操作。研发人员表示事务中在此之前还有对于pfb的insert操作，且一个事务中的所有操作最终都是一起提交的。
验证了之前的猜想。
为了下面的排版稍显美观，给pfb表起个别名为a，给log_pf_his表起个别名为b。现在推测死锁产生逻辑如下：
| TIME | TRX1 | TRX1说明 | TRX2 | TRX2说明 |
|---|---|---|---|---|
| TIME1：sql1 | insert into a values(…); | 给表a加了IX表锁 | ||
| TIME2：sql2 | insert into b values(…,(select…from a where task_id=‘xx’)); | （1）给表b加了IX表锁；（2）给表a加了IX锁；（3）给表a加了X,REC_NOT_GAP记录锁；因为此时sql1还未提交，所以需要给其加X锁，防止其他事务读到或者修改未提交的insert数据。（如果sql1已提交，则给a表加的是S,REC_NOT_GAP记录锁） | ||
| TIME3：sql3 | insert into a values(…); | 给表a加了IX表锁，因为是IX表锁，所以此时a表上的X,REC_NOT_GAP记录锁并不会阻塞TRX2的sql3 | ||
| TIME4：sql4 | insert into b values(…,(select…from a where task_id=‘xx’)); | （1）给表b加了IX表锁IX表锁之间是兼容的，所以可以顺利加上IX表锁（2）给表a加了X,REC_NOT_GAP记录锁，与上文同理；（3）TRX2的sql4想给表a加S,REC_NOT_GAP记录锁，用于锁定TRX1的sql1插入表a的记录，但是因为此时TRX1对表a的该行持有X,REC_NOT_GAP记录锁，**S与X锁冲突，所以只能等待；**此时TRX2也就被阻塞了； | ||
| TIME5：sql5 | insert into a values(…); | 给表a加了IX锁，但因为TRX1已经给表a加过IX锁了，所以在performance_schema.data_locks视图里不会再看到新增加的IX锁 | ||
| TIME6：sql6 | insert into b values(…,(select…from a where task_id=‘xx’)); | 此时sql6想给表a加S,REC_NOT_GAP记录锁，用于锁定TRX2的sql3插入表a的记录；**此时TRX1与TRX2互相等待对方对表a持有的X锁，都想给表a加上S锁，于是发生死锁。**此时TRX2的sql4发生回滚 |
2.4、情景复现
两个事务中完整的操作此时也展示出来了，如下所示。
2064事务：
--time1
insert into pfb(task_id,score,type) values('a12052217','87','a');
insert into log_pf_his(task_id,item)
values('a12052217',
(select concat(type,':',score)
from pfb where task_id='a12052217'));
--time2
insert into pfb(task_id,score,type) values('a12052219','92','b');
insert into log_pf_his(task_id,item)
values('a12052219',
(select concat(type,':',score)
from pfb where task_id='a12052219'));
2070事务：
--time1
insert into pfb(task_id,score,type) values('a12052219','92','b');
insert into log_pf_his(task_id,item)
values('a12052219',
(select concat(type,':',score)
from pfb where task_id='a12052219'));--此时操作已被阻塞
--time2
--此时死锁发生
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
3、解决方案
在了解清楚死锁的成因之后，解决方案就比较清晰了。
方案一：
先提交对于pfb表的insert操作，再对log_pf_his表进行insert into … values(… select …) 操作。
方案二：
对log_pf_his表的insert into … values(… select …) 操作 修改为 insert into … values() ，不要在values中写select子句。
在与研发人员沟通过后，研发人员选择了方案二。在更新换版后，死锁问题未再发生。
