MySQL update use index merge(Using intersect) increase chances for deadlock

昨天一同事发现线上系统在并发更新的时候出现了死锁，通过排查定位于update更新使用了两个索引导致，死锁信息如下：

*** (1) transaction:

transaction 29285454235, active 0.001 sec fetching rows

mysql tables in use 3, locked 3

lock wait 6 lock struct(s), heap size 1184, 4 row lock(s)

mysql thread id 6641616, os thread handle 0x2b165c4b1700, query id 28190427937 10.103.180.86 test_ebs searching rows for update

update test set is_deleted = 1 where group_id = 1332577 and test_id = 4580605

*** (1) waiting for this lock to be granted:

record locks space id 132 page no 37122 n bits 352 index `primary` of table `testdriver`.`test` trx id 29285454235 lock_mode x locks rec but not gap waiting

record lock, heap no 179 physical record: n_fields 8; compact format; info bits 0

*** (2) transaction:

transaction 29285454237, active 0.001 sec fetching rows, thread declared inside innodb 4980

5 lock struct(s), heap size 1184, 3 row lock(s)

mysql thread id 6639213, os thread handle 0x2b1694cc2700, query id 28190427939 10.103.180.113 test_ebs searching rows for update

update test set is_deleted = 1 where group_id = 1332577 and test_id = 4212859

*** (2) holds the lock(s):

record locks space id 132 page no 37122 n bits 352 index `primary` of table `testdriver`.`test` trx id 29285454237 lock_mode x locks rec but not gap

record lock, heap no 179 physical record: n_fields 8; compact format;

表结构：

create table `test` (

`id` bigint(20) not null auto_increment comment ‘主键’,

`test_id` bigint(20) default null,

`group_id` bigint(20) default null comment ‘id，对应test_group.id’,

`gmt_created` datetime default null comment ‘创建时间’,

`gmt_modified` datetime default null comment ‘修改时间’,

`is_deleted` tinyint(4) default ‘0’ comment ‘删除。’,

primary key (`id`),

key `idx_testid` (`test_id`),

key `idx_groupid` (`group_id`)

) engine=innodb auto_increment=7429111 ;

sql执行计划：

mysql>explain update test set is_deleted = 1 where group_id = 1332577 and test_id = 4212859

+—-+————-+—————+————-+———————–+———————–+———+—–+——+———

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra |

| 1 | simple | test | index_merge | idx_testid,idx_groupid | idx_testid,idx_groupid | 9,9 | | 1 | using intersect(idx_testid,idx_groupid); using where; using temporary |

所以第一个事务先根据group_id索引，已经锁住primary id，然后再根据test_id索引，锁定primary id；

第二个事务先根据test_id索引，已经锁住primary id，然后再根据group_id索引，去锁primary id；

所以这样并发更新就可能出现死索引。

mysql官方也已经确认了此bug：https://bugs.mysql.com/bug.php?id=77209

解决方法有两种：

第一、添加test_id+group_id的组合索引，这样就可以避免掉index merge；

第二、将优化器的index merge优化关闭；

建议选择第一种方法来避免此问题的发生。