我們都知道，數(shù)據庫系統(tǒng)中，不同線程并發(fā)訪問數(shù)據，為了保護數(shù)據，在執(zhí)行SQL語句時候需要對數(shù)據加鎖。而死鎖是一個經常遇到問題，SQL語句加鎖和事物隔離級別，訪問的索引是不是唯一，訪問數(shù)據是否存在都有關系，往往死鎖分析非常復雜。這篇文章將介紹一個“簡單的死鎖”，這個死鎖產生的事物中SQL語句都只有一條，而且業(yè)務非常簡單就是刪除一條記錄。兩個事物同時執(zhí)行以下兩個SQL語句就有可能死鎖。
DELETE FROM dept_manager WHERE num = 0;
DELETE FROM dept_manager WHERE dept_no = 'd001';
一、死鎖模擬
死鎖模擬
首先介紹下表結構，這個表除了主鍵索引 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;
然后再準備下數(shù)據：
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ā)碰碰運氣：
# 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ù)據 (1001,'d001',0,'1991-10-01'）只不過一個根據索引 num ，一個根據索引 dept_no 。
二、原因分析
1. 數(shù)據是怎么找到的？
要說清楚死鎖產生原因，就要先理清楚這條SQL是怎么執(zhí)行的，會在那些地方加鎖。在此之前先說說數(shù)據庫是怎么找到我們要刪除的這行數(shù)據的。下面兩幅圖展示根據年齡為30來查記錄的示意圖。首先根據 name 為 seven, 在 name 這個輔助索引查找，但是只能拿到主鍵的 id。隨后再根據主鍵id 去主鍵查找，這個過程稱為回表。訪問數(shù)據是要通過索引的，而且數(shù)據就在主鍵索引上面，所以加鎖就是加在索引上面的。
2. Delete 是怎么執(zhí)行的
Delete 刪除數(shù)據其實并不是把數(shù)據刪除了，只是把數(shù)據標記一下，表示這里可以復用的，如果下次這里有數(shù)據要插入就可以直接復用原來空間里。所以Delete 和 Update 操作比較類似。Delete 和 Update 是根據條件找到第一條數(shù)據，進行修改，然后找到第二條數(shù)據，以此類推直到再也查不到符合條件的數(shù)據。
3. 加鎖分析
我們以 DELETE FROM dept_manager WHERE num = 0; 為例，只有一個條件 num = 0, 因該是根據 num = 0 在 num 索引中找到對應的主鍵id, 隨后根據主鍵 id,找到對應記錄，標記成可復用狀態(tài)。除了刪除數(shù)據行記錄，對應的索引也需要維護下，其他索引對應位置也需要標記成刪除狀態(tài)。這個表中主鍵索引 PRIMARY，唯一索引 num，非唯一索引 dept_no 的對應位置都會加上鎖。同理第二個SQL語句執(zhí)行時候，加鎖位置也是一樣的。（可重復度隔離級別上，非唯一索引還要加上間隙鎖）。
既然加鎖上一樣的，那應該是在不同索引加鎖順序是不一樣的。推測下對于 WHERE num = 0 應該先在 num 上加鎖，隨后在主鍵加鎖，最后在 dept_no上，num ->PRIMARY-> dept_no。WHERE dept_no = 'd001';加鎖順序應該是dept_no -> PRIMARY -> num。盡管這條SQL數(shù)據很簡單，但是由于數(shù)據中索引比較多，加鎖順序也不一樣，導致了死鎖。
三、場景驗證
可以用 show engine innodb status ，來查看最近一次死鎖日志。事物1等待索引dept上的鎖 0: len 4; hex 64303031; asc d001;; 這里“64303031” 16進制轉為字符為“d001” 與 WHERE dept_no = 'd001' 相對應。事物2持有這個鎖的，事物1持有的鎖沒有顯示，應該是主鍵上的鎖，這是符合預期的。
------------------------
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)
四、總結
本文介紹的樣例中，盡管SQL語句很簡單，但由于表中有多個索引，對索引的訪問順序不同，造成死鎖風險。為了避免數(shù)據庫中發(fā)生死鎖，建議：
1. 盡量開啟死鎖檢測；
2. 盡量使用小事務，在業(yè)務允許范圍內，將隔離級別改成讀已提交，可以減少不些不必要的鎖；
3. 避免全表掃描；
4. 避免較多索引；
5. 不同事務對表和行操作的順序盡量一致。
騰訊云
專欄
閩公網安備 35010202001226號
