乐趣区

关于数据库:网易云商记一次实遇的-MySQLindex-merge-死锁历程

jiezi

在理论业务开发过程中，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 加锁机制。事实上死锁的起因还有很多，比方操作数据程序不统一、长事务等，就不一一开展了。

退出移动版