基于本文回答

播面 播面

文图音视，全方位拆解八股文
0
评论
请从左侧选择一个分类

什么是死锁？mysql中如何避免和排查死锁？

知识点图片

讲解MySQL死锁的概念、避免方法（如统一加锁顺序）和排查技巧（查看死锁日志）。

这是一个关于MySQL死锁非常经典和重要的问题。我们来分步详细解释。

什么是死锁 (What is a Deadlock)？

1. 概念定义

死锁是指两个或多个事务（进程）在执行过程中，因争夺资源而造成的一种互相等待的现象，若无外力干涉，它们都将无法推进下去。

2. 一个简单的比喻

想象一座只能单向通行的窄桥。

  • 事务A 就像一辆从桥的南边开上来的车。
  • 事务B 就像一辆从桥的北边开上来的车。

两辆车在桥中间相遇了。

  • 事务A（南边的车）想要完全通过桥，需要等待事务B（北边的车）先退回去。
  • 事务B（北边的车）也想完全通过桥，需要等待事务A（南边的车）先退回去。

双方都占有了一部分资源（自己已经走过的桥面），同时又在等待对方释放资源（对方占有的桥面）。由于谁也不肯退让，就形成了僵局，这就是“死锁”。

3. 在MySQL中的具体场景

在MySQL (特别是InnoDB引擎)中，资源就是数据行锁。一个典型的死锁场景如下：

  • 事务A：锁住了第1行数据，然后尝试去锁第10行数据。
  • 事务B：在同一时间，锁住了第10行数据，然后尝试去锁第1行数据。

这时，就会发生：

  • 事务A持有第1行的锁，等待事务B释放第10行的锁。
  • 事务B持有第10行的锁，等待事务A释放第1行的锁。

这就形成了一个循环等待链，死锁就产生了。InnoDB引擎有一个死锁检测机制，当它发现这种循环等待后，会主动选择一个“代价”最小的事务进行回滚 (Rollback)，释放其持有的锁，从而让另一个事务可以继续执行。被回滚的事务会收到一个错误：ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction。

如何避免死锁 (How to Avoid Deadlocks)

避免死锁的核心思想是打破形成死锁的四个必要条件之一（互斥、请求与保持、不剥夺、循环等待）。在实践中，我们主要通过打破“循环等待”条件来避免死lock。

以下是几种非常有效的策略：

1. 保持一致的加锁顺序

这是最重要也是最有效的避免死锁的方法。确保所有需要操作多行数据的事务，都按照相同的顺序来获取锁。

  • 错误示例 (可能导致死锁):
    • 代码逻辑A: UPDATE T1 SET ... WHERE id=5; UPDATE T2 SET ... WHERE id=10;
    • 代码逻辑B: UPDATE T2 SET ... WHERE id=10; UPDATE T1 SET ... WHERE id=5;
  • 正确做法:
    • 规定所有代码必须先操作T1，再操作T2。或者按ID从小到大的顺序加锁。
    • 代码逻辑A: UPDATE T1 SET ... WHERE id=5; UPDATE T2 SET ... WHERE id=10;
    • 代码逻辑B: UPDATE T1 SET ... WHERE id=5; UPDATE T2 SET ... WHERE id=10;

这样，一个事务要么成功获取所有锁，要么在获取第一个锁时就等待，不会出现交叉持锁的情况。

2. 缩短事务的持有锁时间

事务越长，持有锁的时间就越久，与其他事务发生冲突的概率就越大。

  • 将复杂的计算、IO操作等耗时任务移出事务之外。先在事务外准备好所有需要的数据，然后开启事务，快速执行INSERT, UPDATE, DELETE，然后立即提交。
  • 避免在事务中进行用户交互，如等待用户输入。

3. 使用合适的索引

如果SQL语句没有走索引，会导致全表扫描，MySQL会锁住大量不必要的数据行，甚至锁住整张表，这会大大增加死锁的概率。

  • 确保所有UPDATE和DELETE语句的WHERE条件列都有合适的索引。
  • 特别是范围查询时，索引能显著减少锁定的行数和间隙锁（Gap Lock）的数量。

4. 尽量使用较低的事务隔离级别

MySQL的默认隔离级别是REPEATABLE-READ（可重复读）。在这个级别下，InnoDB会使用间隙锁（Gap Lock）来防止幻读，但这也会增加锁冲突和死锁的概率。
如果业务场景允许（没有防止幻读的强需求），可以将隔离级别设置为READ-COMMITTED（读已提交）。在此级别下，没有间隙锁，能降低死锁发生的概率。
注意: 这是一种权衡，可能会带来不可重复读的问题。

5. 提前锁定需要的行

使用SELECT ... FOR UPDATE可以提前获取你将要更新的行的排他锁。这样可以避免在SELECT和UPDATE之间，其他事务插入或修改数据导致的问题。

sql 
-- 开启事务
START TRANSACTION;

-- 一次性查询并锁定所有需要操作的行
SELECT * FROM users WHERE id IN (1, 10, 20) FOR UPDATE;

-- 然后执行更新操作
UPDATE users SET status = 'processed' WHERE id = 1;
UPDATE users SET status = 'processed' WHERE id = 10;
-- ...

-- 提交事务
COMMIT;

这种方式也能帮助你遵循“一次性申请所有资源”的原则，减少死锁的可能性。

6. 增加重试机制

由于死锁在复杂的系统中很难被100%避免，所以应用程序层面必须有重试机制。当捕获到死锁错误（Error 1213）时，等待一个随机的短暂时间，然后重新执行整个事务。这是一种有效的“兜底”方案。

如何排查死锁 (How to Diagnose Deadlocks)

当死锁发生时，你需要像侦探一样找出原因。

1. 查看最新的死锁日志: SHOW ENGINE INNODB STATUS

这是排查死锁最直接、最常用的命令。执行后，会返回大量信息，你需要找到LATEST DETECTED DEADLOCK这一节。

sql 
SHOW ENGINE INNODB STATUS\G

这部分日志会清晰地告诉你：

  • 死锁发生的时间。
  • 事务1 (TRANSACTION 1):
    • 正在执行的SQL语句。
    • 持有哪些锁 (HOLDS LOCK(S))。
    • 正在等待哪个锁 (WAITS FOR LOCK)。
  • 事务2 (TRANSACTION 2):
    • 同样，它正在执行的SQL，持有的锁，和等待的锁。
  • 谁是“牺牲品”: WE ROLL BACK TRANSACTION (1)，告诉你哪个事务被回滚了。

通过分析两个事务的SQL、持锁和等锁信息，你就能清晰地看到循环等待的链路，从而定位到导致死锁的代码。

2. 开启死锁日志记录

SHOW ENGINE INNODB STATUS只显示最近一次的死锁。如果死锁频繁发生，或者发生在你不方便登录数据库的时候，你可以将所有死锁信息记录到MySQL的错误日志中。

在MySQL配置文件（my.cnf或my.ini）中设置：

plaintext 
[mysqld]
innodb_print_all_deadlocks = ON

修改后重启MySQL服务。这样，每次发生死锁，详细信息都会被自动记录下来，方便事后分析。

3. 使用 Performance Schema (高级)

对于更复杂的场景，可以使用Performance Schema中的锁信息表来监控和分析。

  • performance_schema.data_locks: 显示当前持有的锁和正在等待的锁。
  • performance_schema.data_lock_waits: 专门显示锁等待的关系。

通过查询这些表，可以实时地构建出锁等待图，对于定位复杂的死锁问题非常有帮助。

总结

方面 核心要点
什么是死锁 两个或多个事务互相持有对方需要的锁，形成循环等待。InnoDB会自动检测并回滚一个事务来解决。
如何避免 1. 统一加锁顺序 (最重要)。
2. 缩短事务，减少锁持有时间。
3. 使用合适的索引，避免表扫描。
4. 考虑降低隔离级别 (如READ-COMMITTED)。
5. 使用SELECT ... FOR UPDATE提前锁定。
6. 在应用层加入事务重试机制。
如何排查 1. 使用 SHOW ENGINE INNODB STATUS 查看最新的死锁日志。
2. 开启 innodb_print_all_deadlocks 将所有死锁记录到错误日志。
3. (高级) 使用 Performance Schema 相关表进行实时分析。

理解并遵循这些原则，可以大大降低你的MySQL数据库中死锁发生的概率，并在它发生时能快速有效地解决问题。

00:00
00:00