how to insert a absolute number of data in High concurrency. About the gap lock upgrade insert intention lock

  laravel, mysql-5.7, pdo, php

expect:
Have a table data like this:

id | aid | number

1 | 10 | 10

2 | 10 | 15

now i want insert id=3 number= 20(addition data of id=2 )
In PHP my code like below. when some order to executed (when the gap lock upgrade insert intention) will get this error. but no error happened and the program even not stop.

repeat this error below:
trx1 and 2 or (file1 and 2 in php) are executed in two terminal query while.

//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 statusG;

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'). "rn";
sleep(10); // wrong
DB::table('test_lock')->insert(
    ['aid' => $aid]
);
echo "after insert:" . date('Y-m-d H:m:s.u'). "rn";
//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'). "rn";
var_dump($pdo->errorCode()); // 00000
$ret = DB::table('test_lock')->insert(
    ['aid' => $aid]
);
echo "after insert:" . date('Y-m-d H:m:s.u'). "rn";
DB::commit();

execute file1. then open another terminal to execute file2 can get this error.

i have reported this error on mysql bug center:
https://bugs.mysql.com/bug.php?id=101407

but they told me it’s not a bug. just a typical scenario for the deadlock.
but just change the sleep position(in file1) can work as expected.
Now how should i do?

Source: Laravel

Leave a Reply