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

解决方案:

  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功能。

发表评论