MySQL死锁原因及解决方法

原创已于 2025-05-27 10:39:41 修改 · 1.4k 阅读

6 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库

于 2025-05-27 10:11:18 首次发布

大数据面试专栏收录该内容

183 篇文章

订阅专栏

MySQL 死锁（Deadlock）是指两个或多个事务在执行过程中，因争夺资源而陷入相互等待的状态，导致事务无法继续执行的现象。当死锁发生时，MySQL 会自动检测并选择一个事务作为“牺牲者”（通常是较小的事务）进行回滚，另一个事务则继续执行。

死锁的常见原因

事务访问顺序不一致
事务以不同的顺序访问多个表或行（例如：事务A先操作表1再操作表2，事务B先操作表2再操作表1）。
长时间事务
事务长时间持有锁未提交，增加与其他事务冲突的概率。
索引不当
未正确使用索引导致锁范围扩大（例如：全表扫描时可能升级为表锁）。
并发修改相同数据
多个事务同时修改同一行数据，或竞争同一批资源。

解决死锁的常见方法

1. 自动处理：MySQL 的默认机制

MySQL 检测到死锁后会自动回滚其中一个事务（通过 innodb_deadlock_detect 控制）。
应用层需要捕获死锁错误（错误码 1213），并重试被回滚的事务。

2. 优化事务设计

保持事务简短
减少事务执行时间，尽快提交或回滚，避免长时间持有锁。
按固定顺序访问资源
所有事务以相同的顺序操作表或行（例如：统一按表A → 表B的顺序操作）。
减少事务粒度
拆分大事务为多个小事务，减少锁竞争范围。

3. 合理使用索引

确保查询条件有合适的索引，避免全表扫描导致锁升级（如行锁变表锁）。
使用 EXPLAIN 分析查询执行计划，优化索引设计。

4. 降低隔离级别

使用较低的隔离级别（如 READ COMMITTED），减少锁的范围和持有时间（但需权衡数据一致性）。

5. 显式锁定

在事务中提前锁定所有需要的资源，例如使用 SELECT ... FOR UPDATE 锁定关键行，避免后续争用。
注意：需谨慎使用，不当的显式锁定可能加剧死锁。

6. 设置锁超时

通过 innodb_lock_wait_timeout 设置锁等待超时时间（默认50秒），超时后自动回滚当前语句（非整个事务）。

7. 监控与分析

查看死锁日志
启用 innodb_print_all_deadlocks 后，MySQL 会将死锁信息写入错误日志。
分析死锁信息
执行 SHOW ENGINE INNODB STATUS 查看最近的死锁详情，定位冲突的事务和SQL语句。

示例场景

下面通过一个具体的例子说明 MySQL 死锁的产生和解决方法。

假设有两个事务（事务A和事务B），操作两张表 account 和 order，表中数据如下：

表结构

-- 账户表
CREATE TABLE account (
  id INT PRIMARY KEY,
  balance INT
);
INSERT INTO account VALUES (1, 1000), (2, 2000);

-- 订单表
CREATE TABLE order (
  id INT PRIMARY KEY,
  user_id INT,
  amount INT
);
INSERT INTO order VALUES (101, 1, 500), (102, 2, 800);

死锁产生过程

事务A的操作步骤

开启事务，并更新 account 表中 id=1 的行：

START TRANSACTION;
UPDATE account SET balance = balance - 100 WHERE id = 1;
-- 事务A持有 id=1 的行锁（account表）

接着尝试更新 order 表中 user_id=1 的行：

UPDATE order SET amount = amount + 100 WHERE user_id = 1;
-- 事务A尝试获取 user_id=1 的行锁（order表）

事务B的操作步骤

开启事务，并更新 order 表中 user_id=2 的行：

START TRANSACTION;
UPDATE order SET amount = amount - 200 WHERE user_id = 2;
-- 事务B持有 user_id=2 的行锁（order表）

接着尝试更新 account 表中 id=2 的行：

UPDATE account SET balance = balance + 200 WHERE id = 2;
-- 事务B尝试获取 id=2 的行锁（account表）

死锁发生

事务A 持有 account.id=1 的锁，等待 order.user_id=1 的锁。
事务B 持有 order.user_id=2 的锁，等待 account.id=2 的锁。
如果此时两个事务的锁请求形成循环等待，就会触发死锁！

MySQL 如何处理？

MySQL 检测到死锁后，会选择一个事务作为牺牲者（通常选择回滚代价较小的事务），例如回滚事务B。
事务B的更新操作被撤销，释放其持有的锁。
事务A成功获取 order.user_id=1 的锁，继续执行并提交。

如何解决这类死锁？

方法1：统一资源访问顺序

所有事务按固定顺序操作表或行。例如：

事务A和事务B统一先操作 account 表，再操作 order 表。
避免交叉等待。

方法2：缩短事务时间

减少事务内的操作步骤，尽快提交事务。
例如：将事务A和事务B的两次更新拆分为独立的小事务。

方法3：使用索引优化

确保 order.user_id 字段有索引，避免全表扫描升级为表锁。
如果 user_id 无索引，UPDATE order 会锁全表，导致死锁概率增加。

分析死锁日志

执行 SHOW ENGINE INNODB STATUS，查看 LATEST DETECTED DEADLOCK 部分：

LATEST DETECTED DEADLOCK
------------------------
*** (1) TRANSACTION:
TRANSACTION 12345, ACTIVE 10 sec updating
UPDATE order SET amount = amount + 100 WHERE user_id = 1

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 12345 ... index PRIMARY of table `test`.`account`

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 12345 ... index `idx_user_id` of table `test`.`order`

*** (2) TRANSACTION:
TRANSACTION 67890, ACTIVE 8 sec updating
UPDATE account SET balance = balance + 200 WHERE id = 2

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 12345 ... index `idx_user_id` of table `test`.`order`

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 12345 ... index PRIMARY of table `test`.`account`

通过日志可以明确：