mysql的错误代码4999_MySQL update 执行计划使用 index_merge 导致死锁

MySQL死锁的原因有很多，总的来说，还是因为加锁的顺序不一致，下面来看一个index_merge导致的死锁。

死锁案例：

MySQL版本：5.7.19

隔离级别：提交读(RC)

先看表结构，如下：

CREATE TABLE `t` (

`id` bigint(20) NOT NULL AUTO_INCREMENT,

`c1` varchar(50) DEFAULT NULL,

`c2` varchar(50) DEFAULT NULL,

`ts` datetime DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `idx_c1` (`c1`),

KEY `idx_c2` (`c2`),

KEY `idx_ts` (`ts`)

) ENGINE=InnoDB AUTO_INCREMENT=3279 DEFAULT CHARSET=utf8mb4

主键id，字段c1,c2,ts 都建有二级索引。

看两个SQL：

update t set ts=now() where c1=’100′ and c2=’100′;

update t set ts=now() where c1=’100′ and c2=’200′;

这两个SQL在多个并发同时执行的情况下，很高的概率会发生死锁。死锁信息如下：

------------------------

LATEST DETECTED DEADLOCK

------------------------

2020-01-18 11:26:16 0x7f375216a700

*** (1) TRANSACTION:

TRANSACTION 24049054, ACTIVE 0 sec starting index read

mysql tables in use 3, locked 3

LOCK WAIT 5 lock struct(s), heap size 1136, 4 row lock(s)

MySQL thread id 1021, OS thread handle 139876759136000, query id 6147815 10.49.2.92 sndsadmin updating

update db.t set ts=now() where c1='100' and c2='200'

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 23 page no 29 n bits 104 index PRIMARY of table `db`.`t` trx id 24049054 lock_mode X locks rec but not gap waiting

*** (2) TRANSACTION:

TRANSACTION 24049053, ACTIVE 0 sec starting index read, thread declared inside InnoDB 4999

mysql tables in use 3, locked 3

4 lock struct(s), heap size 1136, 3 row lock(s)

MySQL thread id 244, OS thread handle 139875577145088, query id 6147814 10.49.2.92 sndsadmin updating

update db.t set ts=now() where c1='100' and c2='100'

*** (2) HOLDS THE LOCK(S):

RECORD LOCKS space id 23 page no 29 n bits 104 index PRIMARY of table `db`.`t` trx id 24049053 lock_mode X locks rec but not gap

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 23 page no 12 n bits 952 index idx_c1 of table `db`.`t` trx id 24049053 lock_mode X locks rec but not gap waiting

*** WE ROLL BACK TRANSACTION (2)

看下表中数据分布，表t中一共有2181条记录，c1值为100的有4条记录，c2值为100的有2条记录，c2值为200的有3条记录。如下：

mysql> select count(*) from t;

+----------+

| count(*) |

+----------+

|     2181 |

+----------+

mysql> select * from t where c1='100';

+------+------+------+---------------------+

| id | c1 | c2 | ts |

+------+------+------+---------------------+

| 3274 | 100 | 100 | 2020-01-18 11:26:16 |

| 3275 | 100 | 200 | 2020-01-18 11:29:53 |

| 3277 | 100 | 200 | 2020-01-18 11:29:53 |

| 3278 | 100 | 100 | 2020-01-18 11:26:16 |

+------+------+------+---------------------+

4 rows in set (0.00 sec)

mysql> select * from t where c2='100';

+------+------+------+---------------------+

| id | c1 | c2 | ts |

+------+------+------+---------------------+

| 3274 | 100 | 100 | 2020-01-18 11:26:16 |

| 3278 | 100 | 100 | 2020-01-18 11:26:16 |

+------+------+------+---------------------+

2 rows in set (0.00 sec)

mysql> select * from t where c2='200';

+------+------+------+---------------------+

| id | c1 | c2 | ts |

+------+------+------+---------------------+

| 3275 | 100 | 200 | 2020-01-18 11:30:07 |

| 3276 | 200 | 200 | NULL |

| 3277 | 100 | 200 | 2020-01-18 11:30:07 |

+------+------+------+---------------------+

3 rows in set (0.00 sec)

c1,c2字段上分别建有非唯一的二级索引，c1,c2有值相同的情况。查看上述两个SQL的执行计划，发现都使用了index_merge，在Extra列使用了Using intersect(idx_c2,idx_c1);

explain update t set ts=now() where c1=’100′ and c2=’100′;

explain update t set ts=now() where c1=’100′ and c2=’200′;

执行计划如下：

mysql> explain update t set ts=now() where c1='100' and c2='100';

+----+-------------+-------+------------+-------------+---------------+---------------+---------+------+------+----------+---------------------------------------------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+-------+------------+-------------+---------------+---------------+---------+------+------+----------+---------------------------------------------+

| 1 | UPDATE | t | NULL | index_merge | idx_c1,idx_c2 | idx_c2,idx_c1 | 203,203 | NULL | 1 | 100.00 | Using intersect(idx_c2,idx_c1); Using where |

+----+-------------+-------+------------+-------------+---------------+---------------+---------+------+------+----------+---------------------------------------------+

死锁分析：

本次死锁案例主要由index_merge导致，由于表中索引不合理，MySQL优化器选择了索引合并，即根据where条件分别走idx_c1, idx_c2两个索引，再将结果进行合并。update操作，走了两个二级索引，根据加锁顺序，除了对二级索引加锁外，还要对主键索引进行加锁，多并发执行条件下，获取二级索引的行锁与获取主键索引的行锁不再有顺，最终导致死锁发生。

解决方案：

增加联合索引 idx_c1_c2(c1,c2)，优化器直接使用联合索引，不再使用index_merge，加锁顺序一致，不再出现死锁。

SQL语句中强制走某个索引，比如 idx_c1索引的区分度已经非常好了，那么使用 force index(idx_c1)，让优化器强制走idx_c1，也不再会出现死锁。

关掉参数index_merge_intersection=off，禁用index_merge功能。