Bug #101407 cant get a exist data. even no error throw by api(php)
Submitted: 31 Oct 2020 12:44 Modified: 4 Nov 2020 4:15
Reporter: BB DD Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: C API (client library) Severity:S3 (Non-critical)
Version:5.7 OS:Linux
Assigned to: CPU Architecture:Any

[31 Oct 2020 12:44] BB DD
Description:
I also give it on the stack overflow.  i still don't know is this a bug or not? 
or just me have the wrong use.
https://stackoverflow.com/questions/64575171/is-this-mysql-bug-about-select-for-update-loc...

trx1: got gap lock first then
trx2: got gap lock wait.
trx1: change to insert attention lock then trx2 will give a deadlock

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.

How to repeat:
//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 even not need to commit
show engine innodb status\G;

Suggested fix:
Even I don't know how to stop the program. 
and use errorCode in API also cant catch this error.

//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();
[31 Oct 2020 16:57] MySQL Verification Team
Thank you for the bug report. Please try and provide a test case with C, PHP isn't supported here. Thanks.
[1 Nov 2020 8:09] BB DD
thanks for you can paste "How to repeat:"  to show this error, please.

because PHP uses C API to call MySQL. I have not a C test case.
I still a new C language developer
[1 Nov 2020 8:16] BB DD
use Mysql can show this error(how to repeat): then will give a deadlock even though I do not think it should happen 

but use errorCode by C API can't found this deadlock happened and the Mysql Error log has nothing.  that's why I mention PHP here sorry
[2 Nov 2020 12:50] MySQL Verification Team
HI Mr. DD,

Thank you for your bug report.

However, this is not a bug.

What you have provided us with is a typical scenario for the deadlock. The fact that InnoDB Storage Engine is detecting a deadlock is a proof that it works correctly.

Not a bug.
[4 Nov 2020 4:09] BB DD
but not give any error when can't get a existing data in API. how do I know the  deadlock happened?
and i want insert a absolute new data.
my idea is get a X lock first . then insert a new.

select .. order by id desc for update.
insert ....

now you tell me that's a typical scenario for the deadlock.

i just want to insert absolute new data.
so how should i do?
[4 Nov 2020 4:15] BB DD
select .. order by id desc limit 1 for update
or 
select max(id) .. for update.

in fact, if I not use "max(id) or order by id desc"  that's correct.
but how do I get absolute new data?
[4 Nov 2020 12:49] MySQL Verification Team
Hi,

For your information, deadlock returns the error, which you should check on each DML statement.

You should also read our Reference Manual on which locks need to be taken by INSERT command.

Since this is not a bug, but a request for the explanation of how InnoDB SE functions, please read carefully the next comment.
[4 Nov 2020 12:49] MySQL Verification Team
We're sorry, but the bug system is not the appropriate forum for asking help on using MySQL products. Your problem is not the result of a bug.

For details on getting support for MySQL products see http://www.mysql.com/support/
You can also check our forums (free) at http://forums.mysql.com/

Thank you for your interest in MySQL.