Is this Mysql Bug？ About SELECT ... FOR UPDATE lock_mode X insert intention

Question

have a table struct is id, aid, ...... the aid is an index(a type of int)

trx1 and 2: begin;
trx1: select max(id) from a where aid = 10 for update;
trx2: select max(id) from a where aid = 10 for update; ## have blocked waiting trx 1
trx1: insert into a (........;  then trx 2 will throw a deadlock even not commit yet

this error can't throw out in PHP, no error in PHP and MySQL. just-auto rollback then continues to execute other code.

change MySQL query order below:

trx1 and 2: begin;
trx1: select max(id) ... for update;
trx1: insert ...;
trx2: select max(id) ... for update;
trx1: commit; the trx2 result is currectly

my MySql version is 5.7 I was saw(the same kind of type question):Solution for Insert Intention Locks in MySQL
and this:https://bugs.mysql.com/bug.php?id=25847

for test code below:

//prepared:
CREATE TABLE `test_lock` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `aid` int(11) NOT NULL,
  `otherinfo` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `aid` (`aid`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;
insert into `test_lock` (`aid`) values(10);
##trx1 and trx2
begin;
##trx1
select max(id) from `test_lock` where (`aid` = 10) limit 1 for update;
##trx2(have blocked)
select max(id) from `test_lock` where (`aid` = 10) limit 1 for update;
##trx1
insert into `test_lock` (`aid`) values(10);
##then trx2 will gave a deadlock error and look that error
show engine innodb status\G;

use PHP to test(i use Laravel5.6 Commands):

//file1:
$aid = 10;
DB::beginTransaction();
$result = DB::table('test_lock')->where('aid', $aid)->orderByDesc('id')->lockForUpdate()->first();
var_dump($result);
echo "after get:" . date('Y-m-d H:m:s.u'). "\r\n";
sleep(10); // wrong
DB::table('test_lock')->insert(
    ['aid' => $aid]
);
echo "after insert:" . date('Y-m-d H:m:s.u'). "\r\n";
//sleep(10);  // correctly and  file2 is correct result
DB::commit();

//file2
$aid = 10;
DB::beginTransaction();
$pdo = DB::connection()->getPdo();
$result = DB::table('test_lock')->where('aid', $aid)->orderByDesc('id')->lockForUpdate()->first();
var_dump($result); //NULL
echo "after get:" . date('Y-m-d H:m:s.u'). "\r\n";
var_dump($pdo->errorCode()); // 00000
$ret = DB::table('test_lock')->insert(
    ['aid' => $aid]
);
echo "after insert:" . date('Y-m-d H:m:s.u'). "\r\n";
DB::commit();

In PHP File2 result is NULL. Have not any error throw. no mysql error log left. you can use show engine innodb status\G; to found the deadlock happened.

have some way can give me help, please?

Answer 1

You probably should you try READ-COMMITTED isolation level as the bug report suggests.

Also regarding will throw a deadlock even not commit yet - deadlocks errors are traditionally never thrown during commit, because no InnoDB locks are aquired during commit.