假设我们有下表

CREATE DATABASE IF NOT EXISTS humans;
USE humans;

CREATE TABLE IF NOT EXISTS address (
    last_name VARCHAR(255) NOT NULL,
    address VARCHAR(255),
    PRIMARY KEY (last_name)
);

INSERT INTO address values ("x", "abcd");
INSERT INTO address values ("y", "asdf");

CREATE TABLE IF NOT EXISTS names (
    first_name VARCHAR(255) NOT NULL,
    last_name VARCHAR(255) NOT NULL,
    PRIMARY KEY (first_name, last_name),
    FOREIGN KEY (last_name) REFERENCES address(last_name)
);

我正在向names表中添加记录，但在添加之前，我要删除所有记录，然后重新创建它们（只是为了重现死锁）
启动2个单独的事务。交易-1

START transaction;
DELETE FROM names where last_name="x";
<do not commit or rollback>

交易-2

START transaction
DELETE FROM names where last_name="y";
<do not commit or rollback>

然后在事务1中

INSERT INTO names VALUES ("a", "x");

并且在事务-2中

INSERT INTO names VALUES  ("b", "y");

这将导致死锁。
我不知道为什么。IIUC，innoDB锁定表的行，而不是整个表。这两个事务都在删除单独的记录和添加单独的记录。为什么会出现僵局呢？
这里有更多的细节

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.33    |
+-----------+
1 row in set (0.00 sec)

mysql> show create table names;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                  |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| names | CREATE TABLE `names` (
  `first_name` varchar(255) NOT NULL,
  `last_name` varchar(255) NOT NULL,
  PRIMARY KEY (`first_name`,`last_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

下面是来自SHOW ENGINE INNODB STATUS的死锁

------------------------
LATEST DETECTED DEADLOCK
------------------------
2023-06-13 09:46:39 0x700005d8d000
*** (1) TRANSACTION:
TRANSACTION 23728, ACTIVE 305 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1128, 3 row lock(s), undo log entries 1
MySQL thread id 1177, OS thread handle 123145414819840, query id 307296 localhost root update
INSERT INTO names VALUES ("a", "x")

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 492 page no 5 n bits 72 index last_name of table `humans`.`names` trx id 23728 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;;

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 492 page no 5 n bits 72 index last_name of table `humans`.`names` trx id 23728 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 23729, ACTIVE 302 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1128, 3 row lock(s), undo log entries 1
MySQL thread id 1178, OS thread handle 123145415884800, query id 307297 localhost root update
INSERT INTO names VALUES  ("b", "y")

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 492 page no 5 n bits 72 index last_name of table `humans`.`names` trx id 23729 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 492 page no 5 n bits 72 index last_name of table `humans`.`names` trx id 23729 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)
------------
TRANSACTIONS