一个线上SQL死锁异常分析：深入了解事务和锁

原创子富阿里云开发者

阿里妹导读：引发死锁的原因是什么？如何避免？本文详细介绍了和死锁有关的知识点，通过深入分析MySQL事务和锁的机制，结合案例背景，找到了问题的所在，并梳理了解决方案，详解其原理。希望对同学们有所启发。

一背景

最近线上消费MetaQ的服务频繁报SQL死锁异常，虽然最终可以基于事务自动回滚和逻辑重试保证最终正确性，但若一直放任不管，海量报警日志会掩盖真正需要紧急处理的异常，同时频繁回滚也会降低消费端的吞吐量。个人通过分析线上服务日志、MySQL死锁日志、梳理MySQL在RR级别下的锁机制，找到了真正的问题所在，并对业务处理逻辑进行了优化，特在此整理出来，互相学习提升，如果文中有错误的地方欢迎指正。

二知识储备

正所谓“工欲善其事，必先利其器”，在具体介绍CASE背景和解决方案前，先对需要系统了解的知识点进行详细介绍，以便大家能够快速理解解决方案。

死锁通常是因为两个及以上事务发生了死循环锁依赖，此时不得不回滚来释放锁，那么事务是个什么东西？

1 事务

为什么需要事务？

我们在业务实现时，经常需要保证某一批SQL能够具备ACID特性，如果没有事务，在应用里自己保证将会变得非常复杂，InnoDB引擎引入事务机制，极大简化了我们在此方面的编程模型。

ACID的实现机制是什么？

原子性（Atomicity）：事务内SQL要么同时成功要么同时失败，基于UndoLog实现。

一致性（Consistency）：系统从一个正确态转移到另一个正确态，由应用通过AID来保证，并非数据库的责任。

隔离性（Isolation）：控制事务并发执行时数据的可见性，基于锁和MVCC实现。

持久性（Durability）：提交后一定存储成功不会丢失，基于RedoLog实现。

下面简单说下RedoLog、UndoLog在整个执行过程中的流程（此部分可以掠过）：

为什么需要UndoLog？

InnoDb为支持回滚和MVCC，需要旧数据存档，UndoLog就负责存储这些数据，当更新BufferPool数据前，先将之前数据存入UndoLog。

为什么需要RedoLog？

BufferPool是随机IO以页为单位，性能损耗很大，不可每次提交都同步刷盘，需要后续异步进行。不能同步刷就会有一个问题，如果MySQL宕机，而事务已提交在BufferPool的数据还没有刷到磁盘，就会导致数据丢失持久性无法保证。为此引入RedoLog，这个文件IO是顺序追加IO且以修改为单位，性能很高，每次事务提交持久化RedoLog到磁盘也不会对性能造成太大影响，如果宕机可以通过重启从redoLog恢复丢失数据。

RedoLog高性能？

映射一段连续的存储空间，保证顺序IO，数据先写入Buffer，后一次性批量将事务数据写入磁盘。

2 锁

下面咱们说说InnoDB锁机制（此处重点关注）。

为了控制事务并发时的数据安全，在不同隔离级别下会通过不同的协同机制进行处理。传统隔离机制，完全由锁(LBCC)来处理，但是这样只能满足读读并发，会对性能造成很大影响，故而出现了支持读写并发的MVCC。因为MVCC不涉及此次背景，也不想罗列锁各种类型（避免让大家直接晕在这里），就简单直接的列出update、delete、insert的加锁情况（RC和RR不一样）。

Update & Delete语句加锁

1）聚簇索引（查询命中）

UPDATE students SET score = 100 WHERE id = 15;

RC、RR都是对聚簇索引加X锁。

2）聚簇索引（查询未命中）

UPDATE students SET score = 100 WHERE id = 16;

RC不加锁，RR在16之前和之后的范围里加GAP锁。

3）二级唯一索引（查询命中）

UPDATE students SET score = 100 WHERE no = 'S0003';

RC、RR会对二级和聚簇索引都加X锁（防止其他事务通过聚簇改数据）。

4）二级唯一索引（查询未命中）

UPDATE students SET score = 100 WHERE no = 'S0008';

RC不加锁，RR只在二级索引加GAP。

5）二级非唯一索引（查询命中）

UPDATE students SET score = 100 WHERE name = 'Tom';

RC对二级和聚簇加X锁，RR对二级加X锁和Gap对聚簇加X锁。

6）二级非唯一索引（查询未命中）

UPDATE students SET score = 100 WHERE name = 'John';

RC不加锁，RR只在二级索引加GAP。

注：以上图片源自
https://zhuanlan.zhihu.com/p/245584417

INSERT语句加锁

为了防止幻读，如果记录之间加有GAP锁，此时不能INSERT。

如果INSERT的记录和已有记录造成唯一键冲突，此时不能INSERT。

三线上CASE

1 分析服务线上日志

发现死锁是两个事务对同一个表先delete后insert交叉进行引起的：

delete from db.table where creativeid=102(且删除条数为0)delete fromdb.tablewhere creativeid=103(且删除条数为0)insert intodb.table (creativeid) values (102)insert intodb.table (creativeid) values (103)