一個奇怪的死鎖：似乎一個刪除鎖定了多個二級索引，MySQL的一個錯誤？

最近我遇到了一個意外的死鎖，下面 3 個刪除（MySQL 5.7.12，隔離級別：可重複讀取）

第 1 節

開始;

從年齡 = 99 的水果中刪除；

第 2 節

開始;

從水果中刪除 name = ’lg4xdzvn2bth';

第 1 節

從水果中刪除 name = ’lg4xdzvn2bth';

會話 2 在這裡陷入僵局，多麼令人驚訝！有人知道為什麼嗎？這似乎與 Session 1 中的第一次刪除有關。

但是，AFAIK，**刪除.. where age = ..**應該鎖定i_age二級索引，這與索引無關i_name，對吧？

死鎖報告

2021-06-23 17:59:52 0x700003e01000
*** (1) TRANSACTION:
TRANSACTION 221465, ACTIVE 5 sec fetching rows
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 3, OS thread handle 123145367605248, query id 9710 localhost root updating
delete from fruit where name = 'lg4xdzvn2bth'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 232 page no 30 n bits 496 index i_name of table `jacky`.`fruit` trx id 221465 lock_mode X waiting
Record lock, heap no 429 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 12; hex 6c673478647a766e32627468; asc lg4xdzvn2bth;;
1: len 8; hex 80000000000038a3; asc       8 ;;

*** (2) TRANSACTION:
TRANSACTION 221464, ACTIVE 11 sec starting index read
mysql tables in use 1, locked 1
7 lock struct(s), heap size 1136, 25 row lock(s), undo log entries 11
MySQL thread id 2, OS thread handle 123145367326720, query id 9711 localhost root updating
delete from fruit where name = 'lg4xdzvn2bth'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 232 page no 30 n bits 496 index i_name of table `jacky`.`fruit` trx id 221464 lock_mode X locks rec but not gap
Record lock, heap no 429 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 12; hex 6c673478647a766e32627468; asc lg4xdzvn2bth;;
1: len 8; hex 80000000000038a3; asc       8 ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 232 page no 30 n bits 496 index i_name of table `jacky`.`fruit` trx id 221464 lock_mode X waiting
Record lock, heap no 428 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 12; hex 6c673478647a766e32627468; asc lg4xdzvn2bth;;
1: len 8; hex 80000000000038a2; asc       8 ;;

桌子

CREATE TABLE `fruit` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `name` varchar(32) NOT NULL,
 `age` int(1) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `i_age` (`age`),
 KEY `i_name` (`name`)
) ENGINE=InnoDB;

我們可以使用以下過程來生成數據

   delimiter ;;
   CREATE PROCEDURE `init_data`()
   BEGIN
       DECLARE i int DEFAULT 1;
       WHILE i < 500 DO
           INSERT INTO fruit (`name`, `age`) VALUES 
           (substring(MD5(RAND()),1,20), i / 10 + 1),
           (substring(MD5(RAND()),1,20), i / 10 + 1),
           (substring(MD5(RAND()),1,20), i / 10 + 1);
           SET i = i + 1;
       END WHILE;
       insert into fruit (name, age) values ('lg4xdzvn2bth', 56), ('lg4xdzvn2bth', 99);
   END ;;
   delimiter ;


call init_data();

---

更新

為了清楚起見，我在 MySQL 8.0 上重複了一遍，所以我們可以使用performance_schema.data_locks來查看鎖的詳細資訊。

第 1 節

從年齡 = 99 的水果中刪除；

+-----------+-------------+------------+-----------+---------------+-------------+------------------------+
| thread_id | object_name | index_name | lock_type | lock_mode     | lock_status | lock_data              |
+-----------+-------------+------------+-----------+---------------+-------------+------------------------+
|        47 | fruit       | NULL       | TABLE     | IX            | GRANTED     | NULL                   |
|        47 | fruit       | i_age      | RECORD    | X             | GRANTED     | supremum pseudo-record |
|        47 | fruit       | i_age      | RECORD    | X             | GRANTED     | 99, 16500              |
|        47 | fruit       | i_age      | RECORD    | X             | GRANTED     | 99, 16501              |
|        47 | fruit       | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 16500                  |
|        47 | fruit       | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 16501                  |

但是，我們沒有看到其他二級索引上的刪除持有鎖i_name。

第 2 節：

從水果中刪除 name = ’lg4xdzvn2bth';

+-----------+-------------+------------+-----------+---------------+-------------+------------------------+
| thread_id | object_name | index_name | lock_type | lock_mode     | lock_status | lock_data              |
+-----------+-------------+------------+-----------+---------------+-------------+------------------------+
|        80 | fruit       | NULL       | TABLE     | IX            | GRANTED     | NULL                   |
|        80 | fruit       | i_name     | RECORD    | X             | GRANTED     | 'lg4xdzvn2bth', 16499  |
|        80 | fruit       | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 16499                  |
|        80 | fruit       | i_name     | RECORD    | X             | WAITING     | 'lg4xdzvn2bth', 16500  |  <-----
|        80 | fruit       | i_name     | RECORD    | X,REC_NOT_GAP | GRANTED     | 'lg4xdzvn2bth', 16500  |  <-----

看最後2行，奇怪的是session 2持有一個記錄鎖（不是gap），卻在等待同一索引記錄的next key lock，好詭異！索引記錄**’lg4xdzvn2bth’, 16500**應該由會話 1 持有，對吧？這裡有錯誤？

ypercubeᵀᴹ：刪除行時，需要刪除所有二級索引中對該行的引用。兩個事務都想刪除 name = ’lg4xdzvn2bth’ 和 age=99 的行。

Tran 2 是第二個請求的，所以它正在等待。但是現在 Tran 1 也想刪除所有 name = ’lg4xdzvn2bth’ 的行，tran 2 已經鎖定了這些行。

所以他們都在等待對方的鎖。僵局。

引用自：https://dba.stackexchange.com/questions/294691