【案例分析】mysql index_merge导致的死锁分析

lcbfree

于 2023-11-23 16:39:07 发布

阅读量184

点赞数 4

分类专栏： mysql专栏文章标签： mysql 数据库

本文链接：https://blog.csdn.net/lcbfree/article/details/134482456

版权

mysql专栏专栏收录该内容

2 篇文章 0 订阅

订阅专栏

在这里插入图片描述

问题描述

告警信息突然出现一些数据库异常的告警信息，查看后发现包含如下关键字，发现是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️⃣释放锁
死锁