Bug #111255 unnecessary locks cannot be released when use index_merge
Submitted: 2 Jun 2023 7:48 Modified: 5 Jun 2023 1:16
Reporter: wei lee Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:8.0.33 OS:CentOS
Assigned to: CPU Architecture:Any

[2 Jun 2023 7:48] wei lee 
Description:
when use index_merge optimizer there will be hold unnecessary row locks,increases the probability of deadlock or blocking

How to repeat:

CREATE TABLE `testsemi` (
  `id1` int NOT NULL,
  `id2` int NOT NULL,
  `name` varchar(10) DEFAULT NULL,
  `z` varchar(10) DEFAULT NULL,
  `dd` date DEFAULT NULL,
  PRIMARY KEY (`id1`,`id2`),
  KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=uontf8mb4_0900_ai_ci
；

set transaction_isolation='READ-COMMITTED'
set optimizer_switch='index_merge=on';

insert into testsemi(id1,id2,dd) values(1,1,'2023-05-31');

run next sql 15 times:
insert into testsemi(id1,id2,dd) select id1,id2+(select count(*) from testsemi),dd from testsemi;

update testsemi set name='abc' limit 10;
update testsemi set dd='2023-05-30' limit 5;

select * from testsemi limit 20;
+-----+-----+------+------+------------+
| id1 | id2 | name | z    | dd         |
+-----+-----+------+------+------------+
|   1 |   1 | abc  | NULL | 2023-05-30 |
|   1 |   2 | abc  | NULL | 2023-05-30 |
|   1 |   3 | abc  | NULL | 2023-05-30 |
|   1 |   4 | abc  | NULL | 2023-05-30 |
|   1 |   5 | abc  | NULL | 2023-05-30 |
|   1 |   6 | abc  | NULL | 2023-05-31 |
|   1 |   7 | abc  | NULL | 2023-05-31 |
|   1 |   8 | abc  | NULL | 2023-05-31 |
|   1 |   9 | abc  | NULL | 2023-05-31 |
|   1 |  10 | abc  | NULL | 2023-05-31 |
|   1 |  11 | NULL | NULL | 2023-05-31 |
|   1 |  12 | NULL | NULL | 2023-05-31 |
|   1 |  13 | NULL | NULL | 2023-05-31 |
|   1 |  14 | NULL | NULL | 2023-05-31 |
|   1 |  15 | NULL | NULL | 2023-05-31 |
|   1 |  16 | NULL | NULL | 2023-05-31 |
|   1 |  17 | NULL | NULL | 2023-05-31 |
|   1 |  18 | NULL | NULL | 2023-05-31 |
|   1 |  19 | NULL | NULL | 2023-05-31 |
|   1 |  20 | NULL | NULL | 2023-05-31 |
+-----+-----+------+------+------------+
20 rows in set (0.00 sec)

explain update testsemi set z='test' where id1=1 and name='abc' and dd='2023-05-31';
+----+-------------+----------+------------+-------------+---------------+--------------+---------+------+------+----------+--------------------------------------------+
| id | select_type | table    | partitions | type        | possible_keys | key          | key_len | ref  | rows | filtered | Extra                                      |
+----+-------------+----------+------------+-------------+---------------+--------------+---------+------+------+----------+--------------------------------------------+
|  1 | UPDATE      | testsemi | NULL       | index_merge | PRIMARY,name  | name,PRIMARY | 47,4    | NULL |    5 |   100.00 | Using intersect(name,PRIMARY); Using where |
+----+-------------+----------+------------+-------------+---------------+--------------+---------+------+------+----------+--------------------------------------------+
1 row in set, 1 warning (0.00 sec)

begin;
update testsemi set z='test' where id1=1 and nam='abc' and dd='2023-05-31'

expect for 10 row locks(id2 between 6 and 10 should be release),but there is hold 20 row locks

select * from performance_schema.data_locks;

--------+---------------+-------------+--------------+
| ENGINE | ENGINE_LOCK_ID                          | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA    |
+--------+-----------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+--------------+
| INNODB | 139875127880920:1069:139875139505856    |                  1519 |       370 |      161 | test          | zz1         | NULL           | NULL              | NULL       |       139875139505856 | TABLE     | IX            | GRANTED     | NULL         |
| INNODB | 139875127880920:7:194:2:139875139502800 |                  1519 |       370 |      161 | test          | zz1         | NULL           | NULL              | name       |       139875139502800 | RECORD    | X,REC_NOT_GAP | GRANTED     | 'abc', 1, 1  |
| INNODB | 139875127880920:7:6:2:139875139503144   |                  1519 |       370 |      161 | test          | zz1         | NULL           | NULL              | PRIMARY    |       139875139503144 | RECORD    | X,REC_NOT_GAP | GRANTED     | 1, 1         |
| INNODB | 139875127880920:7:6:3:139875139503144   |                  1519 |       370 |      161 | test          | zz1         | NULL           | NULL              | PRIMARY    |       139875139503144 | RECORD    | X,REC_NOT_GAP | GRANTED     | 1, 2         |
| INNODB | 139875127880920:7:6:4:139875139503144   |                  1519 |       370 |      161 | test          | zz1         | NULL           | NULL              | PRIMARY    |       139875139503144 | RECORD    | X,REC_NOT_GAP | GRANTED     | 1, 3         |
| INNODB | 139875127880920:7:6:5:139875139503144   |                  1519 |       370 |      161 | test          | zz1         | NULL           | NULL              | PRIMARY    |       139875139503144 | RECORD    | X,REC_NOT_GAP | GRANTED     | 1, 4         |
| INNODB | 139875127880920:7:6:6:139875139503144   |                  1519 |       370 |      161 | test          | zz1         | NULL           | NULL              | PRIMARY    |       139875139503144 | RECORD    | X,REC_NOT_GAP | GRANTED     | 1, 5         |
| INNODB | 139875127880920:7:6:7:139875139503144   |                  1519 |       370 |      161 | test          | zz1         | NULL           | NULL              | PRIMARY    |       139875139503144 | RECORD    | X,REC_NOT_GAP | GRANTED     | 1, 6         |
| INNODB | 139875127880920:7:6:8:139875139503144   |                  1519 |       370 |      161 | test          | zz1         | NULL           | NULL              | PRIMARY    |       139875139503144 | RECORD    | X,REC_NOT_GAP | GRANTED     | 1, 7         |
| INNODB | 139875127880920:7:6:9:139875139503144   |                  1519 |       370 |      161 | test          | zz1         | NULL           | NULL              | PRIMARY    |       139875139503144 | RECORD    | X,REC_NOT_GAP | GRANTED     | 1, 8         |
| INNODB | 139875127880920:7:6:10:139875139503144  |                  1519 |       370 |      161 | test          | zz1         | NULL           | NULL              | PRIMARY    |       139875139503144 | RECORD    | X,REC_NOT_GAP | GRANTED     | 1, 9         |
| INNODB | 139875127880920:7:6:11:139875139503144  |                  1519 |       370 |      161 | test          | zz1         | NULL           | NULL              | PRIMARY    |       139875139503144 | RECORD    | X,REC_NOT_GAP | GRANTED     | 1, 10        |
| INNODB | 139875127880920:7:12:2:139875139503488  |                  1519 |       370 |      161 | test          | zz1         | NULL           | NULL              | name       |       139875139503488 | RECORD    | X,REC_NOT_GAP | GRANTED     | 'abc', 1, 2  |
| INNODB | 139875127880920:7:12:3:139875139503488  |                  1519 |       370 |      161 | test          | zz1         | NULL           | NULL              | name       |       139875139503488 | RECORD    | X,REC_NOT_GAP | GRANTED     | 'abc', 1, 3  |
| INNODB | 139875127880920:7:12:4:139875139503488  |                  1519 |       370 |      161 | test          | zz1         | NULL           | NULL              | name       |       139875139503488 | RECORD    | X,REC_NOT_GAP | GRANTED     | 'abc', 1, 4  |
| INNODB | 139875127880920:7:12:5:139875139503488  |                  1519 |       370 |      161 | test          | zz1         | NULL           | NULL              | name       |       139875139503488 | RECORD    | X,REC_NOT_GAP | GRANTED     | 'abc', 1, 5  |
| INNODB | 139875127880920:7:12:6:139875139503488  |                  1519 |       370 |      161 | test          | zz1         | NULL           | NULL              | name       |       139875139503488 | RECORD    | X,REC_NOT_GAP | GRANTED     | 'abc', 1, 6  |
| INNODB | 139875127880920:7:12:7:139875139503488  |                  1519 |       370 |      161 | test          | zz1         | NULL           | NULL              | name       |       139875139503488 | RECORD    | X,REC_NOT_GAP | GRANTED     | 'abc', 1, 7  |
| INNODB | 139875127880920:7:12:8:139875139503488  |                  1519 |       370 |      161 | test          | zz1         | NULL           | NULL              | name       |       139875139503488 | RECORD    | X,REC_NOT_GAP | GRANTED     | 'abc', 1, 8  |
| INNODB | 139875127880920:7:12:9:139875139503488  |                  1519 |       370 |      161 | test          | zz1         | NULL           | NULL              | name       |       139875139503488 | RECORD    | X,REC_NOT_GAP | GRANTED     | 'abc', 1, 9  |
| INNODB | 139875127880920:7:12:10:139875139503488 |                  1519 |       370 |      161 | test          | zz1         | NULL           | NULL              | name       |       139875139503488 | RECORD    | X,REC_NOT_GAP | GRANTED     | 'abc', 1, 10 |
+--------+-----------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+--------------+
21 rows in set (0.00 sec)

if  set optimizer_switch='index_merge=off' or force index (name)， the returned result will be 10 row locks  as expected.

begin;
update testsemi set z='test' where id1=1 and name='abc' and dd='2023-05-31'

or 

update testsemi force index(name) set z='test' where id1=1 and name='abc' and dd='2023-05-31'

select * from performance_schema.data_locks;

Suggested fix:
release unnecessary locks when use index_merge
[2 Jun 2023 12:03] MySQL Verification Team 
Hi Mr. lee,

Thank you for your bug report.

However, this is simply not a bug, but expected behaviour.

When you do merge index, optimiser has to read more rows and to hold them locked during the entire merge process, as long as these rows are needed. Without index merge, there is no need to hold locks on all these rows in the merge buffer, at all. 

Hence, this is a cause of the difference in the number of locks held.

Index merge has the upside of the gained speed (in most cases), but the downside of more records being held in process, hence resulting in more locks.

This is expected behaviour.
[5 Jun 2023 1:16] wei lee 
hi,MySQL Verification Team 
  Thank you for your answer, but in my opinion, this is one point that can be optimized in the future version.
[6 Jun 2023 12:00] MySQL Verification Team 
Thank you Mr. lee,

The list of all Optimiser improvements is still open (internally) for the additions for 8.1 ....... It will be some time before 8.1 is released.