头图

【网易云商】记一次实遇的 MySQL--index merge 死锁历程

在实际业务开发过程中,MySQL 会由于许多原因造成死锁,本文就是基于网易云商的一次实遇死锁经验,讲述了一次由于 Index Merge 优化导致的死锁案例,并分享处理本次死锁所运用的方法。死锁的本质原因还是由加锁导致,因此创建索引时要结合实际业务场景分析。

背景

某一天中午,我们收到了报警信息,出现关键词 Deadlock。


nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction

显然出现了数据库死锁。一开始我们以为是数据库操作顺序不一致引发的,但是经分析相关业务和 SQL 后并未发现不一致的场景,场面一度陷入僵局,没办法只能寻根究底从 MySQL 的索引和加锁机制入手了。

先来看下 MySQL 的加锁机制。

MySQL 的加锁机制

MySQL 索引分为主键索引(或聚簇索引)和二级索引(或非主键索引、非聚簇索引,包括各种主键索引外的其他索引)。不同存储引擎对于数据的组织方式略有不同,本文以 InnoDB 为例展开,MySQL 版本 5.7。

主键索引和数据是存放在一起的,构成一棵 B+ 树,主键位于非叶子节点,数据存放于叶子节点。示意图如下:


图1:图源网络

二级索引列位于非叶子节点,主键值位于叶子节点,示意图如下:


图2:图源网络

以 select * from table where name='ccc' 为例,MySQL 对 SQL 进行解析后发现 name 字段有索引可用,于是先在二级索引(图2)上根据 name='ccc' 找到主键 id=27,然后根据主键 27 到主键索引上(图1)上找到需要的记录。这个过程也被称作回表。

接下来言归正传看下 MySQL 到底是如何给索引加锁的(本例中隔离级别为 RC)。具体分以下 3 个场景分析:

* 根据主键进行更新

update table set name='cjy' where id=27;只需要将主键上 id=27 的记录加上互斥锁即可(加锁后本事务可以读和写,其他事务读和写会被阻塞)。如下:

  • 根据唯一索引进行更新

update table set name='cjy' where name='ccc',InnoDB 现在唯一索引 name 上找到 name='ccc' 的索引项(ccc,27)并加上加上互斥锁,然后根据 id=27 再到主键索引上找到对应的叶子节点并加上互斥锁。

一共两把锁,一把加在唯一索引上,一把加在主键索引上。这里需要说明的是加锁是一步步加的,这种分步加锁的机制就是后文死锁的导火索。示意图如下:

  • 根据非唯一索引进行更新
    update table set name='cjy' where name='ccc'。如果 name 不唯一,和上面唯一索引加锁相似,不同的是会给所有符合条件的索引加锁。如下:

这里有四把锁,加锁步骤如下:

  • 在非唯一索引(name)上找到(ccc,27)的索引项,加上互斥锁;
  • 根据(ccc,27)找到主键索引的(27,ccc)记录,加互斥锁;
  • 在非唯一索引(name)上找到(ccc,29)的索引项,加上互斥锁;
  • 根据(ccc,29)找到主键索引的(29,ccc)记录,加互斥锁;

从上面步骤可以看出,InnoDB 对于每个符合条件的记录是分步逐条加锁的。

再来看下 MySQL 的事务机制。

MySQL 事务机制

事务的四个特性 ACID

原子性(Atomicity):指事务是一个不可分割的最小工作单位,事务中的操作只有都发生和都不发生两种情况。

一致性(Consistency):事务必须使数据库从一个一致状态变换到另外一个一致状态,举个例子,李二给王五转账 50 元,其事务就是让李二账户上减去 50 元,王五账户上加上 50 元;一致性是指其他事务看到的情况是要么李二还没有给王五转账的状态,要么王五已经成功接收到李二的 50 元转账。而对于李二少了 50 元,王五还没加上 50 元这个中间状态是不可见的。

隔离性(Isolation):一个事务的执行不能被其他事务干扰,即一个事务内部的操作及使用的数据对并发的其他事务是隔离的,并发执行的各个事务之间不能互相干扰。

持久性(Durability):一个事务一旦提交成功,它对数据库中数据的改变将是永久性的,接下来的其他操作或故障不应对其有任何影响。

事务的隔离等级

MySQL 中的四种事务隔离级别分别如下:

  • read uncommitted(读未提交数据):允许事务读取未被其他事务提交的变更。(脏读、不可重复读和幻读的问题都会出现)。
  • read committed(读已提交数据):只允许事务读取已经被其他事务提交的变更。(可以避免脏读,但不可重复读和幻读的问题仍然可能出现)。
  • repeatable read(可重复读):确保事务可以多次从一个字段中读取相同的值,在这个事务持续期间,禁止其他事务对这个字段进行更新(update)。(可以避免脏读和不可重复读,但幻读仍然存在)。
  • serializable(串行化):确保事务可以从一个表中读取相同的行,在这个事务持续期间,禁止其他事务对该表执行插入、更新和删除操作,所有并发问题都可避免,但性能十分低下。

其中 RR 实现可重复读的原理是:MVC 多版本并发控制。其实就是版本号概念。读取数据时大于当前事务版本号的记录不管。

同时 RR 中会使用到间隙锁。间隙锁是采用在指定记录的前面和后面以及中间的间隙上加间隙锁的方式避免数据被插入,从而解决幻读的问题。

死锁原因分析

基于上面讲过的知识,下面就来看看本次死锁的具体原因是什么。

本次死锁发生的场景是给一批客户手机号发短信后更新记录时触发的。废话不多说,先上表结构:

(基于数据敏感和方便理解考虑,去除了和本次死锁无关的字段)。

CREATE TABLE `phone_send_record` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `task_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '发送批次id',
  `phone` varchar(256) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '手机号',
  `status` smallint(4) NOT NULL DEFAULT '0' COMMENT '发送状态 0未发送 1发送成功 2 发送失败',
  PRIMARY KEY (`id`),
  KEY `idx_taskId` (`task_id`) USING BTREE,
  KEY `idx_phone` (`phone`) USING BTREE
) ENGINE = InnoDB  DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = '短信发送记录表'

业务场景就是:创建一个短信发送任务(task_id),一个任务里包含了一批手机号,因此数据库短信发送记录表中会有 n 行记录:phone 不同,task_id 相同。更新 SQL 如下:

update phone_send_record set status = #{status} where phone = #{phone} and task_id = #{taskId}

心路历程

我们业务上做了手机号去重,因此理论上同一个发送任务里是不会存在相同手机号的,同时发生死锁的2行记录显示的也是不同的手机号(见下文 sql)。

按道理讲,innoDB 触发行锁,没有锁表,不同行不应该影响才对,就算锁多行那也是资源竞争等待不应该触发死锁。我们带着疑问继续往下看。

接着我们考虑了间隙锁的可能,但是间隙锁是在 RR 隔离级别下才存在,查了下我们数据库的隔离级别是 RC,所以间隙锁的可能也被排除了。

接着查看了 SQL 执行计划,发现使用了 index_merge。index_merge 是 MySQL 5.1 后引入的一项索引合并优化技术,它允许对同一个表同时使用多个索引进行查询,并对多个索引的查询结果进行合并后返回。

回到我们的 SQL,经过分析日志找出发生死锁的是如下 2 条:

update phone_send_record set  status = 0 where phone = '13555111111' and task_id = 123;
update phone_send_record set  status = 0 where phone = '13555222222' and task_id = 123;

如果没有 index_merge,要么走 idx_taskId 索引,要么走 idx_phone 索引,不会出现两个索引一起使用的情况。而在使用 index_merge 技术后,会同时执行两个索引,分别查到结果后再进行合并。再结合上文对加锁机制的理解,两个索引的同时加锁就可能导致死锁。图析如下:

上图只是其中一种造成死锁的可能路径,事实上还有其他可能(大家可以自行发挥想象)。

下表是对上图做的简要解析:

 事务 1 等待事务 2 释放锁,事务 2 等待事务 1 释放锁,这样就造成了死锁。

解决方法

  1. 既然是 index_merge 导致的,那理所当然的想就是关闭 index_merge。的确这能解决上述的死锁问题,但是一般情况下,数据量越大 index_merge 开启的优化效果会越明显,所以没有特别要求不建议关闭(默认是开启的)。因此此方法不推荐。
  2. 使用主键索引进行更新。先根据二级索引查询出主键 id,再拿主键 id 进行 update。这样更新的范围是唯一的,自然不会触发死锁。
  3. 删除多余的独立索引,创建联合索引。本栗就是 KEY idx_t_p (task_id,phone)。这个就提醒我们在加索引字段时(尤其是往旧表加)需要仔细分析下业务场景,来决定是创建独立索引还是联合索引。

小结:方法 2 是从代码层面入手从根本上规避死锁,但是会增加一定的代码量;方法 3 是巧用联合索引实现 2 个独立索引的功能又不会触发 index_merge。推荐方法 2,但方法 3 更方便些,原则上方法 2 和 3 都有效。

总结

本文仅描述了由于 index_merge 优化导致的死锁,讲述了死锁产生的原因以及解决办法,并顺便介绍了 MySQL 加锁机制。事实上死锁的原因还有很多,比如操作数据顺序不一致、长事务等,就不一一展开了。


网易云信技术小站
聊一聊网易云信在技术实践和架构优化上的那些事~

欢迎关注网易云信 GitHub:

608 声望
134 粉丝
0 条评论
推荐阅读
踢碎破局陷阱,来一场酣畅淋漓的 SLG!
SLG 作为深度策略游戏品类的代表,一直是展现人类智慧之光的最佳舞台之一。欣赏顶尖玩家间的对决是一场大脑的狂欢,奇谋频出、神机莫测般的对局让人不禁摩拳擦掌,跃跃欲试。但当我们打开游戏时,往往是高玩难觅...

网易智企阅读 192

封面图
花了几个月时间把 MySQL 重新巩固了一遍,梳理了一篇几万字 “超硬核” 的保姆式学习教程!(持续更新中~)
MySQL 是最流行的关系型数据库管理系统,在 WEB 应用方面 MySQL 是最好的 RDBMS(Relational Database Management System:关系数据库管理系统)应用软件之一。

民工哥14阅读 2k

封面图
硬卷完了!MongoDB 打怪升级进阶成神之路( 2023 最新版 )!
前面我们学习:MySQL 打怪升级进阶成神之路、Redis 打怪升级进阶成神之路,然后我们还在继续 NoSQL 的卷王之路。从第一篇文章开始,我们逐步详细介绍了 MogoDB 基础概念、安装和最基本的CURD操作、索引和聚合、工...

民工哥7阅读 704

封面图
初学后端,如何做好表结构设计?
这篇文章介绍了设计数据库表结构应该考虑的4个方面,还有优雅设计的6个原则,举了一个例子分享了我的设计思路,为了提高性能我们也要从多方面考虑缓存问题。

王中阳Go4阅读 1.8k评论 2

封面图
又一款内存数据库横空出世,比 Redis 更强,性能直接飙升一倍!杀疯了
KeyDB是Redis的高性能分支,专注于多线程,内存效率和高吞吐量。除了多线程之外,KeyDB还具有仅在Redis Enterprise中可用的功能,例如Active Replication,FLASH存储支持以及一些根本不可用的功能,例如直接备份...

民工哥4阅读 1.7k评论 2

封面图
Vue+Express+Mysql全栈项目之增删改查、分页排序导出表格功能
本文记录一下实现一个全栈项目,前端使用vue框架、后端使用express框架、数据库使用mysql。此项目的意义不仅仅有助于我们复习nodejs相关知识、更有助于带前端新人,使其快速从整体全局角度中,理解常规后台管理系...

水冗水孚4阅读 2.6k

MySQL百万数据深度分页优化思路分析
一般在项目开发中会有很多的统计数据需要进行上报分析,一般在分析过后会在后台展示出来给运营和产品进行分页查看,最常见的一种就是根据日期进行筛选。这种统计数据随着时间的推移数据量会慢慢的变大,达到百万...

一个程序员的成长7阅读 954

封面图

欢迎关注网易云信 GitHub:

608 声望
134 粉丝
宣传栏