MySQL：删除同一行时发生死锁 [英] MySQL: A deadlock occurred when deleting the same row

问题描述

最近我在删除记录时遇到死锁（请注意，隔离级别为 REPEATABLE READ ，MySQL 5.7）

Recently I encountered a deadlock when deleting records (Note that the isolation level is REPEATABLE READ, MySQL 5.7)

这是repro步骤

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状态的结果如下所示（最新检测到的死锁部分）

---

The result of show engine innodb status as below shown (LATEST DETECTED DEADLOCK section)

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 ，并且会话保持记录锁定 C ，等待间隙锁定 C ；

As the Innodb status shown, session B is waiting next-key lock C, and session A hold a record lock C and waiting gap lock on C;

DELETE FROM ... WHERE ...在搜索遇到的每条记录上设置排他的下一键锁定

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.

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

Q1: I guess if session B firstly got the gap lock (part of next-key), and then waiting for the record lock. Thereby, the latter insert in session A was blocked by session B (due to the gap lock), and eventually result in a dead lock. Right?

第二季度：当从索引中清除 C 时，会话B保持的间隙锁定应为（'广告'）？如果是这样，为什么会话A正在等待范围（，'C'）上的插入强度锁定？

Q2: As the C is purged from an index, does the gap lock hold by session B should be ('A', 'D')? If so, why the session A is waiting the insert intension lock on range (, 'C')?

Q3 ：为什么会话B的 1个行锁，而会话A具有 4个行锁？

Q3: Why session B has 1 row lock(s), and session A has 4 row lock(s)?

第4季度：当将索引 p_name 更改为唯一索引时，由于间隙锁定，我们仍然会出现死锁，有点奇怪。它的行为不同于官方的 doc ，其中指出

Q4: When change index p_name to a unique index, we still get the deadlock due to gap lock, it's weird. It behaves different from official doc which states only record lock is required.

DELETE FROM ... WHERE ...在搜索遇到的每条记录上设置独占的下一键锁定。但是，对于使用唯一索引锁定行以搜索唯一行的语句，仅需要索引记录锁定。

---

但是，使用主键 id 执行删除（步骤如下所示）是可以的。这是MySQL中的错误吗？

---

However, it is okay when using primary key id to execute delete (steps as below shown). Is this a bug in 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的一部分中，我们可以看到：

From "WAITING FOR THIS LOCK TO BE GRANTED" part of transaction 3631, we could see:

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正在等待记录锁定。相应的索引内容为{名称： C， id：24}。


2. 索引名称为表t中的p_name。


3. lock的模式为 lock_mode X

来自等待此锁被授予交易3630的一部分，我们可以看到：

From "WAITING FOR THIS LOCK TO BE GRANTED" part of transaction 3630, we could see:

*** (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正在等待记录锁定。相应的索引内容是{名称： C， id：24}。等待锁的模式为 lock_mode X locks gap


2. 3630持有记录锁。相应的索引内容是{名称： C， id：24}。保持锁的模式为 lock_mode X locks。


3. 索引名称为表t中的p_name。


4. 此死锁是由于执行插入引起的变成t个（名称）值（'B'）'

1. 3630 is waiting a record lock. The corresponding index content is {"name":"C", "id": 24}. waiting lock's mode is "lock_mode X locks gap"
2. 3630 is holding a record lock. The corresponding index content is {"name":"C", "id": 24}. Holding lock's mode is "lock_mode X locks"
3. The index name is p_name in table t.
4. This deadlock is caused by executing "insert into t (name) values ('B')"

根据您的复制步骤，会话A将发送删除从t其中name ='C'; 首先，它将锁定：

According to your reproduce step, session A will send a delete from t where name = 'C'; first, this will lock:

1. （'A'，'C']和（'C'，'D'）：下一键锁定'C'和'D'之前的空格锁定；

从...删除...在哪里... 在搜索遇到的每条
记录上设置一个独占的next-key锁，但是，对于使用唯一索引搜索
的行来锁定行的语句，只需要
的索引记录锁即可。

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.

1. 为 C对应的主索引ID添加记录锁。此处的ID值应该是 26。

Th会话B将开始，并且从名称为 C的t中删除； 将再次执行。然而。对于会话B，因为尚未提交会话A，所以会话C已锁定 C。但是，如果执行删除sql，则会话B将尝试按以下顺序添加锁：

Then session B will start and delete from t where name = 'C'; will be executed again. However. For session B, because session A hadn't been committed, 'C' had been locked by session A. However, if execute a delete sql, session B will try to add lock in the following sequence:

1. 'C'之前的间隙锁：成功，因为innodb可以在同一位置添加多个间隙锁。


2. 记录锁'C'：已阻止，因为会话A持有该锁定。会话B必须等待会话A释放它。


3. 在'D'之前的间隙锁：

1. gap lock before 'C': Success, because innodb could add multi gap lock in the same position.
2. record lock 'C': Blocked, because session A have holded that lock. session B have to wait it released by session A.
3. gap lock before 'D':

最后，会话A将插入到t（名称）值（'B'）; 中。对于表 t ，有2个索引，分别是 id 和 name 。 id 是一个自动增加的主整数键，对于此名称，此sql将尝试添加插入意图锁。但是，会话B拥有一个间隙锁，因此会话A必须等待会话B释放该间隙锁。现在我们可以看到此死锁是如何发生的。 Innodb将基于成本选择要回滚的会话。在这里会话B将被回滚。

At last, session A send insert into t (name) values ('B');. For table t, there are 2 indexes, which are id and name. id is a auto increasement primary integer key, and for name, this sql will try to add a insert intention lock. However, there have been a gap lock which is holded by session B, therefor session A have to wait session B for releasing that gap lock. Now we could see how this dead lock occur. Innodb will choose a session to rollback base on cost. Here session B will be rolled back.

对于第一季度，答案是肯定的。
实际上，对于第2季度，在会话提交之前，不会从索引中删除已删除的记录。
对于第3季度，行锁号等于 trx_rows_locked ，在mysql网站中，其行列号为：

For Q1, the anwser is yes. For Q2, actually, the deleted record wouldn't be purged from an index before its session commit. For Q3, row lock number is equal to trx_rows_locked, and in mysql website, its:

TRX_ROWS_LOCKED

此交易锁定的近似数字或行。
的值可能包括删除标记的行，这些行实际存在但对事务不可见的
。

The approximate number or rows locked by this transaction. The value might include delete-marked rows that are physically present but not visible to the transaction.

来自此文章，我们知道：

1. 对于非聚集式唯一索引过滤，由于需要返回表，因此过滤后的数目行被锁定为唯一索引加上
   返回的行数。

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

对于非聚集非唯一索引过滤，

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

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

So, trx_rows_locked (gap lock + next-key lock + return table) is 3 after delete in session A. final trx_rows_locked value should be 3 + 1 (insert key lock) after trying to insert.

以下是有关新更新的问题：
我没有注意到删除

The following are for the new update questions: I didn't notice delete primary key and unique secondary key before.

经过一番调查后，我发现：

After some investigating, I found:

1. 删除主键，它们已经被删除并且尚未提交，新的删除操作将只需要记录锁定，而不是下一键锁定


2. 在删除已删除但尚未提交的二级唯一密钥时，新的删除操作将需要 next-key lock 。

1. When deleting a primary key, which have been deleted and not commited yet, the new delete operation will only require record lock instead of next-key lock.
2. When deleting a secondary unique key, which have been deleted and not commited yet, the new delete operation will require next-key lock.

您可以使用 set GLOBAL innodb_status_output_locks = ON;显示引擎的innodb状态以查看运行事务的详细信息锁定状态。

You could use set GLOBAL innodb_status_output_locks=ON; show engine innodb status to see the detail lock status for running trasactions.

这篇关于MySQL：删除同一行时发生死锁的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！