mysql 线上加索引_「案例回放」线上环境一次真实的MySQL死锁案例

简单描述一下：两个会话(数据库连接)分别执行事务，where语句条件不合理，都对innoDB索引的某区间进行了锁定，导致死锁，MySQL自动发现死锁，令其中一个会话抛出Deadlock异常，另一个会话执行成功。

下面开始完整的案例回放：

主题: RCA：Deadlock found when trying to get lock——InnoDB gap 间隙锁引发的死锁类问题

一、现象

11月24日三方财务明细分配需求上线后，断断续续会收到一些报警邮件，频率不大；报警内容如下，均为Mysql DeadLock 异常。如下图：

邮件报警

第一时间登上服务器error日志如下：

error日志

mysql 往paidup_item 表插入新记录时，出现了死锁。什么原因造成的？对数据有什么影响？会不会影响后续流程？

遇到这种异常往往会很被动，一般都要立即回滚，所幸这个场景比较特殊——所有这些异常都发生在理房通回调的自动对账操作，异常发生后，理房通会有10次重试机制，而跟踪发现之前报异常的记录，再重试回调就成功了！因此该异常对流程、对数据不会有特别致命的影响，于是安下心来分析这个异常。

二、背景

1、死锁：死锁一般是事务相互等待对方资源，最后形成环路造成的。

2、MySQL InnoDB存储引擎，基于多版本的并发控制协议(MVCC)，其读操作分为：

快照读：简单的select操作，属于快照读，不加锁。(当然，也有例外，下面会分析)

 select * from table where ?;     

当前读：特殊的读操作，插入/更新/删除操作，属于当前读，需要加锁。

select * from table where ? lock in share mode;select * from table where ? for update;insert into table values (…);update table set ? where ?;delete from table where ?;      

3、MySQL/InnoDB定义的4种隔离级别

• Read Uncommited可以读取未提交记录。此隔离级别，不会使用，忽略。
• Read Committed (RC)快照读忽略，本文不考虑。针对当前读，RC隔离级别保证对读取到的记录加锁 (记录锁)，存在幻读现象。
• Repeatable Read (RR)快照读忽略，本文不考虑。针对当前读，RR隔离级别保证对读取到的记录加锁 (记录锁)，同时保证对读取的范围加锁，新的满足查询条件的记录不能够插入 (间隙锁)，不存在幻读现象。InnoDB默认级别，我们的库表采用的正是RR级别。
• Serializable从MVCC并发控制退化为基于锁的并发控制。不区别快照读与当前读，所有的读操作均为当前读，读加读锁 (S锁)，写加写锁 (X锁)。Serializable隔离级别下，读写冲突，因此并发度急剧下降，在MySQL/InnoDB下不建议使用。

4、三方财务明细分配需求的核心改动

实收明细分配时机改为对账后，分配规则变动。为了兼容老数据，分配时的步骤：

a)作废时实收下的老明细——对应sql如下：

update paidup_item set status=0,updated_time=now() where paidup_id=?;               

b)新的匹配算法生成新明细——对应sql：

 insert into paidup_item (…) values (???)              

以上两笔操作是在同一个事务内。另外paidup_item表结构如下为

paidup_item表结构

主键为id，paidup_id为二级索引(非唯一索引)。

三、分析过程

经过大量资料查询及私下讨论，确定为Innodb 在RR级别下 二级索引的间隙锁问题，并在线下模拟出线上异常场景。现抽象如下：

1、场景描述:

paidup_item表只准备了三条数据。其对应的二级索引 index_on_paidup_id 情况如下：

现在开两个session，分别执行如下

SESSION-A:

SESSION-B：

如上在两个session中交叉执行后，在第3步执行后session-A 处于等待状态；第4步执行后session-B 抛错如下，而session-A执行成功。

以上场景及现象与线上完全一致。理房通自动对账的paidup_id是自动增长的，对账时paidup_item表里并无该记录。

2、异常是如何产生的：

a) session-A 执行第1步：

update paidup_item set status=0,updated_time=now() where paidup_id=128498;

INNODB以独占锁锁定全部通过WHERE条件找到的索引项，并用共享锁锁定他们之间的间隙。

符合条件的记录不存在，在index_on_paidup_id索引上不加行锁；在区间(128497，无穷大)上加共享的间隙锁。

ps:间隙锁区间划分详见我的头条号文章——《让你彻底搞懂MySQL的锁机制(中篇)》

b) session-B执行第2步：

update paidup_item set status=0,updated_time=now() where paidup_id=128499;

符合条件的记录不存在，在index_on_paidup_id索引上不加行锁；在区间(128497，无穷大)上加共享的间隙锁。

c) session-A执行第3步：

insert into paidup_item (...) values (???)       paidup_id=128498

由于session-B也占用了index_on_paidup_id 上 区间(128497，无穷大)的间隙锁，session-A要插入的数据正好处于被占区间，因此 session-A阻塞等待session-B释放间隙锁。

d) session-B执行第4步：

insert into paidup_item (...) values (???)       paidup_id=128499

由于session-A占用了index_on_paidup_id 上 区间(128497，无穷大)的间隙锁，session-B要插入的数据正好处于被占区间，此时，session-B也需要阻塞等待session-A释放锁资源。到这一步，mysql已经能主动检测到死锁，对其中一个事务进行回滚，另一个事务就能继续执行。

因此出现session-B抛Deadlock found… ，而session-A执行成功。

四、解决方案

找到原因后，解决方案就有好多种，思路就是避免产生间隙锁。改写update语句，改为由主键id来更新。已于11月25号上线，之后未出现类似异常。

五、经验教训

1、InnoDB间隙锁是保证RR级别的根本，其存在是合理的。只是我们需要去深入了解它，在使用update 、delete、select … for update 等时，一定要警惕是否会占用较大范围的间隙锁。

2、RCA——root cause analysis 的目的是发现问题本质，举一反三，避免再次发生同类问题。

参考：

http://www.cnblogs.com/LBSer/p/5183300.html

http://hedengcheng.com/?p=771#_Toc374698322

http://www.jianshu.com/p/bf862c37c4c9

http://www.cnblogs.com/zuoxingyu/archive/2012/09/27/2705285.html

http://www.jianshu.com/p/bf862c37c4c9

---

感谢你的阅读，如果文章让你有所收获，欢迎点赞和分享。
本头条号专注于互联网领域的技术交流与经验分享，欢迎您的【关注】。

#互联网科技# #MySQL# #数据库#