Why do these concurrent transactions cause deadlock problems? (For MySQL 8.0 InnoDB engine)

Question

假设我们有以下表：

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表中添加记录，但在添加之前，我删除了所有记录，然后重新创建它们（只是为了重现死锁）

开始两个单独的事务。事务-1

START transaction;
DELETE FROM names where last_name="x";
<不要提交或回滚>

事务-2

START transaction
DELETE FROM names where last_name="y";
<不要提交或回滚>

然后在事务-1中

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

在事务-2中

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

这会导致死锁。

我不确定为什么会发生死锁。根据我了解，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

Answer 1

No index is created on last_name, DELETEs must search the entire table. No, your existing primary key won't help.

Adding INDEX(last_name) may solve your problem. It would be more efficient (see @danblack) to instead PRIMARY KEY(last_name, first_name), unless there is some reason you need a reference position of first_name.