MySQL 中如果发生死锁应该如何解决？

MySQL 死锁问题分析与解决方案

【核心定义】

MySQL 死锁是指两个或两个以上的事务在执行过程中，因争夺锁资源而造成的一种互相等待的现象，若无外力干预，这些事务都将无法继续执行。

【关键要点】

死锁检测与自动处理：InnoDB 引擎默认开启死锁检测，检测到死锁后会自动回滚其中一个事务（通常选择回滚代价最小的事务），让其他事务得以继续执行。
手动排查步骤：
- 查看最近死锁信息：SHOW ENGINE INNODB STATUS\G，重点关注 LATEST DETECTED DEADLOCK 部分
- 监控死锁日志：开启 innodb_print_all_deadlocks 参数，将所有死锁信息写入错误日志
- 使用性能模式：SELECT * FROM performance_schema.data_locks 和 SELECT * FROM performance_schema.data_lock_waits
根本解决策略：
- 事务设计优化：保持事务短小，尽快提交
- 访问顺序一致：多个事务访问相同资源时，按固定顺序访问
- 索引优化：确保查询使用合适的索引，减少锁的范围
- 锁粒度控制：必要时使用 SELECT ... FOR UPDATE 明确锁需求

【深度推导/细节】

死锁产生的典型场景分析

场景一：交叉更新（最常见）

-- 事务1
UPDATE users SET balance = balance - 100 WHERE id = 1;
UPDATE users SET balance = balance + 100 WHERE id = 2;

-- 事务2（更新顺序相反）
UPDATE users SET balance = balance - 50 WHERE id = 2;
UPDATE users SET balance = balance + 50 WHERE id = 1;

死锁发生过程：

Step 1: 事务1获取 id=1 的行锁
Step 2: 事务2获取 id=2 的行锁
Step 3: 事务1尝试获取 id=2 的行锁（等待事务2释放）
Step 4: 事务2尝试获取 id=1 的行锁（等待事务1释放）
Step 5: 循环等待形成死锁

场景二：间隙锁冲突

-- 表结构：id为主键，age有普通索引
-- 事务1
SELECT * FROM users WHERE age = 20 FOR UPDATE;

-- 事务2
INSERT INTO users (name, age) VALUES ('test', 20);

当 age=20 的记录不存在时，事务1会获取间隙锁，事务2的插入操作会被阻塞，如果还有其他锁竞争可能形成死锁。

InnoDB 死锁处理机制

等待图算法（Wait-for Graph）：

InnoDB 使用有向图检测死锁
节点：事务
边：事务A等待事务B持有的锁
定期检测图中是否存在环，存在则判定为死锁

回滚策略选择：

默认回滚修改行数最少的事务（UNDO 量最小）
可通过 innodb_deadlock_detect 关闭死锁检测（不推荐）

【关联/对比】

InnoDB vs MyISAM 的死锁处理

特性	InnoDB	MyISAM
死锁可能	✅ 支持行锁，可能发生死锁	❌ 只有表锁，不会死锁
检测机制	内置死锁检测	无
恢复方式	自动回滚一个事务	无死锁问题

乐观锁 vs 悲观锁的死锁概率

悲观锁（SELECT ... FOR UPDATE）：死锁概率较高，需要精心设计访问顺序
乐观锁（版本号/时间戳）：无死锁问题，但需要处理更新失败的重试逻辑

【实战解决流程】

步骤一：立即诊断

-- 1. 查看当前锁信息
SHOW ENGINE INNODB STATUS\G

-- 2. 查看正在运行的事务
SELECT * FROM information_schema.INNODB_TRX;

-- 3. 查看锁等待关系
SELECT * FROM sys.innodb_lock_waits;

步骤二：分析死锁日志

从 SHOW ENGINE INNODB STATUS 输出的死锁部分，关注：

WAITING FOR THIS LOCK: 事务在等待什么锁
HOLDS THE LOCK: 事务持有什么锁
事务的SQL语句

步骤三：实施解决方案

方案A：临时规避

-- 设置锁等待超时（默认50秒）
SET innodb_lock_wait_timeout = 10;

-- 当前会话设置
SET SESSION innodb_lock_wait_timeout = 5;

方案B：应用层重试

def execute_with_retry(sql, max_retries=3):
    for i in range(max_retries):
        try:
            execute_sql(sql)
            return True
        except DeadlockError:
            if i == max_retries - 1:
                raise
            sleep(random.uniform(0.1, 0.3))  # 随机等待避免活锁
    return False

方案C：数据库设计优化

-- 1. 所有事务按相同顺序访问表
-- 2. 添加必要索引，减少锁范围
CREATE INDEX idx_user_account ON transactions(user_id, account_id);

-- 3. 降低隔离级别（需评估业务影响）
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

步骤四：监控与预防

-- 开启死锁日志记录
SET GLOBAL innodb_print_all_deadlocks = ON;

-- 监控死锁频率
SELECT count(*)
FROM information_schema.INNODB_METRICS
WHERE NAME = 'lock_deadlocks';

『面试官追问』

如何从死锁日志中判断死锁的根本原因？
- 分析每个事务持有的锁和等待的锁
- 查看SQL语句的执行计划，判断是否缺少索引
- 检查事务的执行顺序是否一致
除了交叉更新，还有哪些场景容易产生死锁？
- 批量插入时的唯一键冲突
- 外键约束的级联更新
- 并发创建相同记录（先查后插模式）
- 全表更新与索引更新的并发
如何设计系统以减少死锁发生？
- 使用UUID代替自增ID，避免热点插入
- 将大事务拆分为小事务
- 使用SELECT ... FOR UPDATE NOWAIT（MySQL 8.0+）
- 应用层实现锁队列，串行化冲突操作
MySQL 8.0 在死锁处理方面有哪些改进？
- 新增 NOWAIT 和 SKIP LOCKED 语法，避免等待
- 性能模式的锁信息表更完善
- 死锁检测算法优化，性能更好
什么情况下应该关闭死锁检测？
- 超高并发场景，死锁检测消耗大量CPU
- 业务能接受锁等待超时，而不是立即回滚
- 应用层有完善的重试机制
- 使用命令：SET GLOBAL innodb_deadlock_detect = OFF;

【性能优化建议】

索引设计黄金法则：确保WHERE条件、JOIN条件、ORDER BY字段都有合适索引
事务拆分原则：事务执行时间控制在100ms以内
锁升级策略：当死锁频率超过阈值时，考虑使用应用层分布式锁
监控指标：
- 死锁频率：< 1次/小时
- 锁等待时间：P95 < 100ms
- 事务回滚率：< 0.1%

通过以上系统化的分析、诊断和优化策略，可以有效解决和预防MySQL死锁问题，保障数据库的稳定性和性能。

# MySQL 中如果发生死锁应该如何解决？