问题描述
告警信息突然出现一些数据库异常的告警信息,查看后发现包含如下关键字,发现是mysql 发生了死锁导致事务失败。
org.springframework.dao.DeadlockLoserDataAccessException:
### Error updating database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
原因分析:
查看相关 sql 后发现就是一个条简单的 update 语句,建表语句如下:
CREATE TABLE `table_a` (
`id` BIGINT ( 64 ) NOT NULL,
`table_b_id` BIGINT ( 64 ) NOT NULL,
`a` VARCHAR ( 8 ) NOT NULL,
`b` INT ( 8 ) DEFAULT NULL,
PRIMARY KEY ( `id` ) USING BTREE,
KEY `index_b_id` ( `table_b_id` ) USING BTREE,
KEY `index_a` ( `a` ) USING BTREE
) ENGINE = INNODB DEFAULT CHARSET = utf8;
相关的死锁语句如下:
UPDATE table_a
SET b =?
WHERE
(
table_b_id = ?
AND a = ?
)
业务场景就是有几个如上的 sql 语句几乎并发执行,table_b_id的值相同,字段 a 的值不同。
执行explain 之后发现 type为index_merge,key 为index_b_id、index_a,exrta 列为Using intersect(index_b_id,index_a)。
看到 index_merge 比较陌生,不是很常见。通过官网找到了对 index_merge的描述:
index_merge 通过多次范围扫描检索并将其合并成一个结果。此访问方法仅合并单个表的索引扫描,而不是跨多个表扫描。合并可以产生其基础扫描的并集、交集或交集的并集。
简单来说就是 index_merge 会通过扫描一个表的多个索引,并根据扫描结果获取满足条件的数据,以此优化sql 性能。这是 mysql 在 5.1 版本增加的优化方法,默认是打开的。可以通过以下语句查看该优化方法是否开启,以及开启或关闭该优化。
SELECT @@optimizer_switch ;
SET optimizer_switch='index_merge=off ;
index_merge包含的算法有
- Using intersect(...)
- Using union(...)
- Using sort_union(...)
在 explain方法中的 exrta 字段可以体现。所以我们这次案例中用到的算法是Using intersect,也就是说通过扫描ndex_b_id、index_a两个索引,对满足条件的结果取交集。
以 UPDATE table_a SET b =1 WHERE table_b_id = 100 AND a = 200 为例,执行顺序如下:
- 扫描二级索引index_a,找到满足a=200 的数据
- 扫描二级索引index_b_id,找到table_b_id=100 的数据
- 讲两次扫描结果根据主键 id 是否相同取交集
- 根据取交集得到的主键 id 回到聚簇索引找到相关数据
如果不采用 index_merge的话,执行顺序如下:
- 扫描二级索引index_a,找到满足a=200 的数据
- 根据主键 id 到聚簇索引找到行数据
- 根据table_b_id = 100条件过滤数据
当优化器认为采用 index_merge效率更优时,就会自动采用。
死锁原因
InnoDB 存储引擎的行级锁锁的是索引,而不是行数据,如果 sql 语句用到了主键索引,mysql 会锁住主键索引,如果语句用到了二级索引,mysql 会先锁住二级索引,在锁住主键索引。
举例死锁是因为以下两个事务引起的:
事务一
UPDATE table_a SET b =1 WHERE table_b_id = 100 AND a = 200
事务二
UPDATE table_a SET b =1 WHERE table_b_id = 200 AND a = 200
表数据如下
事务一 | 事务二 |
---|---|
锁住index_b_id中等于 100 的索引项 | |
锁住index_b_id中等于 200 的索引项 | |
回表锁住主键索引中id=10001的索引项 | |
回表锁住主键索引中id=10002的索引项 | |
锁住index_a中等于 100 的索引项 | |
尝试锁住 index_a 中等于 100 的索引项,发现已被加锁,等待事务一释放锁 | |
尝试回表锁住主键索引中 id=10001、id=10002 的索引项,发现 10002 已被加锁,等待事务2️⃣释放锁 | |
死锁 |
解决方案:
-
关闭 index_merge优化项,对整个库有影响
-
添加或将原索引修改成table_b_id和a两个字段的组合索引,只需扫描一个索引,避免 index_merge。
这次案例本身就是因为索引设计不合理导致的,最终我们采用将index_a改成 a 和table_b_id的组合索引,避免了死锁。
死锁场景有很多,在日常开发中可以通过一些方法尽量避免死锁
- 合理设计索引,索引字段要尽量包含常作为检索条件的字段
- 确保不同事务以相同的顺序你对索引加锁
- 避免大事务,锁的释放是在事务结束时,小事务对锁的占用时间短
- 尽量避免 index_merge,因为不能保证加锁顺序相同