Is this Mysql Bug? About SELECT … FOR UPDATE lock_mode X insert intention

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

enter image description here

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

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

have some way can give me help, please?

from Newest questions tagged laravel-5 – Stack Overflow https://ift.tt/31So8LJ
via IFTTT

Related Posts

Codeigniter : Parse error: syntax error, unexpected ‘const’ (T_CONST), expecting variable (T_VARIABLE) in Laravel project

I’m getting following error: **Parse error: syntax error, unexpected ‘const’ (T_CONST), expecting variable (T_VARIABLE)** Note : It’s working in local but facing issue in production server. private…

Firebase receive notification while tab is active or on focus

What i want is to be able to perform an action when a user receives a notification while the browser is open and tab is active or…

Laravel’s alias loader does not find class

We have a legacy project that we cannot update and we need to make some changes in symfony’s Response.php in vendor. We have solved this by copying…

Laravel 5 – generic document management

I have a system where you can create different types of unique documents. For instance, one document is called Project Identified and this expects certain inputs. Originally,…

Laravel Nova limit the results in indexQuery

I ran intro a situation where I need to limit the results of a resource to only 3 results. To be more specific, based on the logged…

Auditoria en laravel 5.8 [closed]

Cómo puedo automatizar el registro de actividades de un usuario en laravel? Si un usuario ingresa a un app de laravel, debo guardar toda su actividas, a…

Leave a Reply

Your email address will not be published.