update.where無索引導致MySQL死鎖問題解決
更新時間：2022年11月02日 10:17:05 作者：Mating
這篇文章主要為大家介紹了update.where無索引導致MySQL死鎖問題解決，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進步，早日升職加薪
引言
隨著我被拉入一個新的群聊“生產(chǎn)環(huán)境死鎖問題排查解決”，打破了午后的悠然愜意，點開群聊秒送了一個648超級大禮包（業(yè)務不正常，死鎖異常日志輸出），領導怒斥并要求趕緊排除解決并總結(jié)經(jīng)驗，剛好我略懂略懂一點MySQL鎖知識，這不得秀一下自己的實力
死鎖的日志
既然死鎖已經(jīng)發(fā)生，也完全不要慌啊，按我說著做，一定能找到原因然后解決
觸發(fā)下面這條命令獲取到線索
SHOW ENGINE INNODB STATUS;
執(zhí)行后你會得到一段讓人看了有點迷迷的死鎖日志，具體我們該怎么分析死鎖，可以分成一下三步
- 查看發(fā)生死鎖的事務（一）信息（包括持有的鎖，等待的鎖）
- 查看發(fā)生死鎖的事務（二）信息（包括持有的鎖，等待的鎖）
- 查看回滾的是事務（一）還是事務（二），做好異常業(yè)務恢復的方案
- 根據(jù)mysql的加鎖機制分析發(fā)生的死鎖的原因
由于死鎖日志過于長，下面的日志只截取部分有用的記錄
------------------------
LATEST DETECTED DEADLOCK
------------------------
2022-10-14 15:51:34 0x1a00
發(fā)生死鎖的事務（一）
*** (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
事務（一）持有鎖的數(shù)據(jù)記錄信息 （supremum虛擬最大記錄）
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
事務（一）持有鎖的數(shù)據(jù)記錄信息 （哪一行數(shù)據(jù)被鎖了）
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
事務（一）等待鎖的數(shù)據(jù)記錄信息
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;
.....
.....
發(fā)生死鎖的事務（二）
*** (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
事務（二）持有鎖的數(shù)據(jù)記錄信息
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
事務（二）等待鎖的數(shù)據(jù)記錄信息
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;;
...
...
分析日志
從數(shù)據(jù)庫版本5.7、事務的隔離級別 REPEATABLE READ
官方文檔明確指出在 REPEATABLE READ 隔離級別下，默認查詢條件下是加 next-key locks (record locks + gap locks ) 或 gap locks，當查詢條件使用了唯一索引時，只會對當前查詢的唯一記錄進行加鎖，此時鎖為 record locks
官方文檔強勢占位
從死鎖日志信息可以得出
- 根據(jù)事務id大小可得出事務（二）32828381 比事務（一）32828384 先執(zhí)行
- 從日志中的 index PRIMARY 得出鎖是加在主鍵索引上
- 根據(jù)業(yè)務代碼，事務（二）將執(zhí)行 N 條同表插入 insert 語句，加上持有鎖信息得出，事務（二）先插入了一條新數(shù)據(jù)A，并得到新數(shù)據(jù)A的行鎖 Record Locks
- 事務（一）執(zhí)行 update 時直接阻塞，為什么呢，直接給出答案吧，因為這條 update 的查詢條件是沒有索引，導致需要所有的記錄都要加 Record Locks 和 Gap Locks，接著由于事務（二）已經(jīng)持有新數(shù)據(jù)A的行鎖，導致無法上鎖而阻塞等待
- 事務（二）繼續(xù)插入一條新數(shù)據(jù)B時獲取 insert intention locks 阻塞等待，很顯然，事務（一）搶先占有插入數(shù)據(jù)上下索引的 Gap Locks，死鎖產(chǎn)生，MySQL提示錯誤，并回滾事務（二）讓事務（一）提交
復盤
接下來我將用一個小例子來復現(xiàn)這次死鎖現(xiàn)象
- 建一個簡單的表
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進行插入數(shù)據(jù)，但先不提交
begin;
insert into dead_lock values ('34', '34', null);
- 開始新的事務B進行更新數(shù)據(jù)，此時你會發(fā)現(xiàn)該事務被阻塞
begin; update dead_lock set status = '1' where batch_no = '20';
- 在事務A里插入一條特殊的數(shù)據(jù)，id為11的數(shù)據(jù)，當然不在事務B的持有鎖的范圍插入數(shù)據(jù)是不會造成死鎖
insert into dead_lock values ('11', '11', null);
總結(jié)
在 MySQL 事務隔離級別 REPEATABLE READ 的情況下，對于 update，delete 等操作語句，查詢條件盡量使用索引，減少鎖的范圍，提高寫的并發(fā)量，避免不必要的死鎖發(fā)生影響業(yè)務正常運行
這次死鎖的說明就到這里，希望大家能看得懂并有所收獲，不得不說有些知識我是略過了，大家可以自行查看官方文檔補充了解，更多關于update where無索引MySQL死鎖的資料請關注腳本之家其它相關文章！
相關文章
mysql 默認是不允許外放訪問的,只允許 localhost 或 127.0.0.1 訪問,下面這篇文章主要給大家介紹了關于Mysql開啟外網(wǎng)訪問的相關資料,文中通過實例代碼介紹的非常詳細,需要的朋友可以參考下2022-05-05
這篇文章主要為大家詳細介紹了mysql 5.7.17 winx64安裝配置教程，具有一定的參考價值，感興趣的小伙伴們可以參考一下2017-03-03
偶然機會,發(fā)現(xiàn)一次查詢匹配數(shù)據(jù)的時候沒有區(qū)分大小寫,如,username = ‘WangWu’,username = 'wangwu’的記錄也被查出來,本文介紹解決這種情況的兩種方式,并通過代碼講解的非常詳細,需要的朋友可以參考下2025-01-01
從創(chuàng)建數(shù)據(jù)庫到存儲過程與用戶自定義函數(shù)的小感
從創(chuàng)建數(shù)據(jù)庫到存儲過程與用戶自定義函數(shù)的小感，深入的學習mysql2011-09-09
這篇文章主要介紹了使用sql語句insert之前判斷是否已存在記錄，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2021-12-12
MySQL通過ibd文件恢復數(shù)據(jù)的操作過程
通常,在InnoDB存儲引擎下,一個表由三部分組成：數(shù)據(jù)字典（.frm文件）、表空間（.ibd文件）和日志文件,其中,.frm文件存儲了表的定義,.ibd文件存儲了表的數(shù)據(jù)和索引,本文給大家記錄一次MySQL通過ibd文件恢復數(shù)據(jù)的操作過程,需要的朋友可以參考下2024-10-10
mysql 搜尋附近N公里內(nèi)數(shù)據(jù)的簡單實例
下面小編就為大家?guī)硪黄猰ysql 搜尋附近N公里內(nèi)數(shù)據(jù)的簡單實例。小編覺得挺不錯的，現(xiàn)在就分享給大家，也給大家做個參考。一起跟隨小編過來看看吧2017-04-04
MySQL數(shù)據(jù)備份之mysqldump的使用方法
mysqldump常用于MySQL數(shù)據(jù)庫邏輯備份,這篇文章主要給大家介紹了關于MySQL數(shù)據(jù)備份之mysqldump使用的相關資料,文中通過實例代碼介紹的非常詳細,需要的朋友可以參考下2021-11-11
Prometheus 插件mysql_exporter安裝過程
mysql_exporter是用來收集MysQL或者Mariadb數(shù)據(jù)庫相關指標的，mysql_exporter需要連接到數(shù)據(jù)庫并有相關權(quán)限，這篇文章主要介紹了Prometheus插件安裝(mysql_exporter),需要的朋友可以參考下2023-06-06
本文主要介紹了MySQL 8.0中手動管理Undo表空間,包括創(chuàng)建、配置、使用和維護這些表空間的具體步驟,文中通過示例代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友們下面隨著小編來一起學習學習吧2024-07-07