事务和锁的考点都在这了，线上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)

2 、分析MySQL死锁日志

可见事务1要对一个已被间隙锁控制的记录进行插入意向锁录入，遂进入阻塞等待间隙锁释放，而恰巧另一个事务也同样要对一个被间隙锁控制的记录进行插入意向锁录入，阻塞等待，当两个事务间隙锁碰巧有交集时就进入了死循环最后死锁。

3、梳理解决方案

降低隔离级别为RC，避免间隙锁（降级后会有不可重复读和幻读问题）。

设置InnoDB在RR级别下不使用间隙锁（关闭后会有幻读问题）。

删除前先判断是否存在，存在再删除，可以完全避免死锁（会导致重复数据录入）。

在极端情况下，两个事务同时执行Select都不存在然后Insert，导致重复数据录入。

解决方案：

• 方案1：select for update（会降低并发度）。

• 方案2：加唯一索引，捕获异常回滚不执行。

• 方案3：若允许极端少数重复数据（仅文案展示），则无需处理。

另外也要注意尽量避免大事务，它不仅会降低并发还会提高死锁几率。

最终解决方案采用先判断再删除，目前涉及表为文案展示，允许极端情况下少量数据重复，故而暂不做绝对唯一处理。

4、方案3原理详解

还原线上场景：假设表中有1，6两条数据，两个事务分别要对不存在的2、5进行先删后插，且交叉执行。

假设表中不存在2和5对应记录，只有1和6

可见T1和T2的插入意向锁都要等待对方释放Gap锁，死循环。

现在我们修改逻辑，在删除前先判断，只有存在记录才进行delete操作。

假设表中2和5都存在

可见事务1和事务2的间隙锁范围不重叠，都可以成功施加插入意向锁。

我们再罗列另外一种情况，就是2或5只存在一个，会不会出现死锁呢？

假设表中2存在

可见虽然事务2可能插入意向锁记录被事务1占据，但是不会有死循环发生，等到事务1执行完释放锁就可以继续进行了。

综上所述，方案3可以完全避免死锁问题。

四、死锁场景分享

死锁案例一

死锁案例二

25和26记录都不存在，A和B并没有更新任何记录，但是由于数据库隔离级别为RR，所以会在 (20, 30) 之间加上间隙锁。之后A和B分别执行 INSERT 要插入25和26，需要在 (20, 30) 之间加插入意向锁，插入意向锁和间隙锁冲突，所以两个事务互相等待，最后形成死锁。

死锁案例三

加锁是一条记录一条记录挨个加锁，如果两条 SQL 语句的加锁顺序不一样，也可能会导致死锁。A 的加锁顺序为：id = 20 -> 30，B 的加锁顺序为：id = 30 -> 20，正好相反，所以会导致死锁。

死锁案例四

REPLACE INTO和INSERT ON DUPLICATE UPDATE。

这两个语句虽然原子化“存在则更新，不存在则插入”的语义，但在MySQL内部还是被拆为多个操作步骤，且在某些版本(5.7)会引入GAP锁来保证数据完整性，从而导致高并发情况下产生死锁。

作者丨子富

来源丨公众号：阿里技术（ID：ali_tech）

dbaplus社群欢迎广大技术人员投稿，投稿邮箱：editor@dbaplus.cn

关注公众号【dbaplus社群】，获取更多原创技术文章和精选工具下载