【八股消消乐】如何解决SQL线上死锁事故

😊你好，我是小航，一个正在变秃、变强的文艺倾年。
🔔本专栏《八股消消乐》旨在记录个人所背的八股文，包括Java/Go开发、Vue开发、系统架构、大模型开发、具身智能、机器学习、深度学习、力扣算法等相关知识点，期待与你一同探索、学习、进步，一起卷起来叭！

题目

💬技术栈：MySQL、锁

🔍简历内容：熟悉MySQL行锁实现算法，如record lock、gap lock、next-key lock，熟悉预防死锁的常用手段。

🚩面试问：

（1）项目初期没有实现读写表分离，都是基于一个主库完成读写操作，业务量逐渐增大后，数据库出现了异常报警。
（2）根据日志分析，作为幂等性校验的一张表经常出现死锁异常，怀疑索引导致的死锁问题。
（3）订单在做幂等性校验时，先是通过订单号检查订单是否存在，如果不存在则新增订单记录。
（4）分析可能的原因并给出解决方案。

---

💡建议暂停思考10s，你有答案了嘛？如果你有不同题解，欢迎评论区留言、打卡。

---

答案

原因：

此时，我们会发现两个事务已经进入死锁状态。我们可以在 information_schema 数据库中查询到具体的死锁情况，如下图所示：

为什么 SELECT 要加 for update 排他锁 ？
如果是两个订单号一样的请求同时进来，就有可能出现幻读。
一开始事务 A 中的查询没有该订单号，后来事务 B 新增了一个该订单号的记录，此时事务 A 再新增一条该订单号记录，就会创建重复的订单记录。

行锁

实现算法：

• record lock：专门对索引项加锁；
• gap lock：对索引项之间的间隙加锁，MySQL中默认开启，即 innodb_locks_unsafe_for_binlog 参数值是 disable 的，且 MySQL 中默认的是 RR 事务隔离级别。
• next-key lock：前面两种的组合，对索引项以其之间的间隙加锁。

对于gap lock 或 next-key lock：

（1）在 Select、Update 和 Delete 时，除了基于唯一索引的查询之外，其它索引查询时都会获取 gap lock 或 next-key lock，即锁住其扫描的范围。
（2）只在可重复读或以上隔离级别下的特定操作才会取得 gap lock 或 next-key lock。

SELECT id FROM demo.order_record where order_no = 4 for update;
order_no 列为非唯一索引，RR 事务隔离级别
SELECT 的加锁类型为 gap lock，这里的 gap 范围是 (4,+∞）

插入意向锁：是一种 gap 锁，它与 gap lock 是冲突的，所以当其它事务持有该间隙的 gap lock 时，需要等待其它事务释放 gap lock 之后，才能获取到插入意向锁。

执行查询 SQL 语句获取的 gap lock 并不会导致阻塞，而当我们执行插入 SQL 时，会在插入间隙上再次获取插入意向锁。

INSERT INTO demo.order_record(order_no, status, create_date) VALUES (5, 1, ‘2025-06-04 10:57:03’);

锁的兼容矩阵图：

预防死锁措施

死锁产生的必要条件：互斥、占有且等待、不可强占用、循环等待。

死锁示例场景：两个更新事务使用了不同的辅助索引，或一个使用了辅助索引，一个使用了聚簇索引，就都有可能导致锁资源的循环等待。由于本身两个事务是互斥，也就构成了以上死锁的四个必要条件了。

避免死锁方案：

1. 在编程中尽量按照固定的顺序来处理数据库记录，假设有两个更新操作，分别更新两条相同的记录，但更新顺序不一样，有可能导致死锁；
2. 在允许幻读和不可重复读的情况下，尽量使用 RC 事务隔离级别，可以避免 gap lock 导致的死锁问题；
3. 更新表时，尽量使用主键更新；
4. 避免长事务，尽量将长事务拆解，可以降低与其它事务发生冲突的概率；
5. 设置锁等待超时参数，我们可以通过 innodb_lock_wait_timeout 设置合理的等待超时阈值，特别是在一些高并发的业务中，我们可以尽量将该值设置得小一些，避免大量事务等待，占用系统资源，造成严重的性能开销。

Innodb提供了wait-for graph算法来主动进行死锁检测，我们可以通过innodb_deadlock_detect = on 打开死锁检测，当检测到死锁后会选择一个最小(锁定资源最少得事务)的事务进行回滚。

---

往期精彩专栏内容，欢迎订阅：

🔗【八股消消乐】20250603：索引失效与优化方法总结
🔗【八股消消乐】20250512：慢SQL优化手段总结
🔗【八股消消乐】20250511：项目中如何排查内存持续上升问题
🔗【八股消消乐】20250510：项目中如何优化JVM内存分配？
🔗【八股消消乐】20250509：你在项目中如何优化垃圾回收机制？
🔗【八股消消乐】20250508：Java编译优化技术在项目中的应用
🔗【八股消消乐】20250507：你了解JVM内存模型吗？
🔗【八股消消乐】20250506：你是如何设置线程池大小？
🔗【八股消消乐】20250430：十分钟带背Duubo中大厂经典面试题
🔗【八股消消乐】20250429：你是如何在项目场景中选取最优并发容器？
🔗【八股消消乐】20250428：你是项目中如何优化多线程上下文切换？
🔗【八股消消乐】20250427：发送请求有遇到服务不可用吗？如何解决？

📌 [ 笔者 ]   文艺倾年
📃 [ 更新 ]   2025.6.4
❌ [ 勘误 ]   /* 暂无 */
📜 [ 声明 ]   由于作者水平有限，本文有错误和不准确之处在所难免，本人也很想知道这些错误，恳望读者批评指正！