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

Question

最近，我在删除记录时遇到了一个死锁(注意，隔离级别是可重复读取，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正在等待下一个密钥锁C，而会话A在C上持有记录锁C和等待间隙锁；

我们都知道

从..。在哪里..。在搜索遇到的每条记录上设置一个独占的下键锁。下一个键锁是索引记录上的记录锁和索引记录之前的间隙锁的组合。

Q1：我想如果会话B首先获得间隙锁(下一个键的一部分)，然后等待记录锁。因此，会话A中的后一个插入被会话B(由于间隙锁)阻塞，并最终导致一个死锁。对吗？

Q2：当C被从索引中清除时，B会话持有的间隙锁应该是('A'，'D')吗？如果是，为什么会话A在范围(，'C')上等待插入强度锁？

Q3：为什么会话B有1 row lock(s)，会话A有4 row lock(s)

Q4：当将索引p_name更改为唯一索引时，仍然会出现由于间隙锁定而导致的死锁，这是很奇怪的。它的行为不同于官方的文档，后者只声明需要记录锁。

从..。在哪里..。为搜索遇到的每一条记录设置一个独占的下键锁。但是，对于使用唯一索引来搜索唯一行的语句，只需要索引记录锁。

但是，使用主键id执行delete (步骤如下所示)是可以的。这是MySQL中的一个bug吗？

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的“等待此锁被授予”部分，我们可以看到：

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的“等待此锁被授予”部分，我们可以看到：

*** (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锁“
3. 索引名是表t中的p_name。
4. 此死锁是由执行“插入t (name)值('B')”引起的。

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

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

从..。在哪里..。为搜索遇到的每一条记录设置一个独占的下键锁。但是，对于使用唯一索引锁定行以搜索唯一行的语句，只需要索引记录锁。

1. 为相应的主索引id添加一个记录锁。这里的id值应该是"26“。

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

1. “C”之前的间隙锁:成功，因为innodb可以在同一位置添加多个间隙锁。
2. 记录锁'C'：阻止了，因为会话A保存了该锁。会话B必须等待它由会话A发布。
3. “D”之前的间隙锁：

最后，会话A发送insert into t (name) values ('B');。对于表t，有两个索引，即id和name。id是一个自动增加的主整数键，对于名称，此sql将尝试添加一个insert意图锁。但是，有一个由会话B持有的间隙锁，因此会话A必须等待会话B释放该间隙锁。现在我们可以看到这个死锁是如何发生的。Innodb将根据成本选择要回滚的会话。在这里，会话B将回滚。

对Q1来说，答案是肯定的。实际上，对于Q2，在会话提交之前不会从索引中清除已删除的记录。对于Q3，行锁号等于trx_rows_locked，而在mysql网站中，其：

上锁 此事务锁定的近似数或多行。该值可能包括实际存在但事务不可见的被删除标记的行。

从这个文章中，我们可以知道：

1. 对于非聚集唯一索引筛选，由于需要返回表，过滤后的行数被锁定为唯一索引加上返回的行数。
2. 对于非群集的非唯一索引筛选，需要使用间隙锁，因此会锁定更多的记录.

因此，trx_rows_locked (间隙锁+下键锁+返回表)是会话A中删除后的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查看运行trasactions的详细锁状态。

Answer 2

对于Q4，我终于找到了解释为什么使用下一个键锁的MySQL 5.7源代码中的注释，仅供参考。

在索引中最多可以匹配一条记录的搜索中，当锁定无删除标记的匹配记录时，我们可以使用LOCK_REC_NOT_GAP类型的记录锁。注意，在唯一的辅助索引中，可能有不同的delete标记的记录版本，其中只有主键值不同:因此，在辅助索引中，当锁定delete标记的记录时，我们必须使用next键锁。注意，如果其中一个列是SQL null，则唯一的辅助索引可以包含多个具有相同键值的行。MySQL下的聚集索引不能包含空列，因为我们要求主键中的所有列都是非空的。