With autocommits, the request causes a Deadlock. MySQL. InnoDB

In an attempt to overcome deadlocks on the combat server, I reached a dead end. There are 2 tables:

First:

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

Second:

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
);

For tests, there are 30 records in table_1, 60 in table_2 (every 2 records from table_2 refer by key to 1 record in table_1).

Next, a simple php script that updates some records from table_1, by condition from table_2 in an infinite loop:

<?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;");
}

So, I run this PHP script in 150 instances, and a Deadlock error is generated. I tried to fix it by modifying the query as follows:

UPDATE table_1
    INNER JOIN (SELECT t1_id FROM table_2 WHERE table_2.data2 like '%$random%' ORDER BY t1_id) table_2 on table_1.id = table_2.t1_id
SET table_1.data1 = table_1.data1 + 1,
    table_1.data2 = table_1.data2 + 1;

WITH tmp (id) AS (SELECT t1_id FROM table_2 WHERE table_2.data2 like '%$random%' ORDER BY t1_id)
UPDATE table_1
    INNER JOIN tmp on table_1.id = tmp.id
SET table_1.data1 = table_1.data1 + 1,
    table_1.data2 = table_1.data2 + 1;

UPDATE table_1
SET table_1.data1 = table_1.data1 + 1,
    table_1.data2 = table_1.data2 + 1
WHERE table_1.id in (SELECT t1_id FROM table_2 WHERE table_2.data2 like '%$random%' ORDER BY t1_id);

UPDATE table_1, (SELECT t1_id FROM table_2 WHERE table_2.data2 like '%$random%' ORDER BY t1_id) tmp1
SET table_1.data1 = table_1.data1 + 1,
    table_1.data2 = table_1.data2 + 1
WHERE table_1.id = tmp1.t1_id;

Actually the first question: I don’t understand where the deadlock comes from, if the records are always sorted in the same order, the race condition is excluded. This is what the SHOW ENGINE INNODB STATUS; returns when catching a deadlock:

------------------------
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)

The only way I managed to solve the deadlock was to create a temporary table that would contain a ready-made set of keys that would be affected by UPDATE immediately from table_1:

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;

Or with the same success I can create a copy of table_2 in a temporary table, it also does not cause deadlocks:

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;

The second question is: Why does this option not cause deadlocks? As I understand it, it is the records from table_1 that are blocked, in this case they are blocked in the same way, only the search does not take place according to table_2, but according to the temporary table tmp1.

It would seem that my problem is solved, deadlocks are not called, but I did not like the solution with a temporary table and I continued my tests during which I came across a very strange thing that finally drove me to a dead end. If you start and complete the transaction yourself, then deadlocks do not appear:

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;

This is probably the last and most exciting question for me… I have an autocommit enabled, why if I don’t explicitly start and complete the transaction, then deadlocks come out? PHP is known to work in one thread, for 1 script there is exactly 1 connection to the database, all 150 scripts work in parallel