1 問題背景

前段時(shí)間，領(lǐng)導(dǎo)說我們業(yè)務(wù)量大漲，部門新增HC，讓我們趕緊招人。

領(lǐng)導(dǎo)：經(jīng)過大家的共同努力和不懈奮斗，我們的業(yè)務(wù)量實(shí)現(xiàn)了顯著的大漲，這是對(duì)我們團(tuán)隊(duì)能力和工作成果的最好證明。為了更好地應(yīng)對(duì)業(yè)務(wù)量的增長(zhǎng)，滿足客戶的需求，我們決定在部門內(nèi)新增HC，大家行動(dòng)起來吧。

。。。

面試官：你好，今天想和你聊聊MySQL數(shù)據(jù)庫中的死鎖問題。首先，你能解釋一下什么是死鎖嗎？

應(yīng)聘者：死鎖是指兩個(gè)或多個(gè)事務(wù)在執(zhí)行過程中，因爭(zhēng)奪資源而造成的一種互相等待的現(xiàn)象，若無外力作用，這些事務(wù)都將無法向前推進(jìn)。在MySQL中，這通常發(fā)生在多個(gè)事務(wù)嘗試以不同順序訪問相同的資源（如表或行）時(shí)。

面試官：很好，那么MySQL中死鎖發(fā)生的常見原因有哪些？

應(yīng)聘者：MySQL中死鎖的常見原因包括：

不同事務(wù)交叉鎖定資源：當(dāng)兩個(gè)或多個(gè)事務(wù)相互等待對(duì)方釋放鎖定的資源時(shí)，就可能發(fā)生死鎖。索引使用不當(dāng)：不恰當(dāng)?shù)乃饕褂每赡軐?dǎo)致查詢鎖定更多行，增加死鎖的風(fēng)險(xiǎn)。大量數(shù)據(jù)的修改：在處理大量數(shù)據(jù)時(shí)，尤其是同時(shí)修改多個(gè)表或行時(shí)，更容易發(fā)生死鎖。鎖升級(jí)：在某些情況下，低級(jí)鎖（如行鎖）可能會(huì)升級(jí)為更高級(jí)別的鎖（如表鎖），這也會(huì)增加死鎖的可能性。

面試官：如何分析一個(gè)SQL都加了哪些鎖呢？你需要哪些前置信息呢？

應(yīng)聘者：好的，我先說一下我的理解。

加鎖規(guī)則：兩個(gè)原則、兩個(gè)優(yōu)化、一個(gè) bug

原則 1：加鎖的基本單位是 next-key lock，前開后閉區(qū)間

原則 2：查找過程中訪問到的對(duì)象才會(huì)加鎖

優(yōu)化 1：索引上的等值查詢，給唯一索引加鎖的時(shí)候，匹配上數(shù)據(jù)，next-key lock 退化為行鎖

優(yōu)化 2：索引上的等值查詢，向右遍歷時(shí)且最后一個(gè)值不滿足等值條件的時(shí)候，next-key lock 退化為間隙鎖

一個(gè) bug：唯一索引上的范圍查詢會(huì)訪問到不滿足條件的第一個(gè)值為止

面試官：那我有個(gè)案例，你分析分析都加了哪些鎖？是否會(huì)產(chǎn)生死鎖。我有一個(gè)回收單表，回收單id+回收類型 是唯一索引， 我先根據(jù)回收單id A更新回收單A狀態(tài)，（如果數(shù)據(jù)不存在）再插入回收單A數(shù)據(jù)。我再根據(jù)回收單id B更新回收單B狀態(tài)，（如果數(shù)據(jù)不存在）再插入回收單B數(shù)據(jù)。

應(yīng)聘者：。。。

上面是國(guó)內(nèi)開發(fā)者在找工作過程中常被問到的問題，大家吐槽是八股文，過度依賴背誦，加劇應(yīng)試教育的傾向，使得應(yīng)聘者更加注重面試通過率而非實(shí)際技能的提升。

其實(shí)有些八股文是實(shí)際遇到問題的經(jīng)驗(yàn)總結(jié)。

這個(gè)問題是我們?cè)诰€上每日錯(cuò)誤日志清零時(shí)發(fā)現(xiàn)排查的死鎖問題。在這里介紹一下，給大家遇到類似問題時(shí)提供一個(gè)排查思路。

2 線上問題

2.1 線上異常日志

線上錯(cuò)誤日志，從日志第2行可以發(fā)現(xiàn)是發(fā)生了死鎖， 從第6行可以發(fā)現(xiàn)是插入了數(shù)據(jù)時(shí)發(fā)生了異常， 從20行可以看到異常的方法。

圖片

根據(jù)日志找到業(yè)務(wù)代碼，發(fā)現(xiàn)業(yè)務(wù)代碼邏輯是：先把回收單id 對(duì)應(yīng) 歷史的回收單都更新為失效，然后再插入回收單id對(duì)應(yīng)的新的回收單數(shù)據(jù)。

2.2 數(shù)據(jù)準(zhǔn)備

首先在測(cè)試庫里建表，并準(zhǔn)備相關(guān)的原數(shù)據(jù)。1、使用的mysql版本：線上5.7.21，測(cè)試8.0.322、配置的隔離級(jí)別：REPEATABLE-READ 創(chuàng)建個(gè)checkout_detail表，分別插入三條數(shù)據(jù)。

CREATE TABLE `checkout_detail` (
  `id` bigint(20) NOT NULL COMMENT '主鍵id',
  `recycle_order_id` bigint(20) NOT NULL COMMENT '回收單ID',
  `confirm_recycle_time` datetime NOT NULL COMMENT '確認(rèn)回收時(shí)間',
  `contrast_type` int(4) NOT NULL COMMENT '對(duì)比類型：1:售前、2:后驗(yàn)、3:售后',
  `remark` varchar(255) DEFAULT '' COMMENT '備注',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_idx_recycle_order_id_contrast_type` (`recycle_order_id`,`contrast_type`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='后驗(yàn)詳情表';
 
INSERT INTO checkout_detail ( id, recycle_order_id, confirm_recycle_time, contrast_type, remark )
VALUES
 ( 1, 1, '2024-07-15 19:56:01', 1, "回收單1" );#模擬線上數(shù)據(jù)
 INSERT INTO checkout_detail ( id, recycle_order_id, confirm_recycle_time, contrast_type, remark );
VALUES
 ( 2, 10, '2024-07-15 19:56:01', 2, "回收單10" );#模擬線上數(shù)據(jù)
 INSERT INTO checkout_detail ( id, recycle_order_id, confirm_recycle_time, contrast_type, remark );
VALUES
 ( 3, 20, '2024-07-15 19:56:01', 3, "回收單20" ); #模擬線上數(shù)據(jù)

2.3 問題復(fù)現(xiàn)

2.3.1 執(zhí)行流程

大家可以思考一下，這個(gè)執(zhí)行過程中都會(huì)加哪些鎖？會(huì)發(fā)生鎖等待嗎？會(huì)發(fā)生死鎖嗎？

2.3.2 死鎖排查

上面執(zhí)行第3步會(huì)鎖等待，執(zhí)行第4步會(huì)死鎖。

執(zhí)行如下SQL:

SHOW ENGINE INNODB STATUS;

它是MySQL 中一個(gè)非常有用的命令，它用于顯示 InnoDB 存儲(chǔ)引擎的當(dāng)前狀態(tài)信息。這個(gè)命令對(duì)于診斷 InnoDB 存儲(chǔ)引擎的問題、監(jiān)控性能以及理解內(nèi)部操作非常有幫助。

輸出的內(nèi)容非常多，我們只關(guān)注鎖信息就行，找到LATEST DETECTED DEADLOCK 最近一次死鎖信息如下：

圖片

2.3.3 死鎖日志分析

現(xiàn)在讓我們來分析這個(gè)死鎖日志，我只會(huì)分析我們需要的信息。

2.3.3.1 事務(wù)A23087信息

*** (1) "TRANSACTION":<br/>
TRANSACTION 23087, ACTIVE 22 sec inserting<br/>
mysql tables in use 1, locked 1<br/>
LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 1<br/>
MySQL thread id 9, OS thread handle 123145459134464, query id 1039 localhost 127.0.0.1 root update<br/>
INSERT INTO checkout_detail ( id, recycle_order_id, confirm_recycle_time, contrast_type, remark )VALUES( 30, 30, '2024-07-15 "19":56:01', 1, "插入回收單30" )<br/>

1. 事務(wù)狀態(tài) 事務(wù)ID：23087 操作：正在進(jìn)行插入（INSERT）操作。
2. 鎖等待情況 鎖結(jié)構(gòu)數(shù)量：3個(gè)鎖結(jié)構(gòu)，這表明MySQL為該事務(wù)準(zhǔn)備了多個(gè)鎖來管理對(duì)數(shù)據(jù)的訪問。行鎖數(shù)量：2個(gè)行鎖，說明事務(wù)試圖在checkout_detail表中的兩行數(shù)據(jù)上設(shè)置鎖。
3. 事務(wù)阻塞 LOCK WAIT：這表明事務(wù)正在等待其他事務(wù)釋放鎖。在當(dāng)前情況下，它正在等待能夠插入或更新它試圖操作的兩行數(shù)據(jù)。

2.3.3.2 事務(wù)23087持有鎖

*** (1) HOLDS THE "LOCK(S)":<br/>
RECORD LOCKS space id 4 page no 5 n bits 72 index uniq_idx_recycle_order_id_contrast_type of table `my_database`.`checkout_detail` trx id 23087 lock_mode X <br/>
Record lock, heap no 1 PHYSICAL "RECORD": n_fields 1; compact format; info bits 0<br/>
 "0": len 8; hex 73757072656d756d; asc supremum;;<br/>

事務(wù)23087持有的鎖是一個(gè)針對(duì)uniq_idx_recycle_order_id_contrast_type索引的X（排他）鎖，但它實(shí)際上鎖定的是索引中的“supremum”偽記錄。這通常意味著事務(wù)并沒有鎖定任何具體的、存在的數(shù)據(jù)行，而是鎖定了一個(gè)表示索引末尾的虛擬記錄。

2.3.3.3 事務(wù)23087等待鎖

*** (1) WAITING FOR THIS LOCK TO BE "GRANTED":<br/>
RECORD LOCKS space id 4 page no 5 n bits 72 index uniq_idx_recycle_order_id_contrast_type of table `my_database`.`checkout_detail` trx id 23087 lock_mode X insert intention waiting<br/>
Record lock, heap no 1 PHYSICAL "RECORD": n_fields 1; compact format; info bits 0<br/>
 "0": len 8; hex 73757072656d756d; asc supremum;;<br/>

事務(wù)23087正在等待一個(gè)插入意向鎖（lock_mode X insert intention waiting）被授予

2.3.3.4 事務(wù)23088信息

** (2) "TRANSACTION":
TRANSACTION 23088, ACTIVE 14 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 10, OS thread handle 123145460199424, query id 1043 localhost 127.0.0.1 root update
INSERT INTO checkout_detail ( id, recycle_order_id, confirm_recycle_time, contrast_type, remark )VALUES( 40, 40, '2024-07-15 "19":56:01', 1, "插入回收單40" )

1. 事務(wù)狀態(tài) 事務(wù)ID：23088 操作：正在進(jìn)行插入（INSERT）操作。
2. 鎖等待情況 鎖結(jié)構(gòu)數(shù)量：3個(gè)鎖結(jié)構(gòu)，這表明MySQL為該事務(wù)準(zhǔn)備了多個(gè)鎖來管理對(duì)數(shù)據(jù)的訪問。行鎖數(shù)量：2個(gè)行鎖，說明事務(wù)試圖在checkout_detail表中的兩行數(shù)據(jù)上設(shè)置鎖。
3. 事務(wù)阻塞 LOCK WAIT：這表明事務(wù)正在等待其他事務(wù)釋放鎖。在當(dāng)前情況下，它正在等待能夠插入或更新它試圖操作的兩行數(shù)據(jù)。

2.3.3.5 事務(wù)23088持有鎖

*** (2) HOLDS THE "LOCK(S)":
RECORD LOCKS space id 4 page no 5 n bits 72 index uniq_idx_recycle_order_id_contrast_type of table `my_database`.`checkout_detail` trx id 23088 lock_mode X
Record lock, heap no 1 PHYSICAL "RECORD": n_fields 1; compact format; info bits 0
 "0": len 8; hex 73757072656d756d; asc supremum;;

事務(wù)23088持有的鎖是一個(gè)針對(duì)uniq_idx_recycle_order_id_contrast_type索引的X（排他）鎖，但它實(shí)際上鎖定的是索引中的“supremum”偽記錄。這通常意味著事務(wù)并沒有鎖定任何具體的、存在的數(shù)據(jù)行，而是鎖定了一個(gè)表示索引末尾的虛擬記錄。

2.3.3.6 事務(wù)23088等待鎖

*** (2) WAITING FOR THIS LOCK TO BE "GRANTED":
RECORD LOCKS space id 4 page no 5 n bits 72 index uniq_idx_recycle_order_id_contrast_type of table `my_database`.`checkout_detail` trx id 23088 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL "RECORD": n_fields 1; compact format; info bits 0
 "0": len 8; hex 73757072656d756d; asc supremum;;

事務(wù)23088正在等待一個(gè)插入意向鎖（lock_mode X insert intention waiting）被授予

3 分析原因

參考MySQL的官方文檔。

間隙鎖（Gap Locks）是一種特殊的鎖機(jī)制，用于鎖定索引記錄之間的間隙，或者第一個(gè)索引記錄之前的間隙以及最后一個(gè)索引記錄之后的間隙。這種鎖的主要目的是防止其他事務(wù)在這些間隙中插入新的記錄，從而維護(hù)數(shù)據(jù)的一致性和隔離性。

插入意向鎖（Insert Intention Locks） 是InnoDB存儲(chǔ)引擎在插入操作前設(shè)置的一種間隙鎖（Gap Locks）。這種鎖的目的是在多個(gè)事務(wù)嘗試向同一個(gè)索引間隙中插入不同位置的數(shù)據(jù)時(shí)，能夠并行執(zhí)行而不需要相互等待。

可以得到索引如下加鎖示意圖

索引上添加鎖

鎖總是鎖定索引記錄。如果要鎖定的是最后一條記錄之后的區(qū)間，防止有人在這個(gè)區(qū)間插入數(shù)據(jù)，那么mysql就會(huì)鎖定隱藏的最大記錄

索引記錄關(guān)聯(lián)的鎖

4 解決方法

1、查看死鎖日志時(shí)，先看一下發(fā)生死鎖的事務(wù)等待獲取鎖的語句， 都有哪些語句發(fā)生死鎖。

2、根據(jù)死鎖語句，找到相關(guān)到業(yè)務(wù)代碼（如果有日志，直接根據(jù)日志找到業(yè)務(wù)代碼也行）。

3、根據(jù)業(yè)務(wù)代碼執(zhí)行流程，來分析死鎖發(fā)生過程。（注意分析數(shù)據(jù)存在，數(shù)據(jù)不存在時(shí)的加鎖區(qū)別）

發(fā)現(xiàn)了問題原因，那么解決方案就很簡(jiǎn)單了。在這個(gè)場(chǎng)景下是：先查詢數(shù)據(jù)是否存在，如果數(shù)據(jù)存在則更新，如果數(shù)據(jù)不存在再插入。

5 總結(jié)

• 兩個(gè)事務(wù)即使生成的間隙鎖的范圍是一樣的，也不會(huì)發(fā)生沖突，因?yàn)殚g隙鎖目的是為了防止其他事務(wù)插入數(shù)據(jù)，因此間隙鎖與間隙鎖之間是相互兼容的。
• 在執(zhí)行插入語句時(shí)，如果插入的記錄在其他事務(wù)持有間隙鎖范圍內(nèi)，插入語句就會(huì)被阻塞，因?yàn)椴迦胝Z句在碰到間隙鎖時(shí)，會(huì)生成一個(gè)插入意向鎖，然后插入意向鎖和間隙鎖之間是互斥的關(guān)系。