これらの同時トランザクションがデッドロックの問題を引き起こすのはなぜですか? (MySQL 8.0 InnoDB エンジンの場合)

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

last_name にはインデックスが作成されません。DELETEs はテーブル全体を検索する必要があります。いいえ、既存の主キーは役に立ちません。

INDEX(last_name) を追加すると問題が解決する可能性があります。何らかの理由で first_name の参照位置が必要な場合を除き、代わりに PRIMARY KEY(last_name, first_name) を使用する方が効率的です (@danblack を参照)。