騰訊云：這個不可思議的死鎖你會解嗎？

我們都知道，數(shù)據(jù)庫系統(tǒng)中，不同線程并發(fā)訪問數(shù)據(jù)，為了保護(hù)數(shù)據(jù)，在執(zhí)行SQL語句時候需要對數(shù)據(jù)加鎖。而死鎖是一個經(jīng)常遇到問題，SQL語句加鎖和事物隔離級別，訪問的索引是不是唯一，訪問數(shù)據(jù)是否存在都有關(guān)系，往往死鎖分析非常復(fù)雜。這篇文章將介紹一個“簡單的死鎖”，這個死鎖產(chǎn)生的事物中SQL語句都只有一條，而且業(yè)務(wù)非常簡單就是刪除一條記錄。兩個事物同時執(zhí)行以下兩個SQL語句就有可能死鎖。

DELETE FROM dept_manager WHERE num = 0;

DELETE FROM dept_manager WHERE dept_no = 'd001';

一、死鎖模擬

死鎖模擬

首先介紹下表結(jié)構(gòu)，這個表除了主鍵索引 PRIMARY，還有一個唯一索引 num 和一個非唯一索引 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í)行兩個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 &

這兩個事物非常都是刪除一行相同的數(shù)據(jù) (1001,'d001',0,'1991-10-01'）只不過一個根據(jù)索引 num ，一個根據(jù)索引 dept_no 。

二、原因分析

1. 數(shù)據(jù)是怎么找到的？

要說清楚死鎖產(chǎn)生原因，就要先理清楚這條SQL是怎么執(zhí)行的，會在那些地方加鎖。在此之前先說說數(shù)據(jù)庫是怎么找到我們要刪除的這行數(shù)據(jù)的。下面兩幅圖展示根據(jù)年齡為30來查記錄的示意圖。首先根據(jù) name 為 seven, 在 name 這個輔助索引查找，但是只能拿到主鍵的 id。隨后再根據(jù)主鍵id 去主鍵查找，這個過程稱為回表。訪問數(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; 為例，只有一個條件 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)。這個表中主鍵索引 PRIMARY，唯一索引 num，非唯一索引 dept_no 的對應(yīng)位置都會加上鎖。同理第二個SQL語句執(zhí)行時候，加鎖位置也是一樣的。（可重復(fù)度隔離級別上，非唯一索引還要加上間隙鎖）。

既然加鎖上一樣的，那應(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持有這個鎖的，事物1持有的鎖沒有顯示，應(yīng)該是主鍵上的鎖，這是符合預(yù)期的。

------------------------

LATEST DETECTED DEADLOCK

------------------------

2021-04-27 16:41:19 0x70000a6b1000

*** (1) TRANSACTION:

TRANSACTION 1681994, ACTIVE 0 sec updating or deleting

mysql tables in use 1, locked 1

LOCK WAIT 4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1

MySQL thread id 30, OS thread handle 123145456488448, query id 343687 localhost 127.0.0.1 root updating

DELETE 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 waiting

Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0

 0: len 4; hex 64303031; asc d001;;

 1: len 4; hex 800003e9; asc     ;;

*** (2) TRANSACTION:

TRANSACTION 1681554, ACTIVE 0 sec starting index read

mysql tables in use 1, locked 1

3 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 updating

DELETE 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 X

Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0

 0: 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 waiting

Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 32

 0: 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語句很簡單，但由于表中有多個索引，對索引的訪問順序不同，造成死鎖風(fēng)險。為了避免數(shù)據(jù)庫中發(fā)生死鎖，建議：

1. 盡量開啟死鎖檢測；

2. 盡量使用小事務(wù)，在業(yè)務(wù)允許范圍內(nèi)，將隔離級別改成讀已提交，可以減少不些不必要的鎖；

3. 避免全表掃描；

4. 避免較多索引；

5. 不同事務(wù)對表和行操作的順序盡量一致。