1 問題背景

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

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

。。。

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

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

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

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

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

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

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

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

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

原則 2：查找過程中訪問到的對象才會加鎖

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

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

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

面試官：那我有個案例，你分析分析都加了哪些鎖？是否會產(chǎn)生死鎖。我有一個回收單表，回收單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)聘者：。。。

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

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

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

2 線上問題

2.1 線上異常日志

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

圖片

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

2.2 數(shù)據(jù)準備

首先在測試庫里建表，并準備相關(guān)的原數(shù)據(jù)。1、使用的mysql版本：線上5.7.21，測試8.0.322、配置的隔離級別：REPEATABLE-READ 創(chuàng)建個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 '確認回收時間',
  `contrast_type` int(4) NOT NULL COMMENT '對比類型：1:售前、2:后驗、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='后驗詳情表';
 
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í)行流程

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

2.3.2 死鎖排查

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

執(zhí)行如下SQL:

SHOW ENGINE INNODB STATUS;

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

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

圖片

2.3.3 死鎖日志分析

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

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 操作：正在進行插入（INSERT）操作。
2. 鎖等待情況 鎖結(jié)構(gòu)數(shù)量：3個鎖結(jié)構(gòu)，這表明MySQL為該事務(wù)準備了多個鎖來管理對數(shù)據(jù)的訪問。行鎖數(shù)量：2個行鎖，說明事務(wù)試圖在checkout_detail表中的兩行數(shù)據(jù)上設(shè)置鎖。
3. 事務(wù)阻塞 LOCK WAIT：這表明事務(wù)正在等待其他事務(wù)釋放鎖。在當前情況下，它正在等待能夠插入或更新它試圖操作的兩行數(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持有的鎖是一個針對uniq_idx_recycle_order_id_contrast_type索引的X（排他）鎖，但它實際上鎖定的是索引中的“supremum”偽記錄。這通常意味著事務(wù)并沒有鎖定任何具體的、存在的數(shù)據(jù)行，而是鎖定了一個表示索引末尾的虛擬記錄。

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正在等待一個插入意向鎖（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 操作：正在進行插入（INSERT）操作。
2. 鎖等待情況 鎖結(jié)構(gòu)數(shù)量：3個鎖結(jié)構(gòu)，這表明MySQL為該事務(wù)準備了多個鎖來管理對數(shù)據(jù)的訪問。行鎖數(shù)量：2個行鎖，說明事務(wù)試圖在checkout_detail表中的兩行數(shù)據(jù)上設(shè)置鎖。
3. 事務(wù)阻塞 LOCK WAIT：這表明事務(wù)正在等待其他事務(wù)釋放鎖。在當前情況下，它正在等待能夠插入或更新它試圖操作的兩行數(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持有的鎖是一個針對uniq_idx_recycle_order_id_contrast_type索引的X（排他）鎖，但它實際上鎖定的是索引中的“supremum”偽記錄。這通常意味著事務(wù)并沒有鎖定任何具體的、存在的數(shù)據(jù)行，而是鎖定了一個表示索引末尾的虛擬記錄。

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正在等待一個插入意向鎖（lock_mode X insert intention waiting）被授予

3 分析原因

參考MySQL的官方文檔。

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

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

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

索引上添加鎖

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

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

4 解決方法

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

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

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

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

5 總結(jié)

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