update.where無索引導致MySQL死鎖問題解決

2022-11-03 14:02:32

引言

隨著我被拉入一個新的群聊“生產環境死鎖問題排查解決”，打破了午後的悠然愜意，點開群聊秒送了一個648超級大禮包（業務不正常，死鎖異常紀錄檔輸出），領導怒斥並要求趕緊排除解決並總結經驗，剛好我略懂略懂一點MySQL鎖知識，這不得秀一下自己的實力

死鎖的紀錄檔

既然死鎖已經發生，也完全不要慌啊，按我說著做，一定能找到原因然後解決

觸發下面這條命令獲取到線索

SHOW ENGINE INNODB STATUS;

執行後你會得到一段讓人看了有點迷迷的死鎖紀錄檔，具體我們該怎麼分析死鎖，可以分成一下三步

檢視發生死鎖的事務（一）資訊（包括持有的鎖，等待的鎖）
檢視發生死鎖的事務（二）資訊（包括持有的鎖，等待的鎖）
檢視回滾的是事務（一）還是事務（二），做好異常業務恢復的方案
根據mysql的加鎖機制分析發生的死鎖的原因

由於死鎖紀錄檔過於長，下面的紀錄檔只擷取部分有用的記錄

------------------------
LATEST DETECTED DEADLOCK
------------------------
2022-10-14 15:51:34 0x1a00
發生死鎖的事務（一）
*** (1) TRANSACTION:
TRANSACTION 32828384, ACTIVE 0 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 12 lock struct(s), heap size 1128, 285 row lock(s)
MySQL thread id 26352, OS thread handle 8548, query id 987590925 WIN-6Q9NIAJLCDR 172.27.15.57 swgj updating
事務（一）導致死鎖的sql
update xxx
     SET STATUS = '2',
        DESCRIPTION = 'xxx',
        MODIFY_TIME = '2022-10-14 15:51:35.707' 
     WHERE  BATCH_NO = 'xxx'
        and SFSB = '1'
事務（一）持有的鎖
*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 8575 page no 82 n bits 104 index PRIMARY of table `swgj`.`spgl_xmspsxblxxxxb` trx id 32828384 lock_mode X
事務（一）持有鎖的資料記錄資訊 （supremum虛擬最大記錄）
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;
事務（一）持有鎖的資料記錄資訊 （哪一行資料被鎖了）
Record lock, heap no 2 PHYSICAL RECORD: n_fields 29; compact format; info bits 0
 0: len 30; hex 30663730333038302d313164662d346439642d626338662d393439333333; asc 0f703080-11df-4d9d-bc8f-949333; (total 36 bytes);
 1: len 6; hex 0000015129e1; asc    Q) ;;
 2: len 7; hex 010000402103cd; asc    @!  ;;
 3: len 6; hex 313635303036; asc 165006;;
 .....
 .....
事務（一）等待的鎖
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 8575 page no 37 n bits 112 index PRIMARY of table `swgj`.`spgl_xmspsxblxxxxb` trx id 32828384 lock_mode X waiting
事務（一）等待鎖的資料記錄資訊
Record lock, heap no 45 PHYSICAL RECORD: n_fields 29; compact format; info bits 0
 0: len 30; hex 32643736613832362d343763362d343861332d613038662d343539333061; asc 2d76a826-47c6-48a3-a08f-45930a; (total 36 bytes);
 1: len 6; hex 000001f4ebdd; asc       ;;
 2: len 7; hex 820000402b3c96; asc    @+< ;;
 3: SQL NULL;
 .....
 .....
 發生死鎖的事務（二）
 *** (2) TRANSACTION:
TRANSACTION 32828381, ACTIVE 5 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 125 lock struct(s), heap size 24696, 137 row lock(s), undo log entries 724
MySQL thread id 26357, OS thread handle 12700, query id 987590958 WIN-6Q9NIAJLCDR 172.27.15.57 swgj update
事務（二）導致死鎖的sql
insert into xxx
     ( ID, BATCH_NO, DFSJZJ,... ) 
     values ( 'xxx', 'xxx','xxx' )
事務（二）持有的鎖
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 8575 page no 37 n bits 112 index PRIMARY of table `swgj`.`spgl_xmspsxblxxxxb` trx id 32828381 lock_mode X locks rec but not gap
事務（二）持有鎖的資料記錄資訊
Record lock, heap no 45 PHYSICAL RECORD: n_fields 29; compact format; info bits 0
 0: len 30; hex 32643736613832362d343763362d343861332d613038662d343539333061; asc 2d76a826-47c6-48a3-a08f-45930a; (total 36 bytes);
 1: len 6; hex 000001f4ebdd; asc       ;;
 2: len 7; hex 820000402b3c96; asc    @+< ;;
 3: SQL NULL;
 ...
 ...
事務（二）等待的鎖
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 8575 page no 82 n bits 104 index PRIMARY of table `swgj`.`spgl_xmspsxblxxxxb` trx id 32828381 lock_mode X locks gap before rec insert intention waiting
事務（二）等待鎖的資料記錄資訊
Record lock, heap no 3 PHYSICAL RECORD: n_fields 29; compact format; info bits 0
 0: len 30; hex 30666435313133662d393462382d346636632d383362372d303463656536; asc 0fd5113f-94b8-4f6c-83b7-04cee6; (total 36 bytes);
 1: len 6; hex 000001dbb470; asc      p;;
 2: len 7; hex 020000013619a1; asc     6  ;;
 3: len 6; hex 313636333830; asc 166380;;
 ...
 ...

分析紀錄檔

從資料庫版本5.7、事務的隔離級別 REPEATABLE READ

官方檔案明確指出在 REPEATABLE READ 隔離級別下，預設查詢條件下是加 next-key locks (record locks + gap locks ) 或 gap locks，當查詢條件使用了唯一索引時，只會對當前查詢的唯一記錄進行加鎖，此時鎖為 record locks

官方檔案強勢佔位

MySQL 隔離級別

MySQL InnoDB 鎖的型別

MySQL InnoDB 中不同 SQL 語句如何置鎖

從死鎖紀錄檔資訊可以得出

根據事務id大小可得出事務（二）32828381 比事務（一）32828384 先執行
從紀錄檔中的 index PRIMARY 得出鎖是加在主鍵索引上
根據業務程式碼，事務（二）將執行 N 條同表插入 insert 語句，加上持有鎖資訊得出，事務（二）先插入了一條新資料A，並得到新資料A的行鎖 Record Locks
事務（一）執行 update 時直接阻塞，為什麼呢，直接給出答案吧，因為這條 update 的查詢條件是沒有索引，導致需要所有的記錄都要加 Record Locks 和 Gap Locks，接著由於事務（二）已經持有新資料A的行鎖，導致無法上鎖而阻塞等待
事務（二）繼續插入一條新資料B時獲取 insert intention locks 阻塞等待，很顯然，事務（一）搶先佔有插入資料上下索引的 Gap Locks，死鎖產生，MySQL提示錯誤，並回滾事務（二）讓事務（一）提交

覆盤

接下來我將用一個小例子來複現這次死鎖現象

建一個簡單的表

CREATE TABLE `dead_lock` (
  `id` varchar(10) NOT NULL,
  `batch_no` varchar(10) DEFAULT NULL,
  `status` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO dead_lock (id,batch_no,status) VALUES
	 ('10','1','1'),
	 ('20','20','1'),
	 ('30','30',NULL),
	 ('32','32',NULL);

開始事務A進行插入資料，但先不提交

begin;
insert into dead_lock values ('34', '34', null);

開始新的事務B進行更新資料，此時你會發現該事務被阻塞

begin;
update dead_lock set status = '1' where batch_no = '20';

在事務A裡插入一條特殊的資料，id為11的資料，當然不在事務B的持有鎖的範圍插入資料是不會造成死鎖

insert into dead_lock values ('11', '11', null);

總結

在 MySQL 事務隔離級別 REPEATABLE READ 的情況下，對於 update，delete 等操作語句，查詢條件儘量使用索引，減少鎖的範圍，提高寫的並行量，避免不必要的死鎖發生影響業務正常執行

這次死鎖的說明就到這裡，希望大家能看得懂並有所收穫，不得不說有些知識我是略過了，大家可以自行檢視官方檔案補充瞭解，更多關於update where無索引MySQL死鎖的資料請關注it145.com其它相關文章！

update.where無索引導致MySQL死鎖問題解決

目錄

引言

死鎖的紀錄檔

分析紀錄檔

覆盤

總結

熱門文章