在试图克服战斗服务器上的死锁时，我走进了死胡同。有两个表：
第一：

create table table_1
(
    id    int auto_increment
        primary key,
    data1 text null,
    data2 text null
);

第二：

create table table_2
(
    id    int auto_increment
        primary key,
    t1_id int  null,
    data1 text null,
    data2 text null,
    constraint table_2_table_1_id_fk
        foreign key (t1_id) references table_1 (id)
            on update cascade on delete cascade
);

对于测试，table_1中有30条记录，table_2中有60条记录（table_2中的每2条记录通过键引用table_1中的1条记录）。
接下来，一个简单的php脚本，在无限循环中根据table_2中的条件更新table_1中的一些记录：

<?php
$db = new PDO("mysql:host=127.0.0.1;dbname=test_db;", 'debian-sys-maint', 'pass', [
    PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8'",
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
]);
$random = rand(0, 9);
while (true) {
    $db->exec("
UPDATE table_1
INNER JOIN table_2 on table_1.id = table_2.t1_id and table_2.data2 like '%$random%'
SET table_1.data1 = table_1.data1 + 1,
    table_1.data2 = table_1.data2 + 1;");
}

因此，我在150个示例中运行了这个PHP脚本，并生成了一个死锁错误。我试图通过修改查询来修复它，如下所示：
一个三个一个x一个四个一个x一个五个一个x一个六个一个

• • 实际上第一个问题是：**我不明白死锁是从哪里来的，如果记录总是按相同的顺序排序，那么竞争条件就被排除了。这是SHOW ENGINE INNODB STATUS;捕获死锁时返回的结果：

------------------------
LATEST DETECTED DEADLOCK
------------------------
2022-05-10 01:56:01 140233769219840
*** (1) TRANSACTION:
TRANSACTION 3529589, ACTIVE 1 sec starting index read
mysql tables in use 2, locked 2
LOCK WAIT 7 lock struct(s), heap size 1128, 90 row lock(s)
MySQL thread id 20, OS thread handle 140233708357376, query id 173 localhost 127.0.0.1 debian-sys-maint executing
UPDATE table_1
INNER JOIN table_2 on table_1.id = table_2.t1_id and table_2.data2 like '%9%'
SET table_1.data1 = table_1.data1 + 1,
    table_1.data2 = table_1.data2 + 1

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 2 page no 4 n bits 224 index PRIMARY of table `test_db`.`table_1` trx id 3529589 lock_mode X locks rec but not gap
.....................
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2 page no 4 n bits 224 index PRIMARY of table `test_db`.`table_1` trx id 3529589 lock_mode X locks rec but not gap waiting
.....................
*** (2) TRANSACTION:
TRANSACTION 3529887, ACTIVE 0 sec starting index read
mysql tables in use 2, locked 2
LOCK WAIT 5 lock struct(s), heap size 1128, 52 row lock(s)
MySQL thread id 118, OS thread handle 140229428811520, query id 444 localhost 127.0.0.1 debian-sys-maint executing
UPDATE table_1
INNER JOIN table_2 on table_1.id = table_2.t1_id and table_2.data2 like '%7%'
SET table_1.data1 = table_1.data1 + 1,
    table_1.data2 = table_1.data2 + 1

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 2 page no 4 n bits 224 index PRIMARY of table `test_db`.`table_1` trx id 3529887 lock_mode X locks rec but not gap
.....................
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2 page no 4 n bits 224 index PRIMARY of table `test_db`.`table_1` trx id 3529887 lock_mode X locks rec but not gap waiting
.....................
*** WE ROLL BACK TRANSACTION (2)

我设法解决死锁的唯一方法是创建一个临时表，其中包含一组现成的键，这些键将立即受到来自table_1的UPDATE的影响：

DROP TABLE IF EXISTS tmp1;
CREATE TEMPORARY TABLE tmp1
SELECT table_2.t1_id FROM table_2 WHERE table_2.data2 like '%$random%'

UPDATE table_1
    INNER JOIN tmp1 on table_1.id = tmp1.t1_id
SET table_1.data1 = table_1.data1 + 1,
    table_1.data2 = table_1.data2 + 1;

或者，我可以在临时表中创建table_2的副本，同样也不会导致死锁：

DROP TABLE IF EXISTS tmp1;
CREATE TEMPORARY TABLE tmp1
SELECT * FROM table_2 WHERE 1;

UPDATE table_1
    INNER JOIN tmp1 on table_1.id = tmp1.t1_id and tmp1.data2 like '%$random%'
SET table_1.data1 = table_1.data1 + 1,
    table_1.data2 = table_1.data2 + 1;

• • 第二个问题是：**为什么这个选项不会导致死锁？据我所知，被阻塞的是来自table_1的记录，在这个例子中，它们以同样的方式被阻塞，只是搜索不是根据table_2进行的，而是根据临时表tmp1进行的。

看起来我的问题解决了，死锁没有被调用，但是我不喜欢使用临时表的解决方案，我继续测试，在此期间我遇到了一个非常奇怪的事情，最终将我推向了死胡同。如果您自己启动并完成事务，那么死锁就不会出现：

BEGIN;
UPDATE table_1
INNER JOIN table_2 on table_1.id = table_2.t1_id and table_2.data2 like '%$random%'
SET table_1.data1 = table_1.data1 + 1,
    table_1.data2 = table_1.data2 + 1;
COMMIT;

• • 这可能是最后一个也是最令我兴奋的问题了...**我启用了自动提交，为什么如果我不显式启动和完成事务，就会出现死锁？众所周知，PHP在一个线程中工作，对于一个脚本来说，正好有一个到数据库的连接，所有150个脚本并行工作