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

最近删除记录时遇到死锁（注意隔离级别是可重复读取, 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
------------------------
*** (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状态所示，会话B正在等待next-key lockC，并且会话A持有记录锁C并等待间隙锁定C;

众所周知

DELETE FROM ... WHERE ... 对搜索遇到的每个记录设置独占的下一键锁定

下一个键锁是索引记录上的记录锁和索引记录之前的间隙上的间隙锁的组合。

Q1：我猜测会话B是否首先获得间隙锁（next-key的一部分），然后等待记录锁。因此，会话A中的后者插入被会话B阻塞（由于间隙锁），最终导致死锁。正确的？

Q2: 作为C从索引中清除，会话 B 持有的间隙锁是否应该为 ('A', 'D')？如果是这样，为什么会话 A 正在等待范围（，'C'）上的插入意图锁？

Q3: 为什么会话B有1 row lock(s)，并且会话 A 有4 row lock(s)?

Q4：改变索引时p_name对于唯一索引，我们仍然会由于间隙锁而陷入死锁，这很奇怪。和官方的表现不一样doc https://dev.mysql.com/doc/refman/8.0/en/innodb-locks-set.html其中指出仅需要记录锁定。

DELETE FROM ... WHERE ... 对搜索遇到的每个记录设置独占的下一键锁定。然而，对于使用唯一索引锁定行来搜索唯一行的语句，只需要索引记录锁.

不过使用主键的时候就可以了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;                                   |                                      |
+-------------------------------------------+--------------------------------------+

从事务 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

1. 3631 正在等待记录锁。对应的索引内容为{"name":"C","id":24}。
2. 索引名称是表 t 中的 p_name。
3. 锁的模式是“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         ;;

1. 3630 正在等待记录锁。对应的索引内容为{"name":"C","id":24}。等待锁的模式是“lock_mode X 锁间隙”
2. 3630 正在持有记录锁。对应的索引内容为{"name":"C","id":24}。持有锁的模式是“lock_mode X locks”
3. 索引名称是表 t 中的 p_name。
4. 这个死锁是由于执行“insert into t(name)values('B')”引起的

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

1. ('A', 'C'] 和 ('C', 'D')：下一个键锁定 'C' 和 'D' 之前的间隙锁定；

从...删除... https://dev.mysql.com/doc/refman/8.0/en/innodb-locks-set.html对每个设置独占的下一键锁定 记录搜索遭遇。但是，只有索引记录锁是 对于使用唯一索引进行搜索锁定行的语句是必需的 对于唯一的行。

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

然后会话 B 将开始并且delete from t where name = 'C';将再次被执行。然而。对于会话 B，由于会话 A 尚未提交，“C”已被会话 A 锁定。但是，如果执行删除 sql，会话 B 将尝试按以下顺序添加锁定：

1. 'C'之前的间隙锁：成功，因为innodb可以在同一位置添加多个间隙锁。
2. 记录锁'C'：Blocked，因为会话 A 已持有该锁。会话 B 必须等待会话 A 释放它。
3. 'D' 之前的间隙锁定：

最后，会话A发送insert into t (name) values ('B');。餐桌用t，有2个索引，分别是id and name. id是一个自增主整型键，对于name，这条sql会尝试添加一个插入意向锁。然而，会话 B 持有一个间隙锁，因此会话 A 必须等待会话 B 释放该间隙锁。现在我们可以看看这个死锁是如何发生的。 Innodb会根据成本选择一个会话进行回滚。这里会话B将被回滚。

对于Q1，答案是肯定的。 实际上，对于第二季度，在会话提交之前，删除的记录不会从索引中清除。 对于 Q3，行锁数量等于trx_rows_locked，在 mysql 网站中，其：

TRX_ROWS_LOCKED https://dev.mysql.com/doc/refman/5.7/en/information-schema-innodb-trx-table.html

The 大概的数量或行数被本次交易锁定。价值 可能包括物理上存在但不存在的删除标记行 对交易可见。

由此article https://developpaper.com/understanding-mysql-lock-details-based-on-updating-sql-statements/，我们可以知道：

3. 对于非聚集唯一索引过滤，由于需要回表，所以锁定过滤行数为唯一索引加上 返回的行数。

4. 对于非聚集非唯一索引过滤，会涉及到间隙锁，因此会锁定更多的记录。

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

以下是新的更新问题： 我之前没有注意到删除主键和唯一辅助键。

经过一番调查，我发现：

1. 当删除一个primary key，已被删除且尚未提交，新的删除操作符只需要record lock而不是下一键锁。
2. 当删除一个secondary unique key，已被删除且尚未提交，新的删除操作符将需要next-key lock.

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