MySQL 核心模塊—揭秘死鎖案例分析插入意向鎖
1. 準(zhǔn)備工作
創(chuàng)建測(cè)試表：
CREATE TABLE `t_deadlock_1` (
`id` int NOT NULL AUTO_INCREMENT,
`i1` int DEFAULT NULL,
`i2` int DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_i1` (`i1`)
) ENGINE = InnoDB;插入測(cè)試數(shù)據(jù)：
INSERT INTO `t_deadlock_1` (`id`, `i1`, `i2`)
VALUE (22, 2, 3), (23, 5, 4), (24, 6, 7);把事務(wù)隔離級(jí)別設(shè)置為 REPEATABLE-READ（如已設(shè)置，忽略此步驟）：
SET transaction_isolation = 'REPEATABLE-READ';
-- 確認(rèn)設(shè)置成功
SHOW VARIABLES like 'transaction_isolation';
+-----------------------+-----------------+
| Variable_name | Value |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+2. 加鎖情況
創(chuàng)建 2 個(gè) MySQL 連接，開啟 2 個(gè)事務(wù)，執(zhí)行以下 SQL：
-- session 1（事務(wù) 1）
BEGIN; DELETE FROM t_deadlock_1 WHERE `i1` = 5;
-- session 2（事務(wù) 2）
BEGIN; DELETE FROM t_deadlock_1 WHERE `i1` = 5;在 session 1 中執(zhí)行以下 select 語(yǔ)句查看加鎖情況：
select
engine_transaction_id, object_name, index_name,
lock_type, lock_mode, lock_status, lock_data
from performance_schema.data_locks
where object_name = 't_deadlock_1'
and lock_type = 'RECORD'\G
***************************[ 1. row ]***************************
engine_transaction_id | 250490
object_name | t_deadlock_1
index_name | idx_i1
lock_type | RECORD
lock_mode | X
lock_status | WAITING
lock_data | 5, 23
***************************[ 2. row ]***************************
engine_transaction_id | 250489
object_name | t_deadlock_1
index_name | idx_i1
lock_type | RECORD
lock_mode | X
lock_status | GRANTED
lock_data | 5, 23
***************************[ 3. row ]***************************
engine_transaction_id | 250489
object_name | t_deadlock_1
index_name | PRIMARY
lock_type | RECORD
lock_mode | X,REC_NOT_GAP
lock_status | GRANTED
lock_data | 23
***************************[ 4. row ]***************************
engine_transaction_id | 250489
object_name | t_deadlock_1
index_name | idx_i1
lock_type | RECORD
lock_mode | X,GAP
lock_status | GRANTED
lock_data | 6, 24加鎖情況第 2 ~ 4 條，是事務(wù) 1 的加鎖情況。
事務(wù) 1 執(zhí)行 delete 語(yǔ)句過程中，會(huì)先掃描需要?jiǎng)h除的記錄，并對(duì)掃描到的記錄加鎖。
掃描過程使用了二級(jí)索引 idx_i1，先定位到這個(gè)索引中 <i1 = 5, id = 23> 的記錄，加排他 Next-Key 鎖，對(duì)應(yīng)加鎖情況第 2 條（2. row）。
回表查詢主鍵索引中 <id = 23> 的記錄，加排他普通記錄鎖，對(duì)應(yīng)加鎖情況第 3 條（3. row）。
掃描到匹配 where 條件的第 1 條記錄之后，接著掃描下一條記錄，也就是二級(jí)索引 idx_i1 中 <i1 = 6, id = 24> 的記錄，加排他間隙鎖，對(duì)應(yīng)加鎖情況第 4 條（4. row）。
因?yàn)檫@條記錄不匹配 where 條件，不需要回表查詢對(duì)應(yīng)的主鍵索引記錄，所以沒有對(duì)主鍵索引中 <id = 24> 的記錄加鎖。
按照 <i1 = 5, id = 23> 的記錄加鎖情況，<i1 = 6, id = 24> 的記錄也應(yīng)該加排他 Next-Key 鎖，但實(shí)際上只加了排他間隙鎖。
這是因?yàn)?InnoDB 對(duì)命中索引的等值查詢條件做了特殊處理。
可重復(fù)讀隔離級(jí)別默認(rèn)會(huì)對(duì)掃描到的記錄加排他 Next-Key 鎖。如果 InnoDB 發(fā)現(xiàn)記錄不匹配命中索引的等值查詢條件，會(huì)改為對(duì)這條記錄加排他間隙鎖，避免鎖定不匹配的記錄本身，以縮小加鎖范圍。
加鎖情況第 1 條（1. row），是事務(wù) 2 的加鎖情況。
事務(wù) 2 執(zhí)行 delete 語(yǔ)句過程中，也會(huì)先掃描需要?jiǎng)h除的記錄，并對(duì)掃描到的記錄加鎖。
掃描過程同樣使用了二級(jí)索引 idx_i1，先定位到這個(gè)索引中 <i1 = 5, id = 23> 的記錄，加排他 Next-Key 鎖。
但是，因?yàn)槭聞?wù) 1 先對(duì)這條記錄加了排他 Next-Key 鎖，事務(wù) 2 的加鎖操作被阻塞，進(jìn)入鎖等待狀態(tài)。
介紹完事務(wù) 1 和事務(wù) 2 的加鎖情況，我們?cè)僭?session 1 中執(zhí)行以下 insert 語(yǔ)句，插入一條記錄：
INSERT INTO t_deadlock_1 (`id`, `i1`, `i2`) VALUES (25, 2, 10);結(jié)果就出現(xiàn)了死鎖，事務(wù) 2 被選擇成為死鎖受害事務(wù)，回滾了：
(1213, 'Deadlock found when trying to get lock; try restarting transaction')3. 死鎖分析
為了找到死鎖原因，我們需要借助死鎖日志，可以在 session 1 或者 session 2 中執(zhí)行以下 show 語(yǔ)句，查看最新的死鎖日志：
SHOW ENGINE InnoDB STATUS\G
------------------------
LATEST DETECTED DEADLOCK
------------------------
2024-09-07 07:48:49 0x7000087c0000
*** TRANSACTION:
-- 事務(wù) 2
TRANSACTION 250490, ACTIVE 19 sec starting index read
...
DELETE FROM t_deadlock_1 WHERE `i1` = 5
*** HOLDS THE LOCK(S):
RECORD LOCKS space id 232 page no 5 n bits 72 \
index idx_i1 of table `test`.`t_deadlock_1` trx id 250490 \
lock_mode X waiting
Record lock, heap no 3 PHYSICAL RECORD: \
n_fields 2; compact format; info bits 32
0: len 4; hex 80000005; asc ;;
1: len 4; hex 80000017; asc ;;
*** WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 232 page no 5 n bits 72 \
index idx_i1 of table `test`.`t_deadlock_1` trx id 250490 \
lock_mode X waiting
Record lock, heap no 3 PHYSICAL RECORD: \
n_fields 2; compact format; info bits 32
0: len 4; hex 80000005; asc ;;
1: len 4; hex 80000017; asc ;;
*** TRANSACTION:
-- 事務(wù) 1
TRANSACTION 250489, ACTIVE 26 sec inserting
...
INSERT INTO t_deadlock_1 (`id`, `i1`, `i2`) VALUES (25, 2, 10)
*** HOLDS THE LOCK(S):
RECORD LOCKS space id 232 page no 5 n bits 72 \
index idx_i1 of table `test`.`t_deadlock_1` trx id 250489 \
lock_mode X
Record lock, heap no 3 PHYSICAL RECORD: \
n_fields 2; compact format; info bits 32
0: len 4; hex 80000005; asc ;;
1: len 4; hex 80000017; asc ;;
*** WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 232 page no 5 n bits 72 \
index idx_i1 of table `test`.`t_deadlock_1` trx id 250489 \
lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: \
n_fields 2; compact format; info bits 32
0: len 4; hex 80000005; asc ;;
1: len 4; hex 80000017; asc ;;以上是從 SHOW ENGINE InnoDB STATUS 結(jié)果中摘出來(lái)的最新的死鎖日志。
為了方便手機(jī)上閱讀，我對(duì)格式做了一些調(diào)整，內(nèi)容也有一點(diǎn)小小的修改，去掉了事務(wù)前面的編號(hào)。
從死鎖日志可以看到，事務(wù) 1（250489）和事務(wù) 2（250490）加鎖發(fā)生死鎖，都是因?yàn)槎?jí)索引 idx_i1 中的一條記錄：
/* i1 字段 */ 0: len 4; hex 80000005; asc ;;
/* id 字段 */ 1: len 4; hex 80000017; asc ;;在 《30. 死鎖日志詳解》這篇文章中，我們介紹過把死鎖日志中整數(shù)類型字段值轉(zhuǎn)換為整數(shù)的方法。
我們用這個(gè)方法，把上面死鎖日志中這條記錄的兩個(gè)字段值轉(zhuǎn)換為整數(shù)：
## i1 字段，輸出：5
echo $((0x80000005 ^ (1 << (4 * 8 - 1))))
## id 字段，輸出：23
echo $((0x80000017 ^ (1 << (4 * 8 - 1))))從以上輸出可以看到，事務(wù) 1（250489）和事務(wù) 2（250490）加鎖發(fā)生死鎖，都是因?yàn)槎?jí)索引 idx_i1 中 <i1 = 5, id = 23> 的記錄。
*** TRANSACTION:
-- 事務(wù) 1
TRANSACTION 250489, ACTIVE 26 sec inserting
...
*** HOLDS THE LOCK(S):
RECORD LOCKS space id 232 page no 5 n bits 72 \
index idx_i1 of table `test`.`t_deadlock_1` trx id 250489 \
lock_mode X
...
*** WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 232 page no 5 n bits 72 \
index idx_i1 of table `test`.`t_deadlock_1` trx id 250489 \
lock_mode X locks gap before rec insert intention waiting
...上面是從死鎖日志中摘出來(lái)的一小段，從這段日志可以看到，事務(wù) 1（250489）持有 <i1 = 5, id = 23> 的記錄的排他 Next-Key 鎖，等待獲得這條記錄的插入意向鎖。
*** TRANSACTION:
-- 事務(wù) 2
TRANSACTION 250490, ACTIVE 19 sec starting index read
...
DELETE FROM t_deadlock_1 WHERE `i1` = 5
*** HOLDS THE LOCK(S):
RECORD LOCKS space id 232 page no 5 n bits 72 \
index idx_i1 of table `test`.`t_deadlock_1` trx id 250490 \
lock_mode X waiting
...
*** WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 232 page no 5 n bits 72 \
index idx_i1 of table `test`.`t_deadlock_1` trx id 250490 \
lock_mode X waiting
...上面也是從死鎖日志中摘出來(lái)的一小段，從這段日志可以看到，事務(wù) 2（250490）的 HOLDS THE LOCK(S) 和 WAITING FOR THIS LOCK TO BE GRANTED 的記錄都處于 waiting 狀態(tài)。
這是因?yàn)槭聞?wù) 2（250490）在等待獲得事務(wù) 1（250489）持有的 <i1 = 5, id = 23> 的記錄的排他 Next-Key 鎖，又阻塞了事務(wù) 1（250489）對(duì) <i1 = 5, id = 23> 的記錄加插入意向鎖。
既然事務(wù) 1（250489）已經(jīng)持有 <i1 = 5, id = 23> 的記錄的排他 Next-Key 鎖，也就是既鎖定了這條記錄，又鎖定了它前面的間隙。
理論上來(lái)說，事務(wù) 1（250489）再對(duì)這條記錄加插入意向鎖，可以直接獲得鎖。
為什么會(huì)被事務(wù) 2（250490）阻塞呢？
如果事務(wù) 1（250489）因?yàn)槌钟羞@條記錄的排他 Next-Key 鎖，就可以直接獲得這條記錄的插入意向鎖。
獲得插入意向鎖之后，插入 <i1 = 2, id = 25> 的記錄到 <i1 = 5, id = 23> 的記錄前面。
新插入的記錄，會(huì)導(dǎo)致事務(wù) 1 和事務(wù) 2 原來(lái)對(duì) <i1 = 5, id = 23> 的記錄加的鎖都需要拆分。
已經(jīng)獲得的鎖，拆分是沒有問題的。
事務(wù) 2（250490）在等待獲得 <i1 = 5, id = 23> 的記錄的排他 Next-Key 鎖，也會(huì)拆分，得到兩個(gè)處于等待狀態(tài)的鎖。
然而，InnoDB 卻不允許一個(gè)事務(wù)同時(shí)有兩個(gè)處于等待狀態(tài)的鎖。
基于這個(gè)規(guī)則，雖然事務(wù) 1（250489）已經(jīng)持有 <i1 = 5, id = 23> 的記錄的排他 Next-Key 鎖，但是因?yàn)槭聞?wù) 2（250490）在等待獲得這條記錄的排他 Next-Key 鎖，事務(wù) 1（250489）想要對(duì)這條記錄加插入意向鎖，也需要等待。
事務(wù) 1（250489）和事務(wù) 2（250490）相互等待，就形成了死鎖，過程如下：
- 事務(wù) 1 持有鎖。
- 事務(wù) 2 等待獲得事務(wù) 1 持有的鎖。
- 事務(wù) 1 等待事務(wù) 2 獲得并釋放鎖之后，才能獲得插入意向鎖。
4. 總結(jié)
如果事務(wù) 1 已經(jīng)對(duì)某條記錄加了排他 Next-Key 鎖：
- 沒有其它事務(wù)在等待獲得這條記錄的鎖，事務(wù) 1 想要往這條記錄前面的間隙插入記錄，不需要等待獲得插入意向鎖，可以直接插入記錄。
- 其它事務(wù)在等待獲得這條記錄的鎖，事務(wù) 1 想要往這條記錄前面的間隙插入記錄，需要等待其它事務(wù)獲得并釋放鎖之后，事務(wù) 1 才能獲得插入意向鎖，然后才能往這個(gè)間隙插入記錄。
