MySQL update 执行计划使用 index_merge 导致死锁

MySQL, 数据库 / 作者：

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操作，走了两个二级索引，根据加锁顺序，除了对二级索引加锁外，还要对主键索引进行加锁，多并发执行条件下，获取二级索引的行锁与获取主键索引的行锁不再有顺，最终导致死锁发生。

解决方案：

  1. 增加联合索引 idx_c1_c2(c1,c2)，优化器直接使用联合索引，不再使用index_merge，加锁顺序一致，不再出现死锁。
  2. SQL语句中强制走某个索引，比如 idx_c1索引的区分度已经非常好了，那么使用 force index(idx_c1)，让优化器强制走idx_c1，也不再会出现死锁。
  3. 关掉参数index_merge_intersection=off，禁用index_merge功能。

发表评论