1. 数据库死锁排查从入门到精通刚入行那会儿最怕的就是半夜被报警叫醒系统卡死日志里赫然写着deadlock detected。死锁就像数据库里的幽灵来无影去无踪但破坏力极强。记得第一次处理生产环境死锁我对着日志研究了整整两天才勉强找到问题根源。现在回头看其实死锁排查有章可循关键是要读懂数据库留给我们的破案线索——死锁日志。死锁本质上是个资源竞争问题两个或多个事务互相持有对方需要的锁又都在等待对方释放就像两个人在狭窄的走廊里迎面相遇谁也不肯退让。MySQL、Oracle等主流数据库都内置了死锁检测机制一旦发现就会立即中断其中一个事务并在日志中记录详细现场信息。这些日志就是我们的破案关键。2. 死锁日志全解析从理论到实战2.1 死锁产生的四大经典场景先来看几个典型的死锁模式这些都是我这些年踩过的坑交叉更新死锁事务A先更新表1再更新表2事务B先更新表2再更新表1。当两个事务同时执行时就可能互相阻塞。-- 事务A UPDATE users SET score100 WHERE id1; -- 持有users.id1的锁 UPDATE orders SET status1 WHERE user_id1; -- 等待orders.user_id1的锁 -- 事务B UPDATE orders SET status1 WHERE user_id1; -- 持有orders.user_id1的锁 UPDATE users SET score100 WHERE id1; -- 等待users.id1的锁间隙锁冲突在RR隔离级别下范围查询会加间隙锁容易导致死锁。比如两个事务同时执行-- 事务A SELECT * FROM products WHERE price100 FOR UPDATE; -- 持有price100的间隙锁 INSERT INTO products VALUES(null,新品,150); -- 等待插入意向锁 -- 事务B SELECT * FROM products WHERE price200 FOR UPDATE; -- 持有price200的间隙锁 INSERT INTO products VALUES(null,新品,150); -- 等待插入意向锁唯一键冲突当两个事务同时插入相同唯一键值时可能发生死锁-- 事务A INSERT INTO users(id,name) VALUES(10,张三); -- 等待id10的锁 -- 事务B INSERT INTO users(id,name) VALUES(10,李四); -- 等待id10的锁锁升级死锁事务A先获取某行的共享锁然后尝试升级为排他锁同时事务B持有该行的共享锁也尝试升级。2.2 死锁日志的黄金结构以MySQL为例死锁日志通常包含以下关键信息基于InnoDB引擎LATEST DETECTED DEADLOCK ------------------------ 2023-08-20 14:30:45 0x7f8e60085700 *** (1) TRANSACTION: TRANSACTION 123456, ACTIVE 2 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s) MySQL thread id 100, OS thread handle 12345, query id 1000 192.168.1.1 root updating UPDATE products SET stockstock-1 WHERE id100 *** (1) HOLDS THE LOCK(S): RECORD LOCKS space id 10 page no 5 n bits 72 index PRIMARY of table test.products trx id 123456 lock_mode X locks rec but not gap *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 10 page no 6 n bits 72 index idx_category of table test.products trx id 123456 lock_mode X locks rec but not gap waiting *** (2) TRANSACTION: TRANSACTION 123457, ACTIVE 1 sec starting index read mysql tables in use 1, locked 1 3 lock struct(s), heap size 1136, 2 row lock(s) MySQL thread id 101, OS thread handle 12346, query id 1001 192.168.1.1 root updating UPDATE products SET priceprice*1.1 WHERE categoryelectronics *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 10 page no 6 n bits 72 index idx_category of table test.products trx id 123457 lock_mode X locks rec but not gap *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 10 page no 5 n bits 72 index PRIMARY of table test.products trx id 123457 lock_mode X locks rec but not gap waiting *** WE ROLL BACK TRANSACTION (2)解读要点事务信息每个事务的ID、状态、执行的SQL语句持有锁HOLDS THE LOCK(S)部分显示当前事务已经获取的锁等待锁WAITING FOR THIS LOCK显示事务正在等待的锁决策结果最后一行显示数据库选择回滚哪个事务2.3 实战分析电商库存更新死锁案例假设我们有个电商系统某天出现如下死锁日志LATEST DETECTED DEADLOCK ------------------------ *** (1) TRANSACTION: TRANSACTION 1234, ACTIVE 5 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s) MySQL thread id 10, query id 100 localhost root updating UPDATE inventory SET quantityquantity-1 WHERE product_id100 *** (1) HOLDS THE LOCK(S): RECORD LOCKS space id 5 page no 10 n bits 72 index PRIMARY of table db.inventory trx id 1234 lock_mode X locks rec but not gap *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 5 page no 11 n bits 72 index idx_product of table db.inventory trx id 1234 lock_mode X locks rec but not gap waiting *** (2) TRANSACTION: TRANSACTION 1235, ACTIVE 3 sec starting index read mysql tables in use 1, locked 1 2 lock struct(s), heap size 1136, 1 row lock(s) MySQL thread id 11, query id 101 localhost root updating UPDATE inventory SET quantityquantity-1 WHERE product_id101 *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 5 page no 11 n bits 72 index idx_product of table db.inventory trx id 1235 lock_mode X locks rec but not gap *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 5 page no 10 n bits 72 index PRIMARY of table db.inventory trx id 1235 lock_mode X locks rec but not gap waiting *** WE ROLL BACK TRANSACTION (2)分析步骤确认死锁类型这是典型的交叉更新死锁锁定资源路径事务1持有主键记录锁product_id100等待二级索引锁product_id100事务2持有二级索引锁product_id101等待主键记录锁product_id101SQL执行顺序事务1UPDATE inventory SET quantityquantity-1 WHERE product_id100事务2UPDATE inventory SET quantityquantity-1 WHERE product_id101根本原因两个事务以不同顺序获取锁且存在并发执行解决方案统一访问路径所有更新操作都通过主键进行减小事务粒度将长事务拆分为多个短事务添加重试机制捕获死锁异常后自动重试3. 高级排查技巧与工具链3.1 死锁监控系统搭建对于生产环境建议配置完善的死锁监控开启InnoDB死锁日志# my.cnf配置 [mysqld] innodb_print_all_deadlocks ON # 打印所有死锁到错误日志使用performance_schema监控-- 开启死锁检测 UPDATE performance_schema.setup_consumers SET ENABLED YES WHERE NAME LIKE %deadlock%; -- 查看死锁事件 SELECT * FROM performance_schema.events_statements_history_long WHERE EVENT_NAME LIKE %deadlock%;ELK日志分析系统将数据库错误日志接入ELK设置死锁告警3.2 锁等待超时优化除了死锁锁等待超时也是常见问题-- 查看当前锁等待情况 SELECT * FROM sys.innodb_lock_waits; -- 设置合理的锁等待超时时间 SET GLOBAL innodb_lock_wait_timeout 30; # 单位秒3.3 可视化分析工具对于复杂的死锁场景可以使用pt-deadlock-loggerPercona工具实时解析死锁日志pt-deadlock-logger /var/log/mysql/error.logMySQL Workbench可视化显示锁等待关系自定义脚本解析用Python解析死锁日志并生成可视化图表4. 预防死锁的工程实践4.1 应用层最佳实践统一访问顺序所有事务按照固定顺序访问表和行减小事务粒度避免大事务尽快提交合理使用索引确保查询使用适当的索引减少锁范围重试机制捕获死锁异常后自动重试3次为宜// Java示例死锁重试逻辑 int retries 3; while(retries 0) { try { // 执行业务逻辑 return doBusiness(); } catch (DeadlockException e) { retries--; if(retries 0) throw e; Thread.sleep(100 * (3 - retries)); // 指数退避 } }4.2 数据库配置优化隔离级别选择根据业务需求选择合适的事务隔离级别READ COMMITTED减少锁冲突但可能出现不可重复读REPEATABLE READInnoDB默认需要处理间隙锁问题锁超时设置SET GLOBAL innodb_lock_wait_timeout 30; # 默认50秒死锁检测优化SHOW VARIABLES LIKE innodb_deadlock_detect; # 是否开启死锁检测4.3 架构设计考量分布式锁对于分布式系统考虑使用Redis或Zookeeper实现分布式锁乐观锁对于冲突少的场景使用版本号控制替代悲观锁队列缓冲将并发请求串行化处理5. 典型死锁场景与解决方案速查表死锁类型典型场景解决方案交叉更新事务以不同顺序更新多张表统一访问顺序间隙锁冲突RR级别下的范围查询与插入改用RC级别或调整查询方式唯一键冲突并发插入相同唯一键先SELECT FOR UPDATE检查锁升级共享锁升级排他锁直接获取排他锁外键约束并发操作有外键关联的表按固定顺序操作父表和子表6. 死锁排查工具箱诊断命令SHOW ENGINE INNODB STATUS; # 查看最新死锁信息 SELECT * FROM information_schema.INNODB_TRX; # 查看当前运行事务 SELECT * FROM sys.innodb_lock_waits; # 查看锁等待关系日志分析脚本# 死锁日志分析脚本示例 import re def parse_deadlock(log_file): pattern rLATEST DETECTED DEADLOCK.*?WE ROLL BACK TRANSACTION \(\d\) with open(log_file) as f: content f.read() for match in re.finditer(pattern, content, re.DOTALL): print(Found deadlock:) print(match.group(0))监控配置Prometheus Grafana监控死锁指标配置死锁告警规则死锁排查就像侦探破案需要耐心分析日志中的每个线索。掌握了正确的方法后你会发现看似复杂的死锁问题其实都有规律可循。记住好的数据库工程师不是从不遇到问题而是能快速定位和解决问题。