为什么并发的"Delete...Insert“语句会导致死锁？

Question

考虑mysql中的以下模式：

create table foo(
  id int not null primary key auto_increment,
  name varchar(32) not null,
  unique key(name)
);

表中还有一个名字叫"abc“的记录。

我有一个交易(RC)：

start transaction;
delete from foo where name = "abc";
insert into foo(name) values("abc");
commit;

如果存在两个并发事务，则会发生死锁。

       |        TX A         |             TX B
---------------------------------------------------------------------
Step 1 | start transaction;  | 
       | delete name="abc";  |
---------------------------------------------------------------------
Step 2 |                     | start transaction;
       |                     | delete name="abc";
       |                     | <wait for lock>
---------------------------------------------------------------------
Step 3 | insert name="abc";  | <deadlock detected, exit>
---------------------------------------------------------------------
Step 4 | commit;             |
---------------------------------------------------------------------

我想知道为什么这个序列会导致死锁。

在mysql文档中说(https://dev.mysql.com/doc/refman/8.0/en/innodb-locks-set.html)

如果发生重复键错误，则在重复索引记录上设置共享锁.如果有多个会话试图插入同一行(如果另一个会话已经具有独占锁)，则共享锁的这种使用会导致死锁。如果另一个会话删除行，则可能发生这种情况。

我想，事务A运行"delete“语句时，它已经获得了记录"abc”的X锁。当"insert“语句执行时，由于”重复键错误“，它尝试获取S锁。既然它有相同记录的X锁，那么它不应该得到S锁吗？为什么这里会出现僵局？

Answer 1

我复制了死锁，得到了innoDB状态日志，如下所示：

------------------------
LATEST DETECTED DEADLOCK
------------------------
2019-10-18 18:35:14 0x7f1dfc738700
*** (1) TRANSACTION:
TRANSACTION 26547965, ACTIVE 6 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
/* ApplicationName=DataGrip 2019.1.1 */ delete from foo where name='abc'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 3011 page no 4 n bits 224 index IDX_NAME of table `foo` trx id 26547965 lock_mode X locks rec but not gap waiting
Record lock, heap no 153 PHYSICAL RECORD: n_fields 2; ....

*** (2) TRANSACTION:
TRANSACTION 26547960, ACTIVE 10 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 2
/* ApplicationName=DataGrip 2019.1.1 */ INSERT INTO foo(id, name)
VALUES (1, 'abc')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 3011 page no 4 n bits 224 index IDX_NAME of table `foo` trx id 26547960 lock_mode X locks rec but not gap
Record lock, heap no 153 PHYSICAL RECORD: ...

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 3011 page no 4 n bits 224 index IDX_NAME of table `foo` trx id 26547960 lock mode S waiting
Record lock, heap no 153 PHYSICAL RECORD: ....

*** WE ROLL BACK TRANSACTION (1)

日志清楚地解释了TX等待X锁由TX持有的原因，同时TX等待S锁被TX的锁请求阻塞。

根据Mysql文档：

如果发生重复键错误，则在重复索引记录上设置共享锁.如果有多个会话试图插入同一行(如果另一个会话已经具有独占锁)，则共享锁的这种使用会导致死锁。“

Insert语句确实在某个时候获得了S锁，所以死锁发生的原因是非常清楚的。

但问题是：

• 根据mysql文档，如果出现重复密钥错误，insert语句将获得S锁，在当前情况下不会发生这种情况，我们讨论为什么insert语句在当前事务已经持有X锁时仍然获得S锁，X锁足以进行当前读取以检查重复密钥错误。那么它是用来干什么的呢?