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

---

****

---

一、死锁原因分析

死锁通常由以下场景引发：

• 事务执行顺序不一致：多个事务以不同顺序访问雷同资源。
  
• 索引缺失：全表扫描导致行锁升级为表锁。
  
• 长事务或大事务：长时间持有锁资源，增长辩说概率。
  
• 隔离级别设置：如 REPEATABLE READ 隔离级别下的间隙锁竞争。
  

---

二、诊断死锁

1. 查察死锁日志

1. SHOW ENGINE INNODB STATUS;  -- 获取最新死锁信息

复制代码

重点关注 LATEST DETECTED DEADLOCK 段，分析涉及的事务、SQL 及锁信息。
2. 开启死锁监控（恒久跟踪）

1. # my.cnf 配置
2. innodb_print_all_deadlocks = ON  -- 记录所有死锁到错误日志

复制代码

---

三、解决死锁的常见方法

1. 优化事务逻辑

• 固定资源访问顺序：确保所有事务按雷同顺序操纵表或行。
  
  1. -- 事务1和事务2均按顺序更新表A、表B
  2. BEGIN;
  3. UPDATE table_a SET ... WHERE id = 1;
  4. UPDATE table_b SET ... WHERE id = 2;
  5. COMMIT;

  复制代码
• 收缩事务时间：克制在事务中执行耗时操纵（如外部 API 调用）。
  

2. 索引优化

• 为 WHERE、JOIN、ORDER BY 条件字段添加索引，减少锁范围。
  1. -- 添加联合索引减少锁冲突
  2. ALTER TABLE orders ADD INDEX idx_user_product (user_id, product_id);

  复制代码

3. 降低锁粒度

• 使用行锁代替表锁：确保操纵通过索引定位数据。
  
• 克制 SELECT ... FOR UPDATE 滥用：仅在须要时加锁。
  

4. 调整隔离级别

• 使用 READ COMMITTED 隔离级别，减少间隙锁（Gap Lock）的使用：
  1. SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

  复制代码

5. 主动死锁检测与重试

在代码层捕捉死锁错误（错误码 1213），主动重试事务：

1. # Python 示例（伪代码）
2. max_retries = 3
3. for attempt in range(max_retries):
4.     try:
5.         with connection.cursor() as cursor:
6.             cursor.execute("BEGIN")
7.             # 执行SQL操作
8.             cursor.execute("COMMIT")
9.         break
10.     except pymysql.err.OperationalError as e:
11.         if e.args[0] == 1213:  # 死锁错误码
12.             connection.rollback()
13.             time.sleep(0.1 * (2 ** attempt))  # 指数退避
14.         else:
15.             raise

复制代码

---

四、防备死锁的最佳实践

• 事务设计原则：
  
  
  
  • 保持事务简短，尽快提交或回滚。
    
  • 克制在事务中执行用户交互操纵。
    
  
  
• 索引与查询优化：
  
  
  
  • 定期分析慢查询日志，优化全表扫描语句。
    
  • 使用 EXPLAIN 查抄 SQL 执行计划。
    
  
  
• 监控与告警：
  
  
  
  • 通过 Prometheus + Grafana 监控死锁频率。
    
  • 配置报警规则（如每分钟死锁数凌驾阈值）。
    
  
  

---

五、案例分析

场景形貌

两个并发事务引发死锁：

• 事务1：UPDATE table SET ... WHERE id = 1; → UPDATE table SET ... WHERE id = 2;
  
• 事务2：UPDATE table SET ... WHERE id = 2; → UPDATE table SET ... WHERE id = 1;
  

死锁日志解读

1. LATEST DETECTED DEADLOCK
2. ...
3. *** (1) TRANSACTION:
4. TRANSACTION 12345, ACTIVE 0 sec updating
5. mysql tables in use 1, locked 1
6. LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
7. MySQL thread id 100, OS thread handle 0x7f8a1c0d6700, query id 2000 updating
8. UPDATE table SET ... WHERE id = 1;
10. *** (1) HOLDS THE LOCK(S):
11. RECORD LOCKS space id 0 page no 10 n bits 80 index PRIMARY of table `test`.`table`
12. trx id 12345 lock_mode X locks rec but not gap
14. *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
15. RECORD LOCKS space id 0 page no 11 n bits 80 index PRIMARY of table `test`.`table`
16. trx id 12345 lock_mode X locks rec but not gap
18. *** (2) TRANSACTION:
19. TRANSACTION 67890, ACTIVE 0 sec updating
20. mysql tables in use 1, locked 1
21. 3 lock struct(s), heap size 1136, 2 row lock(s)
22. MySQL thread id 101, OS thread handle 0x7f8a1c0d6800, query id 2001 updating
23. UPDATE table SET ... WHERE id = 2;
24. ...

复制代码

解决方案

• 同一更新顺序：所有事务按 id 升序更新。
  
• 合并更新语句：使用单条 SQL 更新多行。
  1. UPDATE table SET ... WHERE id IN (1, 2) ORDER BY id ASC;

  复制代码

---

六、工具保举

• pt-deadlock-logger（Percona Toolkit）：
  
  1. pt-deadlock-logger --ask-pass --host=localhost --user=root

  复制代码
  及时监控死锁事件并记录到文件。
  
• 性能模式（Performance Schema）：
  
  1. SELECT * FROM performance_schema.data_locks;  -- 查看当前锁状态

  复制代码

---

总结

解决 MySQL 死锁需联合 事务逻辑优化、索引调整 和 锁机制理解，核心原则是减少资源竞争。通过监控工具快速定位题目，并在代码层实现重试机制，可明显降低死锁对业务的影响。

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。