MySQL:删除同一行时发生死锁

Question

最近在删除记录时遇到死锁(注意隔离级别是 REPEATABLE READ ，MySQL 5.7)
这是重现步骤
1 创建一个新表

CREATE TABLE `t` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `p_name` (`name`)
) ENGINE=InnoDB CHARSET=utf8;

2 准备3条记录

insert into t (name) value ('A'), ('C'), ('D');

3

+====================================+============================================================+
|             Session A              |                         Session B                          |
+====================================+============================================================+
| begin;                             |                                                            |
+------------------------------------+------------------------------------------------------------+
|                                    | begin;                                                     |
+------------------------------------+------------------------------------------------------------+
| delete from t where name = 'C';    |                                                            |
+------------------------------------+------------------------------------------------------------+
|                                    | delete from t where name = 'C';  --Blocked!                |
+------------------------------------+------------------------------------------------------------+
| insert into t (name) values ('B'); |                                                            |
+------------------------------------+------------------------------------------------------------+
|                                    | ERROR 1213 (40001): Deadlock found when trying to get lock |
+------------------------------------+------------------------------------------------------------+

的结果显示引擎 innodb 状态 如下图(LATEST DETECTED DEADLOCK 部分)

LATEST DETECTED DEADLOCK
------------------------
*** (1) TRANSACTION:
TRANSACTION 3631, ACTIVE 21 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 13, OS thread handle 123145439432704, query id 306 localhost root updating
delete from t where name = 'C'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 69 page no 4 n bits 72 index p_name of table `jacky`.`t` trx id 3631 lock_mode X waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 1; hex 43; asc C;;
 1: len 8; hex 8000000000000018; asc         ;;

*** (2) TRANSACTION:
TRANSACTION 3630, ACTIVE 29 sec inserting
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 2
MySQL thread id 14, OS thread handle 123145439711232, query id 307 localhost root update
insert into t (name) values ('B')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 69 page no 4 n bits 72 index p_name of table `jacky`.`t` trx id 3630 lock_mode X
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 1; hex 43; asc C;;
 1: len 8; hex 8000000000000018; asc         ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 69 page no 4 n bits 72 index p_name of table `jacky`.`t` trx id 3630 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 1; hex 43; asc C;;
 1: len 8; hex 8000000000000018; asc         ;;

如 Innodb 状态所示， session B 正在等待下一个键锁 C , session A 持有记录锁 C 并等待间隙锁定 C ;

众所周知

DELETE FROM ... WHERE ... sets an exclusive next-key lock on every record the search encounters

A next-key lock is a combination of a record lock on the index record and a gap lock on the gap before the index record.

第一季度 : 我猜如果 session B 首先获得间隙锁(next-key 的一部分)，然后等待记录锁。从而，后者在 session A 中的插入被 session B 阻塞(由于间隙锁)，并最终导致死锁。对？
Q2 : 如 C 从索引中清除， session B 持有的间隙锁是否应该是 ('A', 'D')？如果是这样，为什么 session A 正在等待范围 (, 'C') 上的插入意图锁？
Q3 : 为什么 session B 有 1 row lock(s) ， session A 有 4 row lock(s) ?

Q4 : 更改索引时 p_name对于唯一索引，由于间隙锁，我们仍然会陷入死锁，这很奇怪。它的行为不同于官方 doc其中指出只需要记录锁定。

DELETE FROM ... WHERE ... sets an exclusive next-key lock on every record the search encounters. However, only an index record lock is required for statements that lock rows using a unique index to search for a unique row.

但是，使用主键时是可以的 id执行删除(步骤如下所示)。这是 MySQL 中的错误吗？
1 准备数据

delete from t;
insert into t (id, name) value (1, 'A'), (3, 'C'), (5, 'D');

2

+-------------------------------------------+--------------------------------------+
|                 Session A                 |              Session B               |
+-------------------------------------------+--------------------------------------+
| begin;                                    |                                      |
|                                           | begin;                               |
| delete from t where id = 3;               |                                      |
|                                           | delete from t where id = 3; Blocked! |
| insert into t (id, name) values (2, 'B'); |                                      |
|                                           |                                      |
| commit;                                   |                                      |
+-------------------------------------------+--------------------------------------+

Answer 1

从交易 3631 的“WAITING FOR THIS LOCK TO BE GRANTED”部分，我们可以看到:

RECORD LOCKS space id 69 page no 4 n bits 72 index p_name of table `jacky`.`t` trx id 3631 lock_mode X waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 32

3631 正在等待记录锁定。对应的索引内容为{"name":"C", "id": 24}。

索引名称是表 t 中的 p_name。

锁的模式是“lock_mode X”

从交易 3630 的“WAITING FOR THIS LOCK TO BE GRANTED”部分，我们可以看到:

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 69 page no 4 n bits 72 index p_name of table `jacky`.`t` trx id 3630 lock_mode X
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 1; hex 43; asc C;;
 1: len 8; hex 8000000000000018; asc         ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 69 page no 4 n bits 72 index p_name of table `jacky`.`t` trx id 3630 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 1; hex 43; asc C;;
 1: len 8; hex 8000000000000018; asc         ;;

3630 正在等待记录锁定。对应的索引内容为{"name":"C", "id": 24}。等待锁的模式是“lock_mode X locks gap”

3630 持有记录锁。对应的索引内容为{"name":"C", "id": 24}。持有锁的模式是“lock_mode X locks”

索引名称是表 t 中的 p_name。

此死锁是由执行“插入 t(名称)值('B')”引起的

根据您的重现步骤， session A 将发送 delete from t where name = 'C';首先，这将锁定:

('A', 'C'] and ('C', 'D'): next-key lock 'C' and gap lock before 'D';

DELETE FROM ... WHERE ... sets an exclusive next-key lock on everyrecord the search encounters. However, only an index record lock isrequired for statements that lock rows using a unique index to searchfor a unique row.

为 'C' 对应的主索引 id 添加一个记录锁。这里 id 值应该是“26”。

然后 session B 将开始和 delete from t where name = 'C';将再次被执行。然而。对于 session B，因为 session A还没有提交，'C'已经被 session A锁定。但是，如果执行一个删除sql， session B会按照以下顺序尝试加锁:

'C'之前的gap lock:成功，因为innodb可以在同一个位置添加多gap lock。

记录锁“C”:已屏蔽 ，因为 session A 持有该锁。 session B 必须等待 session A 释放它。

'D' 之前的间隙锁:

最后， session A 发送 insert into t (name) values ('B'); .表用 t ，有2个索引，分别是 id和 name . id是一个自增主整数键，对于name，这个sql会尝试加一个插入意向锁。但是， session B 持有一个间隙锁，因此 session A 必须等待 session B 释放该间隙锁。现在我们可以看到这个死锁是如何发生的。 Innodb 将根据成本选择一个 session 进行回滚。这里 session B 将被回滚。
对于 Q1，答案是肯定的。
对于 Q2，实际上，已删除的记录在 session 提交之前不会从索引中清除。
对于 Q3，行锁编号等于 trx_rows_locked ，并在 mysql 网站中，其:

TRX_ROWS_LOCKED

The approximate number or rows locked by this transaction. The valuemight include delete-marked rows that are physically present but notvisible to the transaction.

从这里 article ，我们可以知道:

3. For non-clustered unique index filtering, due to the need to return tables, the number of filtered rows is locked as the unique index plusthe number of returned rows.

4. For non-clustered non-unique index filtering, the gap lock is involved, so more records are locked.

因此，在 session A 中删除后 trx_rows_locked(间隙锁 + 下一个键锁 + 返回表)为 3。尝试插入后，最终的 trx_rows_locked 值应为 3 + 1(插入键锁)。

以下是针对新的更新问题:
我之前没有注意到删除主键和唯一的辅助键。
经过一番调查，我发现:

删除 primary key 时，已经被删除还没有提交，新的删除操作只需要record lock而不是下一键锁定。

删除 secondary unique key 时，已删除且尚未提交，新的删除操作将需要 next-key lock .

您可以使用 set GLOBAL innodb_status_output_locks=ON; show engine innodb status查看正在运行的交易的详细锁定状态。