Bug #109388 two client can both grant "RECORD X supremum pseudo-record lock "

• Submitted: 15 Dec 2022 6:59
• Modified: 15 Dec 2022 12:48
• Reporter: b a
• Status: Not a Bug
• Category: MySQL Server: Locking
• Severity: S3 (Non-critical)
• Version: 8.0.28
• OS: CentOS
• CPU Architecture: x86
• Tags: lock, pseudo-record

[15 Dec 2022 6:59] b a

Description:
delete a non-exist record from table need a "RECORD X supremum pseudo-record lock".

but two client delete from the same table can granted "RECORD X supremum pseudo-record lock" at the same time.

this will cause deadlock when insert some data after the delete. 

How to repeat:
create table tb_test (id int,d varchar(10)); 

step 1 client A:
set autocommit=0;
delete from tb_test where  id=1; # non exist id 1,,grant RECORD X supremum pseudo-record lock

setp 2 client B:
set autocommit=0;
delete from tb_test where  id=2; # non exist id 2, grant another RECORD X supremum pseudo-record lock

step 3 client A:
insert into tb_test(id,d) values (1,'data'); # this will wait for "RECORD	X,INSERT_INTENTION supremum pseudo-record"

step 4 client B:
insert into tb_test(id,d) values (2,'data');# this trigger dead lock

Suggested fix:
let step 2 cliet B wait for RECORD X supremum pseudo-record lock

[15 Dec 2022 12:48] MySQL Verification Team

Hi Mr. a,

Thank you for your bug report.

However, it is not a bug.

That is how ACID standard prescribes that locking should be performed. When you are inserting a row at the end or deleting the one that does not exist, but has to be searched at the end of the index, then supremum record has to be locked.

This has all been described in our Reference Manual.

Not a bug.