Bug #113175 Getting wrong result for locked rows

• Submitted: 22 Nov 2023 5:55
• Modified: 22 Nov 2023 11:35
• Reporter: Harshit Gangal
• Status: Not a Bug
• Category: MySQL Server: Locking
• Severity: S3 (Non-critical)
• Version: 8.0.34
• OS: Any
• CPU Architecture: Any

[22 Nov 2023 5:55] Harshit Gangal

Description:
If one session acquires a lock and modifies the rows with delete followed by insert. The other session waiting to acquire the lock initially blocked does not get the updated result set when unblocked.

How to repeat:
Initial Setup:

drop table if exists some_table;
			
create table some_table (
 id bigint,
 col varchar(10),
 primary key (id),
 unique index(col)
);
		
insert into some_table(id, col) values (3, null), (4, '5');

Steps:

Open 2 sessions

Session 1:
begin;
select col from some_table where col = '5' or id = 3  for update;

Session 2:
begin;
select col from some_table where col = '5' for update; -- blocked for lock

Session 1:
delete from some_table where col = '5' or id = 3;
insert into some_table(id, col) values (3, '5');
commit;

Session 2:
-- unlocks but returns no result
Expected: (3, '5')

select col from some_table where col = '5' for update; -- returns the updated result i.e. (3, '5')

Suggested fix:
As per the documentation
For a unique index with a unique search condition, InnoDB locks only the index record found, not the gap before it. This should be modified to take the gap lock for unique index as well.

[22 Nov 2023 11:35] MySQL Verification Team

Hi Mr. Gangal,

Thank you very much for your bug report.

However, this can not be a bug.

You have inserted only a couple of rows in that table. Hence, no search is done by index. Instead all of the rows are scanned.

If you can send us a test case where the search will use index , instead of row scanning, we shall be glad to reconsider it.

Not a bug.