为什么两个并发的delete + insert语句在一个空表上死锁? [英] Why do two concurrent delete + insert statements deadlock on an empty table?

问题描述

我很想知道为什么当主键不存在时，两个同时使用主键的DELETE和INSERT语句导致MySQL死锁.该示例旨在以最简单的形式说明问题.

I'm curious to know why two concurrent DELETE followed by INSERT statements that use primary keys causes a deadlock in MySQL when the primary keys don't exist. The example is contrived to illustrate the issue in it's simplest form.

这是设置.

> SELECT @@GLOBAL.tx_isolation, @@tx_isolation;
+-------------------------+------------------+
| @@GLOBAL.tx_isolation   | @@tx_isolation   |
|-------------------------+------------------|
| REPEATABLE-READ         | REPEATABLE-READ  |
+-------------------------+------------------+
1 row in set
Time: 0.002s

> select version();
+-------------+
| version()   |
|-------------|
| 5.7.12      |
+-------------+
1 row in set
Time: 0.002s

create table lock_test ( id int(11) not null, primary key (`id`) );

下面，1>代表一个mysql终端，而2>代表另一个.

Below, 1> represents one mysql terminal and 2> represents another.

1> begin;
1> delete from lock_test where id = 1;

2> begin;
2> delete from lock_test where id = 2;

1> insert into lock_test values (1); -- hangs

2> insert into lock_test values (2);
*** deadlock ***

这是show engine innodb status输出:

------------------------
LATEST DETECTED DEADLOCK
------------------------
2018-06-06 16:15:18 0x70000ba52000
*** (1) TRANSACTION:
TRANSACTION 807765, ACTIVE 46 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 620, OS thread handle 123145496289280, query id 43097 localhost ::1 root update
insert into lock_test values (1)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 6819 page no 3 n bits 72 index PRIMARY of table `content_graph`.`lock_test` trx id 807765 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) TRANSACTION:
TRANSACTION 807766, ACTIVE 37 sec inserting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 617, OS thread handle 123145497681920, query id 43099 localhost ::1 root update
insert into lock_test values (2)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 6819 page no 3 n bits 72 index PRIMARY of table `content_graph`.`lock_test` trx id 807766 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 6819 page no 3 n bits 72 index PRIMARY of table `content_graph`.`lock_test` trx id 807766 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** WE ROLL BACK TRANSACTION (2)

请注意，如果您首先插入ID为1和2的记录，然后重复上述顺序，则不会出现死锁.

Note that if you first insert records with ids 1, and 2 then repeat the sequence above there is no deadlock.

我的感觉是，因为键不在索引中(都是追加的)，所以删除操作必须锁定更多索引(主要数据库将进入的页面更多)，但是我想确保我理解了正确的.

My sense is that because the key is not in the index (both are appending) the delete has to lock more of the index (more of the page the primary would land in) but I want to make sure I have my understanding correct.

推荐答案

空白"被锁定，因为有人可能会尝试插入我要删除的行.

The "gap" is locked in anticipation that someone may try to insert the row I am trying to delete.

或者，换一种方式来看……要完美地处理每一个奇怪的情况，那太慢了.因此，InnoDB选择有效地处理大多数情况，并在罕见的奇数案中进行处理.

Or, to look at it another way... It would be too slow to perfectly handle every odd case. So, InnoDB chooses to handle most cases efficiently and punt on the rare oddball case.

底线:忍受它.您将陷入僵局.您不一定能够理解它们.但是您的代码需要恢复-只需回滚并返回BEGIN.

Bottom line: Live with it. You will get deadlocks. You won't necessarily be able to understand them. But your code needs to recover -- by simply rolling back and going back to the BEGIN.

这篇关于为什么两个并发的delete + insert语句在一个空表上死锁?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！