mysqlmysql如何避免死锁发生-mysql教程-PHP中文网

死锁源于事务间循环等待资源，解决需打破循环等待条件。通过缩短事务时间、统一加锁顺序、使用索引减少锁范围、合理使用SELECT FOR UPDATE并配合ORDER BY、避免长事务和全表扫描，结合应用层重试机制，可显著降低死锁发生概率；排查时利用SHOW ENGINE INNODB STATUS分析冲突事务的锁持有与等待关系，定位问题SQL并优化执行计划。

mysqlmysql如何避免死锁发生

MySQL死锁，说到底就是两笔或多笔交易（事务）在争抢资源时，各自都持有了一部分资源，又都想获取对方持有的另一部分资源，结果谁也无法继续，形成了一个僵局。要避免它，核心思路就是减少事务冲突的可能性，并建立一套可预测的资源获取顺序。这听起来有点抽象，但落实到具体操作，就是精细化事务管理、优化查询和设计合理的数据访问模式。

死锁这东西，我个人觉得它就像是数据库并发世界里的一面镜子，映照出我们对数据访问和事务设计的理解深度。它不像慢查询那样直接指向性能瓶颈，更多时候，它是在提醒我们：嘿，你这里的数据访问逻辑可能有点混乱，或者说，不够“礼貌”。

解决方案

避免死锁，首先要从根本上理解它的发生机制，然后才能对症下药。我的经验是，没有银弹，但一系列最佳实践的组合拳，能大大降低死锁的发生概率。

缩短事务的持续时间：这是最直接也最有效的办法。事务越短，持有锁的时间就越短，与其他事务发生冲突的可能性就越小。能原子性完成的操作，就不要拖泥带水。
保持一致的锁定顺序：如果你的事务需要锁定多个资源（比如多行数据或多张表），务必让所有相关的事务都以相同的顺序去获取这些锁。比如，总是先锁定 A 表的记录，再锁定 B 表的记录。这就像大家排队，都按一个方向走，就不会在路口撞车。
使用合适的索引：这听起来像是性能优化，但实际上对避免死锁至关重要。当查询没有使用索引时，MySQL可能会进行全表扫描或全索引扫描，这会锁定更多的行，甚至升级为表锁，从而大大增加死锁的风险。高效的索引能确保事务只锁定它真正需要的行。
SELECT ... FOR UPDATE 的艺术：在需要更新数据之前，如果你知道稍后会更新某些行，提前使用 SELECT ... FOR UPDATE 显式锁定这些行。这能确保在后续更新时，这些行已经为你所独占，避免了其他事务介入并打乱你的锁定顺序。但也要注意，滥用它会增加锁等待。
减少不必要的锁定：检查你的事务逻辑，是不是有些操作其实不需要在事务内完成，或者不需要锁定那么多资源。比如，一些只读操作可以放在事务外部，或者使用更低的隔离级别（但要权衡数据一致性）。
处理死锁的重试机制：即使做了再多的预防，死锁也可能偶尔发生。MySQL在检测到死锁时，会自动选择一个事务作为“牺牲品”并回滚。你的应用程序应该能够捕获这个错误（例如 SQLSTATE '40001' 或错误代码 1213），然后重试整个事务。这是一种“防守性编程”，确保系统在面对死锁时能自我恢复。

为什么MySQL会发生死锁？理解其根本原因

死锁的发生，并非偶然，而是并发控制中特定条件组合下的必然产物。从操作系统的角度看，它符合著名的“Coffman条件”：

互斥条件（Mutual Exclusion）：资源在某一时刻只能被一个事务占用。在MySQL中，行锁、表锁等就是互斥的。一个事务锁住了一行，其他事务就不能再锁住它。
持有并等待条件（Hold and Wait）：一个事务已经持有了至少一个资源，但又在等待获取其他事务持有的资源。这就是典型的“僵局”开始。
不可剥夺条件（No Preemption）：资源在被事务占用期间不能被强制性地剥夺，只能由持有者主动释放。MySQL的锁就是这样的，除非事务结束或被回滚，否则锁不会被释放。
循环等待条件（Circular Wait）：存在一个事务链，每个事务都在等待链中下一个事务释放它所需要的资源，从而形成一个循环。

举个例子，假设我们有两张表 accounts (账户) 和 transactions (交易)，每个表都有主键索引。

事务 A：

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1; -- 锁定了 id=1 的账户
UPDATE transactions SET status = 'processed' WHERE account_id = 2; -- 尝试锁定 account_id=2 的交易
COMMIT;

登录后复制

事务 B：

START TRANSACTION;
UPDATE accounts SET balance = balance + 100 WHERE id = 2; -- 锁定了 id=2 的账户
UPDATE transactions SET status = 'pending' WHERE account_id = 1; -- 尝试锁定 account_id=1 的交易
COMMIT;

登录后复制

如果事务 A 先锁定了 accounts.id=1，同时事务 B 锁定了 accounts.id=2。接着，事务 A 尝试锁定 transactions 表中 account_id=2 的记录，但这条记录可能已经被事务 B 的某个操作（即使是隐式的）锁住，或者事务 B 正在尝试锁定它。同时，事务 B 尝试锁定 transactions 表中 account_id=1 的记录，而这条记录又可能被事务 A 锁住。这就形成了一个完美的循环等待：A 等 B 释放，B 等 A 释放。MySQL的InnoDB存储引擎会检测到这种情况，并选择其中一个事务（通常是修改行数最少的那个）回滚，以打破死锁。

理解了这些，我们就能明白，避免死锁的核心就是打破这些条件中的一个或多个，特别是循环等待条件。

设计与编码层面，有哪些具体策略能有效避免死锁？

在实际的系统设计和编码过程中，我们可以从多个维度入手，将死锁的预防融入到日常开发中。

事务粒度与范围的精确控制：
- 尽可能小：一个事务只做一件事，或者只包含一组紧密相关的操作。不要把整个业务流程都包在一个大事务里，特别是涉及到用户交互或外部系统调用的部分。
- 快速提交/回滚：一旦事务完成其逻辑，立即提交或回滚，不要让它长时间挂起。
- 避免在事务中等待用户输入或外部服务响应：这会极大地延长锁的持有时间，是死锁的温床。
强制一致的资源访问顺序：
- 全局约定：在设计数据库访问层时，就应该规定好访问多表或多行时的顺序。例如，涉及 User 和 Order 两张表的操作，总是先处理 User 表，再处理 Order 表。
  
  如知AI笔记
  如知笔记——支持markdown的在线笔记，支持ai智能写作、AI搜索，支持DeepseekR1满血大模型
  
  27
  
  查看详情
- 按主键/唯一键排序：如果需要更新多行数据，且这些行分布在同一个表中，可以先根据它们的主键或唯一键进行排序，然后按序更新。
```
-- 错误示范，可能导致死锁
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- 较好实践，如果需要同时更新，且可以提前确定所有ID
-- 假设要更新 id=1 和 id=2 的账户，并且总是按ID升序锁定
SELECT * FROM accounts WHERE id IN (1, 2) FOR UPDATE ORDER BY id;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
```
  登录后复制
  FOR UPDATE 结合 ORDER BY 是一个非常强大的组合，它能确保你以一个固定的、可预测的顺序锁定所有需要的行。
优化SQL查询，提升索引使用效率：
- 覆盖索引：如果查询能直接通过索引获取所有需要的数据，而不需要回表，那么查询速度会更快，持有锁的时间也更短。
- 正确使用 WHERE 子句：确保 WHERE 条件能有效利用索引，避免全表扫描。
- 外键索引：所有外键列都应该有索引，这对于关联查询和级联操作的性能至关重要，也能减少锁的冲突。
合理使用锁定机制：
- SELECT ... FOR UPDATE 的精准使用：当需要确保数据在读取后到更新前不会被其他事务修改时，这非常有用。但只锁定真正需要修改的行。
- 避免 LOCK TABLES：除非万不得已（比如进行DDL操作），否则尽量不要使用 LOCK TABLES，因为它会锁定整张表，极大地限制了并发。
应用程序层面的重试机制：
- 捕获死锁错误：在代码中捕获 Deadlock found when trying to get lock; try restarting transaction 这样的错误。
- 设计重试逻辑：当捕获到死锁错误时，不要直接报错，而是等待一小段时间（例如随机退避），然后重试整个事务。通常，重试几次就能成功。这是一种非常常见的处理死锁的方式，因为它把死锁从一个“错误”变成了一个“可恢复的事件”。

当死锁发生时，如何排查与优化？

死锁不是“避免”了就万事大吉，它总会以意想不到的方式出现。关键在于，当它发生时，我们有没有能力快速定位问题并进行优化。

SHOW ENGINE INNODB STATUS 是你的朋友：这是排查InnoDB死锁的黄金命令。它的输出非常详细，其中有一个 LATEST DETECTED DEADLOCK 部分，会清晰地告诉你：

死锁发生的时间。
涉及的两个（或多个）事务的详细信息：它们的ID、当前正在执行的SQL语句、持有的锁以及正在等待的锁。
被选为“牺牲品”的事务：MySQL会告诉你哪个事务被回滚了。通过分析这些信息，你可以直接看到是哪两条SQL语句、在什么资源上发生了冲突，从而定位到代码中的具体事务逻辑。

-- 示例输出片段（简化版）
------------------------
LATEST DETECTED DEADLOCK
------------------------
2023-10-27 10:30:05 0x7f9a8c0d7000
*** (1) TRANSACTION:
TRANSACTION 12345, ACTIVE 5 sec starting 'SQL_TEXT_1'
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 10, OS thread handle 0x7f9a8c0d7000, query id 20 localhost root updating
UPDATE accounts SET balance = balance - 100 WHERE id = 1
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 123 page no 456 n bits 72 index `PRIMARY` of table `test`.`accounts` trx id 12345 lock_mode X locks rec but not gap waiting
Record lock, table `test`.`accounts`, index `PRIMARY`, id 2
*** (2) TRANSACTION:
TRANSACTION 12346, ACTIVE 7 sec starting 'SQL_TEXT_2'
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 11, OS thread handle 0x7f9a8c0d8000, query id 21 localhost root updating
UPDATE accounts SET balance = balance + 100 WHERE id = 2
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 123 page no 456 n bits 72 index `PRIMARY` of table `test`.`accounts` trx id 12346 lock_mode X locks rec but not gap
Record lock, table `test`.`accounts`, index `PRIMARY`, id 1
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 123 page no 456 n bits 72 index `PRIMARY` of table `test`.`accounts` trx id 12346 lock_mode X locks rec but not gap waiting
Record lock, table `test`.`accounts`, index `PRIMARY`, id 1
*** WE ROLL BACK TRANSACTION (1)

登录后复制

通过这个输出，你可以清晰地看到事务12345在等待id=2的锁，而事务12346持有id=1的锁，并也在等待id=1的锁（这里示例有点简化，实际是它持有的锁和它等待的锁构成循环）。

利用 information_schema 视图： information_schema.innodb_trx、information_schema.innodb_locks 和 information_schema.innodb_lock_waits（或 sys.innodb_lock_waits 在MySQL 5.7+）提供了更细粒度的当前事务和锁信息。你可以通过这些视图实时监控锁等待情况，虽然它们不会直接报告死锁，但可以帮助你理解系统当前的并发状态。
分析慢查询日志和错误日志：
- 错误日志：MySQL的错误日志（error.log）会记录死锁的详细信息，这比 SHOW ENGINE INNODB STATUS 更持久，方便事后分析。确保你的MySQL配置开启了死锁日志记录。
- 慢查询日志：虽然死锁本身不一定是慢查询，但导致死锁的事务往往因为锁等待而变慢。分析慢查询日志可以帮助你发现那些长时间持有锁的“可疑”查询。
审查数据库模式和索引：
- 缺失索引：再次强调，确保所有 WHERE 子句、JOIN 条件和外键都有合适的索引。
- 不必要的唯一约束：有时过于严格的唯一约束也会增加锁冲突。
- 数据类型选择：选择最小合适的数据类型，可以减少行的大小，理论上也能略微提升锁定效率。
调整 innodb_lock_wait_timeout 参数：这个参数定义了一个事务在等待锁时最长等待多少秒。默认值通常是50秒。
- 调小：如果你的应用对响应时间非常敏感，可以适当调小这个值，让事务更快地放弃等待并回滚，从而更快地重试。但这也会增加事务回滚的频率。
- 调大：在某些批处理或长时间运行的事务中，如果偶尔的锁等待是可接受的，可以适当调大，减少因超时而回滚的情况。调整这个值需要非常谨慎，因为它直接影响到系统的并发行为和用户体验。