MySQL Bugs: #112523: UPDATE/DELETE not using PRIMARY index (even with FORCE INDEX)

Bug #112523	UPDATE/DELETE not using PRIMARY index (even with FORCE INDEX) - causing locks
Submitted:	26 Sep 2023 12:57	Modified:	28 Sep 2023 14:14
Reporter:	Yoni Sade	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	8.0.34	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
When updating or deleting InnoDB table rows by filtering on primary key column using a subquery, MySQL locks ALL rows instead of using PRIMARY index to lock on required rows causing one session to lock another.

As can be seen by running while it happens:

select * from performance_schema.data_locks;

select * from sys.innodb_lock_waits;

How to repeat:
Session 1:

set autocommit = 0;

CREATE TABLE test
(id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
group_id INT NOT NULL,
comment VARCHAR(1000));

insert into test (group_id) values (1);
insert into test (group_id) values (1);
insert into test (group_id) values (2);
insert into test (group_id) values (2);
insert into test (group_id) values (2);

commit;

START TRANSACTION;

UPDATE test
SET comment=''
WHERE id in (SELECT id FROM (SELECT id FROM test WHERE group_id in (SELECT group_id FROM test WHERE id=1)) t);

or

DELETE FROM test
WHERE id in (SELECT id FROM (SELECT id FROM test WHERE group_id in (SELECT group_id FROM test WHERE id=1)) t);

Session 2:

set autocommit = 0;

START TRANSACTION;

UPDATE test
SET comment=''
WHERE id in (SELECT id FROM (SELECT id FROM test WHERE group_id in (SELECT group_id FROM test WHERE id=3)) t);

or

DELETE FROM test
WHERE id in (SELECT id FROM (SELECT id FROM test WHERE group_id in (SELECT group_id FROM test WHERE id=3)) t);

Suggested fix:
Change optimizer behaviour to use PRIMARY/UNIQUE index instead of ALL or allow FORCE INDEX to have effect in UPDATE/DELETE commands.

Hi Mr. Sade,

Thank you for your bug report.

However, it is not a bug.

Your table is very small, so it is much more efficient to scan entire table then to search by index.

Try making a test case with thousand and more rows and see if the behaviour changes.

Not a bug.

But the side effect of this behaviour is the all the rows in the table are begin locked instead of the ones with IDs which return from the subquery, causing deadlocks and lock timeouts.

HI,

You are correct about it. 

This is also expected behaviour. If InnoDB has to change any rows, it will locks all rows that it has to read.

That is how MBCC storage engines function.

Then can you change this entry to a feature request?

Allowing DELETE & UPDATE statements to be affected from FORCE INDEX or /*+INDEX*/ hint and not using ALL method to update the table/index.

Hi,

Unfortunately we can not even make it as a feature request.

Optimiser decides how to retrieve the rows and the storage engine is processing row by row. These are two levels that are separated by many others.

This is a behaviour by design. It worked this way in 3.23, 4.0, 4.1, 5.0, 5.1, 5.5, 5.6 until today.

But shouldn't using a syntactically correct hint affect these type of statements as it affects SELECT statements to use the index?

Hi,

If you read our latest Reference Manuals, you will see that we are reducing hints and not adding new ones.

Well, this is an existing (and important) hint and I would imagine you'd want to help customers to mitigate locks/deadlocks as much as possible and allow to change the aforementioned behaviour somehow.

Hi,

We discussed it with some Development teams and it seems that a hint like that could have severe side effects.