一篇學會MySQL死鎖-更新插入導致死鎖
1 問題背景
前段時間，領導說我們業務量大漲，部門新增HC，讓我們趕緊招人。
領導：經過大家的共同努力和不懈奮斗，我們的業務量實現了顯著的大漲，這是對我們團隊能力和工作成果的最好證明。為了更好地應對業務量的增長，滿足客戶的需求，我們決定在部門內新增HC，大家行動起來吧。
。。。
面試官：你好，今天想和你聊聊MySQL數據庫中的死鎖問題。首先，你能解釋一下什么是死鎖嗎？
應聘者：死鎖是指兩個或多個事務在執行過程中，因爭奪資源而造成的一種互相等待的現象，若無外力作用，這些事務都將無法向前推進。在MySQL中，這通常發生在多個事務嘗試以不同順序訪問相同的資源（如表或行）時。
面試官：很好，那么MySQL中死鎖發生的常見原因有哪些？
應聘者：MySQL中死鎖的常見原因包括：
不同事務交叉鎖定資源：當兩個或多個事務相互等待對方釋放鎖定的資源時，就可能發生死鎖。索引使用不當：不恰當的索引使用可能導致查詢鎖定更多行，增加死鎖的風險。大量數據的修改：在處理大量數據時，尤其是同時修改多個表或行時，更容易發生死鎖。鎖升級：在某些情況下，低級鎖（如行鎖）可能會升級為更高級別的鎖（如表鎖），這也會增加死鎖的可能性。
面試官：如何分析一個SQL都加了哪些鎖呢？你需要哪些前置信息呢？
應聘者：好的，我先說一下我的理解。
加鎖規則：兩個原則、兩個優化、一個 bug
原則 1：加鎖的基本單位是 next-key lock，前開后閉區間
原則 2：查找過程中訪問到的對象才會加鎖
優化 1：索引上的等值查詢，給唯一索引加鎖的時候，匹配上數據，next-key lock 退化為行鎖
優化 2：索引上的等值查詢，向右遍歷時且最后一個值不滿足等值條件的時候，next-key lock 退化為間隙鎖
一個 bug：唯一索引上的范圍查詢會訪問到不滿足條件的第一個值為止
面試官：那我有個案例，你分析分析都加了哪些鎖？是否會產生死鎖。我有一個回收單表，回收單id+回收類型 是唯一索引， 我先根據回收單id A更新回收單A狀態，（如果數據不存在）再插入回收單A數據。我再根據回收單id B更新回收單B狀態，（如果數據不存在）再插入回收單B數據。
應聘者：。。。
上面是國內開發者在找工作過程中常被問到的問題，大家吐槽是八股文，過度依賴背誦，加劇應試教育的傾向，使得應聘者更加注重面試通過率而非實際技能的提升。
其實有些八股文是實際遇到問題的經驗總結。
這個問題是我們在線上每日錯誤日志清零時發現排查的死鎖問題。在這里介紹一下，給大家遇到類似問題時提供一個排查思路。
2 線上問題
2.1 線上異常日志
線上錯誤日志，從日志第2行可以發現是發生了死鎖， 從第6行可以發現是插入了數據時發生了異常， 從20行可以看到異常的方法。
圖片
根據日志找到業務代碼，發現業務代碼邏輯是：先把回收單id 對應 歷史的回收單都更新為失效，然后再插入回收單id對應的新的回收單數據。
2.2 數據準備
首先在測試庫里建表，并準備相關的原數據。1、使用的mysql版本：線上5.7.21，測試8.0.322、配置的隔離級別：REPEATABLE-READ 創建個checkout_detail表，分別插入三條數據。
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" );#模擬線上數據
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" );#模擬線上數據
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" ); #模擬線上數據2.3 問題復現
2.3.1 執行流程
執行時間順序 | 事務A | 事務B |
|
| |
1 |
| |
2 |
| |
3 |
| |
4 |
|
大家可以思考一下，這個執行過程中都會加哪些鎖？會發生鎖等待嗎？會發生死鎖嗎？
2.3.2 死鎖排查
上面執行第3步會鎖等待，執行第4步會死鎖。
執行如下SQL:
SHOW ENGINE INNODB STATUS;它是MySQL 中一個非常有用的命令，它用于顯示 InnoDB 存儲引擎的當前狀態信息。這個命令對于診斷 InnoDB 存儲引擎的問題、監控性能以及理解內部操作非常有幫助。
輸出的內容非常多，我們只關注鎖信息就行，找到LATEST DETECTED DEADLOCK 最近一次死鎖信息如下：
圖片
2.3.3 死鎖日志分析
現在讓我們來分析這個死鎖日志，我只會分析我們需要的信息。
2.3.3.1 事務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/>- 事務狀態 事務ID：23087 操作：正在進行插入（INSERT）操作。
- 鎖等待情況 鎖結構數量：3個鎖結構，這表明MySQL為該事務準備了多個鎖來管理對數據的訪問。行鎖數量：2個行鎖，說明事務試圖在checkout_detail表中的兩行數據上設置鎖。
- 事務阻塞 LOCK WAIT：這表明事務正在等待其他事務釋放鎖。在當前情況下，它正在等待能夠插入或更新它試圖操作的兩行數據。
2.3.3.2 事務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/>事務23087持有的鎖是一個針對uniq_idx_recycle_order_id_contrast_type索引的X（排他）鎖，但它實際上鎖定的是索引中的“supremum”偽記錄。這通常意味著事務并沒有鎖定任何具體的、存在的數據行，而是鎖定了一個表示索引末尾的虛擬記錄。
2.3.3.3 事務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/>事務23087正在等待一個插入意向鎖（lock_mode X insert intention waiting）被授予
2.3.3.4 事務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" )- 事務狀態 事務ID：23088 操作：正在進行插入（INSERT）操作。
- 鎖等待情況 鎖結構數量：3個鎖結構，這表明MySQL為該事務準備了多個鎖來管理對數據的訪問。行鎖數量：2個行鎖，說明事務試圖在checkout_detail表中的兩行數據上設置鎖。
- 事務阻塞 LOCK WAIT：這表明事務正在等待其他事務釋放鎖。在當前情況下，它正在等待能夠插入或更新它試圖操作的兩行數據。
2.3.3.5 事務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;;事務23088持有的鎖是一個針對uniq_idx_recycle_order_id_contrast_type索引的X（排他）鎖，但它實際上鎖定的是索引中的“supremum”偽記錄。這通常意味著事務并沒有鎖定任何具體的、存在的數據行，而是鎖定了一個表示索引末尾的虛擬記錄。
2.3.3.6 事務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;;事務23088正在等待一個插入意向鎖（lock_mode X insert intention waiting）被授予
3 分析原因
參考MySQL的官方文檔。
間隙鎖（Gap Locks）是一種特殊的鎖機制，用于鎖定索引記錄之間的間隙，或者第一個索引記錄之前的間隙以及最后一個索引記錄之后的間隙。這種鎖的主要目的是防止其他事務在這些間隙中插入新的記錄，從而維護數據的一致性和隔離性。
插入意向鎖（Insert Intention Locks） 是InnoDB存儲引擎在插入操作前設置的一種間隙鎖（Gap Locks）。這種鎖的目的是在多個事務嘗試向同一個索引間隙中插入不同位置的數據時，能夠并行執行而不需要相互等待。
可以得到索引如下加鎖示意圖
索引上添加鎖
鎖總是鎖定索引記錄。如果要鎖定的是最后一條記錄之后的區間，防止有人在這個區間插入數據，那么mysql就會鎖定隱藏的最大記錄
索引記錄關聯的鎖
4 解決方法
1、查看死鎖日志時，先看一下發生死鎖的事務等待獲取鎖的語句， 都有哪些語句發生死鎖。
2、根據死鎖語句，找到相關到業務代碼（如果有日志，直接根據日志找到業務代碼也行）。
3、根據業務代碼執行流程，來分析死鎖發生過程。（注意分析數據存在，數據不存在時的加鎖區別）
發現了問題原因，那么解決方案就很簡單了。在這個場景下是：先查詢數據是否存在，如果數據存在則更新，如果數據不存在再插入。
5 總結
- 兩個事務即使生成的間隙鎖的范圍是一樣的，也不會發生沖突，因為間隙鎖目的是為了防止其他事務插入數據，因此間隙鎖與間隙鎖之間是相互兼容的。
- 在執行插入語句時，如果插入的記錄在其他事務持有間隙鎖范圍內，插入語句就會被阻塞，因為插入語句在碰到間隙鎖時，會生成一個插入意向鎖，然后插入意向鎖和間隙鎖之間是互斥的關系。
