<p>假设我们有以下表：</p> <pre class="brush:php;toolbar:false;">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) );</pre> <p>我正在向names表中添加记录，但在添加之前，我删除了所有记录，然后重新创建它们（只是为了重现死锁）</p> <p>开始两个单独的事务。事务-1</p> <pre class="brush:php;toolbar:false;">START transaction; DELETE FROM names where last_name="x"; <不要提交或回滚></pre> <p>事务-2</p> <pre class="brush:php;toolbar:false;">START transaction DELETE FROM names where last_name="y"; <不要提交或回滚></pre> <p>然后在事务-1中</p> <pre class="brush:php;toolbar:false;">INSERT INTO names VALUES ("a", "x");</pre> <p>在事务-2中</p> <pre class="brush:php;toolbar:false;">INSERT INTO names VALUES ("b", "y");</pre> <p>这会导致死锁。</p> <p>我不确定为什么会发生死锁。根据我了解，InnoDB锁定表的行，而不是整个表。两个事务都在删除不同的记录并添加不同的记录。那么为什么会发生死锁呢？</p> <p>以下是更多细节</p> <pre class="brush:php;toolbar:false;">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 | +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+</pre> <p>以下是来自<code>SHOW ENGINE INNODB STATUS</code>的死锁信息</p> <pre class="brush:php;toolbar:false;">------------------------ 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</pre></p>