Description:
Index Merge execution may lead to deadlocks in InnoDB when executing concurrent UPDATE statements. The current behavior suggests that MySQL might be acquiring primary key locks before completing all secondary index scans, which deviates from the expected Index Merge execution flow.
Expected behavior:
1. Index Merge should first scan and acquire locks on all involved secondary indexes
2. Collect and sort rowids in the sort buffer
3. Only then proceed to acquire primary key locks for the final table access
Current behavior:
- Based on deadlock information, MySQL appears to be acquiring primary key locks before completing secondary index scans
- This leads to deadlocks in scenarios where they could be avoided
- Example deadlock scenario:
- Transaction 1: Has locks on secondary index idx_list_id_child_type
- Transaction 2: Has different locks on the same secondary index
- Both transactions then attempt to acquire primary key locks, causing deadlock
Test case:
1. Table has composite indexes (list_id, child_type) and (child_id, child_type)
2. Two concurrent UPDATEs filtering on list_id=21832, different child_ids, and child_type=1
3. Both updates attempt to use Index Merge access path
4. Deadlock occurs between secondary and primary key lock acquisitions
This appears to be an optimization opportunity in the Index Merge execution strategy. A more ordered approach to lock acquisition could prevent these deadlocks while maintaining transaction isolation.
The issue affects high-concurrency workloads where multiple transactions perform updates using Index Merge access paths on overlapping sets of rows.
How to repeat:
IDK，
1.1. Create table structure:
```sql
CREATE TABLE `tg_list_rel` (
`id` bigint(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
`list_id` bigint(11) NOT NULL COMMENT '清单id',
`period_id` int(11) DEFAULT '0' COMMENT '清单周期',
`child_id` bigint(11) NOT NULL COMMENT '目标清单id或者目标id',
`child_type` tinyint(1) DEFAULT '0' COMMENT '子对象类型、1：目标、2：清单',
`goal_group_id` bigint(11) NOT NULL DEFAULT '0' COMMENT '目标分组ID',
`goal_group_sort` int(11) NOT NULL DEFAULT '9999' COMMENT '分组排序',
`approve` tinyint(1) NOT NULL DEFAULT '1' COMMENT '是否校验通过，1：通过 2：不通过',
`weight` int(11) DEFAULT '0' COMMENT '个人清单中目标类型的区分，0：默认值，1：我负责的，2：我贡献的',
`supporter_flag` int(11) DEFAULT '0' COMMENT '个人清单所属人作为支持方的标识',
`sort` int(11) NOT NULL DEFAULT '0' COMMENT '排序权重',
`valid` tinyint(4) DEFAULT '1' COMMENT '删除标识 1：有效 0：已删除或无效',
`create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后更新时间',
`create_uid` int(11) DEFAULT NULL COMMENT '创建者uid',
`create_name` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '创建者姓名',
`create_mis` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '创建者mis',
`create_empid` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '创建者工号id',
`update_uid` int(11) DEFAULT NULL COMMENT '最后更新者uid',
`update_name` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '最后更新者姓名',
`update_mis` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '最后更新者mis',
`update_empid` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '最后更新者工号id',
PRIMARY KEY (`id`),
KEY `idx_list_id_child_type` (`list_id`,`child_type`),
KEY `idx_child` (`child_id`,`child_type`),
KEY `idx_list_id_period` (`list_id`,`period_id`)
) ENGINE=InnoDB AUTO_INCREMENT=418022 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='TG清单关系表';
2.SQL:
-- Transaction 1
UPDATE tg_list_rel
SET valid = 0,
update_time = '2024-12-21 10:24:57.157',
update_uid = *,
update_name = '*',
update_mis = '*',
update_empid = '*'
WHERE list_id = 21832
AND child_id in (81742)
AND child_type = 1
AND valid = 1;
-- Transaction 2 (concurrent)
UPDATE tg_list_rel
SET valid = 0,
update_time = '2024-12-21 10:24:57.157',
update_uid = *,
update_name = '*',
update_mis = '*',
update_empid = '*'
WHERE list_id = 21832
AND child_id in (87569)
AND child_type = 1
AND valid = 1;
3.Deadlock Information
LATEST DETECTED DEADLOCK
------------------------
2024-12-21 10:24:57 0x7eee7eefd700
*** (1) TRANSACTION:
TRANSACTION 24601978667, ACTIVE 0 sec starting index read
mysql tables in use 3, locked 3
LOCK WAIT 7 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 2
MySQL thread id 58252803, OS thread handle 139547230336768, query id 13415112428 10.27.49.189 kms_teamgoals_gtyz updating
/*id:bb3c1a0c*//*ip=10.27.49.189*/update tg_list_rel
SET `valid` = 0,
update_time = '2024-12-21 10:24:57.157',
update_uid = *,
update_name = '*',
update_mis = '*',
update_empid = '*'
WHERE ( list_id = 21832
and child_id in
(
81742
)
and child_type = 1
and `valid` = 1 )
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 10155 page no 10382 n bits 760 index idx_list_id_child_type of table `teamgoals`.`tg_list_rel` trx id 24601978667 lock_mode X waiting
*** (2) TRANSACTION:
TRANSACTION 24601978660, ACTIVE 0 sec fetching rows
mysql tables in use 3, locked 3
27 lock struct(s), heap size 3520, 93 row lock(s), undo log entries 2
MySQL thread id 58177712, OS thread handle 139562796963584, query id 13415112400 10.27.49.189 kms_teamgoals_gtyz updating
/*id:bb3c1a0c*//*ip=10.27.49.189*/update tg_list_rel
SET `valid` = 0,
update_time = '2024-12-21 10:24:57.157',
update_uid = *,
update_name = '*',
update_mis = '*',
update_empid = '*'
WHERE ( list_id = 21832
and child_id in
(
87569
)
and child_type = 1
and `valid` = 1 )
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 10155 page no 10382 n bits 760 index idx_list_id_child_type of table `teamgoals`.`tg_list_rel` trx id 24601978660 lock_mode X
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 10155 page no 14562 n bits 176 index PRIMARY of table `teamgoals`.`tg_list_rel` trx id 24601978660 lock_mode X locks rec but not gap waiting
*** WE ROLL BACK TRANSACTION (1)
Description: Index Merge execution may lead to deadlocks in InnoDB when executing concurrent UPDATE statements. The current behavior suggests that MySQL might be acquiring primary key locks before completing all secondary index scans, which deviates from the expected Index Merge execution flow. Expected behavior: 1. Index Merge should first scan and acquire locks on all involved secondary indexes 2. Collect and sort rowids in the sort buffer 3. Only then proceed to acquire primary key locks for the final table access Current behavior: - Based on deadlock information, MySQL appears to be acquiring primary key locks before completing secondary index scans - This leads to deadlocks in scenarios where they could be avoided - Example deadlock scenario: - Transaction 1: Has locks on secondary index idx_list_id_child_type - Transaction 2: Has different locks on the same secondary index - Both transactions then attempt to acquire primary key locks, causing deadlock Test case: 1. Table has composite indexes (list_id, child_type) and (child_id, child_type) 2. Two concurrent UPDATEs filtering on list_id=21832, different child_ids, and child_type=1 3. Both updates attempt to use Index Merge access path 4. Deadlock occurs between secondary and primary key lock acquisitions This appears to be an optimization opportunity in the Index Merge execution strategy. A more ordered approach to lock acquisition could prevent these deadlocks while maintaining transaction isolation. The issue affects high-concurrency workloads where multiple transactions perform updates using Index Merge access paths on overlapping sets of rows. How to repeat: IDK， 1.1. Create table structure: ```sql CREATE TABLE `tg_list_rel` ( `id` bigint(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键', `list_id` bigint(11) NOT NULL COMMENT '清单id', `period_id` int(11) DEFAULT '0' COMMENT '清单周期', `child_id` bigint(11) NOT NULL COMMENT '目标清单id或者目标id', `child_type` tinyint(1) DEFAULT '0' COMMENT '子对象类型、1：目标、2：清单', `goal_group_id` bigint(11) NOT NULL DEFAULT '0' COMMENT '目标分组ID', `goal_group_sort` int(11) NOT NULL DEFAULT '9999' COMMENT '分组排序', `approve` tinyint(1) NOT NULL DEFAULT '1' COMMENT '是否校验通过，1：通过 2：不通过', `weight` int(11) DEFAULT '0' COMMENT '个人清单中目标类型的区分，0：默认值，1：我负责的，2：我贡献的', `supporter_flag` int(11) DEFAULT '0' COMMENT '个人清单所属人作为支持方的标识', `sort` int(11) NOT NULL DEFAULT '0' COMMENT '排序权重', `valid` tinyint(4) DEFAULT '1' COMMENT '删除标识 1：有效 0：已删除或无效', `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后更新时间', `create_uid` int(11) DEFAULT NULL COMMENT '创建者uid', `create_name` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '创建者姓名', `create_mis` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '创建者mis', `create_empid` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '创建者工号id', `update_uid` int(11) DEFAULT NULL COMMENT '最后更新者uid', `update_name` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '最后更新者姓名', `update_mis` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '最后更新者mis', `update_empid` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '最后更新者工号id', PRIMARY KEY (`id`), KEY `idx_list_id_child_type` (`list_id`,`child_type`), KEY `idx_child` (`child_id`,`child_type`), KEY `idx_list_id_period` (`list_id`,`period_id`) ) ENGINE=InnoDB AUTO_INCREMENT=418022 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='TG清单关系表'; 2.SQL: -- Transaction 1 UPDATE tg_list_rel SET valid = 0, update_time = '2024-12-21 10:24:57.157', update_uid = *, update_name = '*', update_mis = '*', update_empid = '*' WHERE list_id = 21832 AND child_id in (81742) AND child_type = 1 AND valid = 1; -- Transaction 2 (concurrent) UPDATE tg_list_rel SET valid = 0, update_time = '2024-12-21 10:24:57.157', update_uid = *, update_name = '*', update_mis = '*', update_empid = '*' WHERE list_id = 21832 AND child_id in (87569) AND child_type = 1 AND valid = 1; 3.Deadlock Information LATEST DETECTED DEADLOCK ------------------------ 2024-12-21 10:24:57 0x7eee7eefd700 *** (1) TRANSACTION: TRANSACTION 24601978667, ACTIVE 0 sec starting index read mysql tables in use 3, locked 3 LOCK WAIT 7 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 2 MySQL thread id 58252803, OS thread handle 139547230336768, query id 13415112428 10.27.49.189 kms_teamgoals_gtyz updating /*id:bb3c1a0c*//*ip=10.27.49.189*/update tg_list_rel SET `valid` = 0, update_time = '2024-12-21 10:24:57.157', update_uid = *, update_name = '*', update_mis = '*', update_empid = '*' WHERE ( list_id = 21832 and child_id in ( 81742 ) and child_type = 1 and `valid` = 1 ) *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 10155 page no 10382 n bits 760 index idx_list_id_child_type of table `teamgoals`.`tg_list_rel` trx id 24601978667 lock_mode X waiting *** (2) TRANSACTION: TRANSACTION 24601978660, ACTIVE 0 sec fetching rows mysql tables in use 3, locked 3 27 lock struct(s), heap size 3520, 93 row lock(s), undo log entries 2 MySQL thread id 58177712, OS thread handle 139562796963584, query id 13415112400 10.27.49.189 kms_teamgoals_gtyz updating /*id:bb3c1a0c*//*ip=10.27.49.189*/update tg_list_rel SET `valid` = 0, update_time = '2024-12-21 10:24:57.157', update_uid = *, update_name = '*', update_mis = '*', update_empid = '*' WHERE ( list_id = 21832 and child_id in ( 87569 ) and child_type = 1 and `valid` = 1 ) *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 10155 page no 10382 n bits 760 index idx_list_id_child_type of table `teamgoals`.`tg_list_rel` trx id 24601978660 lock_mode X *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 10155 page no 14562 n bits 176 index PRIMARY of table `teamgoals`.`tg_list_rel` trx id 24601978660 lock_mode X locks rec but not gap waiting *** WE ROLL BACK TRANSACTION (1)