這個(gè)不可思議的死鎖你會(huì)解嗎？
| 作者 王起帆，騰訊CSIG數(shù)據(jù)庫產(chǎn)品中心后臺(tái)開發(fā)工程師，目前主要參與DBbrain開發(fā)工作，熱愛技術(shù)，歡迎留言進(jìn)行交流。
DELETE FROM dept_manager WHERE num = 0;DELETE FROM dept_manager WHERE dept_no = 'd001';
一、死鎖模擬
死鎖模擬
首先介紹下表結(jié)構(gòu)，這個(gè)表除了主鍵索引 PRIMARY，還有一個(gè)唯一索引 num 和一個(gè)非唯一索引 dept_no ，建表語句如下：
CREATE TABLE `dept_manager` (`emp_no` int(11) NOT NULL,`dept_no` char(4) NOT NULL,`num` int(11) NOT NULL,`to_date` date NOT NULL,PRIMARY KEY (`emp_no`),unique index(`num`),KEY `dept_no` (`dept_no`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;
然后再準(zhǔn)備下數(shù)據(jù)：
INSERT INTO `dept_manager` VALUES (1001,'d001',0,'1991-10-01'),(1002,'d005',1,'9999-01-01'),(1005,'d002',3,'1989-12-17'),(1007,'d002',4,'9999-01-01'),(1008,'d004',7,'1988-09-09'),(1009,'d004',8,'1992-08-02'),(1010,'d005',9,'1996-08-30');
使用執(zhí)行兩個(gè)sql很難，使用 mysqlslap 來高并發(fā)碰碰運(yùn)氣：
mysqlslap --create-schema dldb -q "begin;DELETE FROM dept_manager WHERE num = 0; rollback;" --number-of-queries=100000 -uroot -p123456 &mysqlslap --create-schema dldb -q "begin;DELETE FROM dept_manager WHERE dept_no = 'd001'; rollback;" --number-of-queries=100000 -uroot -p123456 &
這兩個(gè)事物非常都是刪除一行相同的數(shù)據(jù) (1001,'d001',0,'1991-10-01'）只不過一個(gè)根據(jù)索引 num ，一個(gè)根據(jù)索引 dept_no 。
二、原因分析
1. 數(shù)據(jù)是怎么找到的？
要說清楚死鎖產(chǎn)生原因，就要先理清楚這條SQL是怎么執(zhí)行的，會(huì)在那些地方加鎖。在此之前先說說數(shù)據(jù)庫是怎么找到我們要?jiǎng)h除的這行數(shù)據(jù)的。下面兩幅圖展示根據(jù)年齡為30來查記錄的示意圖。首先根據(jù) name 為 seven, 在 name 這個(gè)輔助索引查找，但是只能拿到主鍵的 id。隨后再根據(jù)主鍵id 去主鍵查找，這個(gè)過程稱為回表。訪問數(shù)據(jù)是要通過索引的，而且數(shù)據(jù)就在主鍵索引上面，所以加鎖就是加在索引上面的。
2. Delete 是怎么執(zhí)行的
Delete 刪除數(shù)據(jù)其實(shí)并不是把數(shù)據(jù)刪除了，只是把數(shù)據(jù)標(biāo)記一下，表示這里可以復(fù)用的，如果下次這里有數(shù)據(jù)要插入就可以直接復(fù)用原來空間里。所以Delete 和 Update 操作比較類似。Delete 和 Update 是根據(jù)條件找到第一條數(shù)據(jù)，進(jìn)行修改，然后找到第二條數(shù)據(jù)，以此類推直到再也查不到符合條件的數(shù)據(jù)。
3. 加鎖分析
我們以 DELETE FROM dept_manager WHERE num = 0; 為例，只有一個(gè)條件 num = 0, 因該是根據(jù) num = 0 在 num 索引中找到對應(yīng)的主鍵id, 隨后根據(jù)主鍵 id,找到對應(yīng)記錄，標(biāo)記成可復(fù)用狀態(tài)。除了刪除數(shù)據(jù)行記錄，對應(yīng)的索引也需要維護(hù)下，其他索引對應(yīng)位置也需要標(biāo)記成刪除狀態(tài)。這個(gè)表中主鍵索引 PRIMARY，唯一索引 num，非唯一索引 dept_no 的對應(yīng)位置都會(huì)加上鎖。同理第二個(gè)SQL語句執(zhí)行時(shí)候，加鎖位置也是一樣的。（可重復(fù)度隔離級(jí)別上，非唯一索引還要加上間隙鎖）。
既然加鎖上一樣的，那應(yīng)該是在不同索引加鎖順序是不一樣的。推測下對于 WHERE num = 0 應(yīng)該先在 num 上加鎖，隨后在主鍵加鎖，最后在 dept_no上，num ->PRIMARY-> dept_no。WHERE dept_no = 'd001';加鎖順序應(yīng)該是dept_no -> PRIMARY -> num。盡管這條SQL數(shù)據(jù)很簡單，但是由于數(shù)據(jù)中索引比較多，加鎖順序也不一樣，導(dǎo)致了死鎖。
三、場景驗(yàn)證
可以用 show engine innodb status ，來查看最近一次死鎖日志。事物1等待索引dept上的鎖 0: len 4; hex 64303031; asc d001;; 這里“64303031” 16進(jìn)制轉(zhuǎn)為字符為“d001” 與 WHERE dept_no = 'd001' 相對應(yīng)。事物2持有這個(gè)鎖的，事物1持有的鎖沒有顯示，應(yīng)該是主鍵上的鎖，這是符合預(yù)期的。
------------------------LATEST DETECTED DEADLOCK------------------------2021-04-27 16:41:19 0x70000a6b1000*** (1) TRANSACTION:TRANSACTION 1681994, ACTIVE 0 sec updating or deletingmysql tables in use 1, locked 1LOCK WAIT 4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1MySQL thread id 30, OS thread handle 123145456488448, query id 343687 localhost 127.0.0.1 root updatingDELETE FROM dept_manager WHERE num = 0*** (1) WAITING FOR THIS LOCK TO BE GRANTED: #請求 dept_no上鎖RECORD LOCKS space id 367 page no 5 n bits 80 index dept_no of table `employees`.`dept_manager` trx id 1681994 lock_mode X locks rec but not gap waitingRecord lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 00: len 4; hex 64303031; asc d001;;1: len 4; hex 800003e9; asc ;;*** (2) TRANSACTION:TRANSACTION 1681554, ACTIVE 0 sec starting index readmysql tables in use 1, locked 13 lock struct(s), heap size 1136, 2 row lock(s)MySQL thread id 106, OS thread handle 123145477099520, query id 341105 localhost 127.0.0.1 root updatingDELETE FROM dept_manager WHERE dept_no = 'd001'*** (2) HOLDS THE LOCK(S): # 持有 dept_no 上鎖RECORD LOCKS space id 367 page no 5 n bits 80 index dept_no of table `employees`.`dept_manager` trx id 1681554 lock_mode XRecord lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 00: len 4; hex 64303031; asc d001;;1: len 4; hex 800003e9; asc ;;*** (2) WAITING FOR THIS LOCK TO BE GRANTED: # 請求主鍵RECORD LOCKS space id 367 page no 3 n bits 80 index PRIMARY of table `employees`.`dept_manager` trx id 1681554 lock_mode X locks rec but not gap waitingRecord lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 320: len 4; hex 800003e9; asc ;;1: len 6; hex 00000019aa4a; asc J;;2: len 7; hex 2c000001b80ede; asc , ;;3: len 4; hex 64303031; asc d001;;4: len 4; hex 80000000; asc ;;5: len 3; hex 8f8f41; asc A;;*** WE ROLL BACK TRANSACTION (2)
四、總結(jié)
本文介紹的樣例中，盡管SQL語句很簡單，但由于表中有多個(gè)索引，對索引的訪問順序不同，造成死鎖風(fēng)險(xiǎn)。為了避免數(shù)據(jù)庫中發(fā)生死鎖，建議：
1. 盡量開啟死鎖檢測；
2. 盡量使用小事務(wù)，在業(yè)務(wù)允許范圍內(nèi)，將隔離級(jí)別改成讀已提交，可以減少不些不必要的鎖；
3. 避免全表掃描；
4. 避免較多索引；
5. 不同事務(wù)對表和行操作的順序盡量一致。
- End -
更多精彩
絕了！這個(gè)MySQL故障定位方法太好用了
數(shù)據(jù)庫診斷不了的，騰訊大神來“診斷”