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。現在推測死鎖產生邏輯如下：

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子句。

在與研發人員溝通過后，研發人員選擇了方案二。在更新換版后，死鎖問題未再發生。