SQL 死锁产生原因与排查方法

死锁报错特征为mysql提示“deadlock found when trying to get lock”、postgresql提示“deadlock detected”、sql server报1205错误；本质是事务间循环等待，系统会杀掉一个事务使其回滚。

死锁报错长什么样？怎么一眼认出是死锁

MySQL 报错里出现 Deadlock found when trying to get lock，PostgreSQL 是 deadlock detected，SQL Server 则抛 1205 错误 —— 这三类就是最直接的死锁信号。不是所有锁等待都叫死锁，只有 A 等 B、B 又等 A 的循环依赖才触发。系统检测到后会主动杀掉其中一个事务（victim），让它回滚并报错，另一个继续执行。

常见误判点：

• 把长时间 Lock wait timeout exceeded（超时）当成死锁 —— 那只是单向等待，没形成环
• 应用日志只记了“更新失败”，没捕获数据库原生错误，导致排查时漏掉关键线索
• ORM 框架吞掉底层 SQL 异常，只返回泛化错误，得去开 log_statement = 'all' 或启用慢日志+死锁日志才能定位

MySQL 怎么查死锁详情？SHOW ENGINE INNODB STATUS 看哪几块

这条命令输出很长，但真正有用的就三段：LATEST DETECTED DEADLOCK 下的最近一次死锁快照、TRANSACTIONS 里的事务状态、以及各事务持有的锁和等待的锁。重点盯住「transaction X has lock… transaction Y has lock… but is waiting for…」这句链式描述。

实操注意：

• 输出只保留最后一次死锁，重启 MySQL 或下次死锁发生就会覆盖，得配合监控脚本定期采集
• innodb_print_all_deadlocks = ON 必须提前开启，否则死锁信息只进 Error log，且默认不记录完整 SQL
• 看到 RECORD LOCKS space id ... page no ... n bits 不用深究页号，关注 index `xxx` 和涉及的 PRIMARY 或二级索引名更实用
• 如果事务显示 TRX HAS BEEN WAITING FOR ... SEC 却没被选为 victim，说明它只是长等待，不是死锁参与者

PostgreSQL 死锁怎么定位？pg_locks 和 pg_stat_activity 联查要点

PostgreSQL 不像 MySQL 那样自动输出死锁图，得靠关联视图实时抓。核心思路是：找出 granted = false 的锁请求，再顺着 pid 关联到正在运行的语句。

常用查法：

• 执行 select * FROM pg_locks WHERE NOT granted; 找阻塞源头
• 连查 SELECT blocked_locks.pid AS blocked_pid, blocking_locks.pid AS blocking_pid, blocked_activity.query AS blocked_query, blocking_activity.query AS current_query FROM pg_catalog.pg_locks blocked_locks JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid JOIN pg_catalog.pg_locks blocking_locks ON blocking_activity.pid = blocking_locks.pid AND blocking_locks.granted JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid WHERE NOT blocked_locks.granted;
• 注意 pg_locks 中的 mode 字段：多个 RowExclusiveLock 不冲突，但一个 ShareLock 和一个 ExclusiveLock 同时争同一行就容易卡住
• 事务长时间 idle in transaction 状态，却持有锁，是典型隐患 —— 用 state = 'idle in transaction' 过滤就能揪出来

如何避免死锁？顺序、索引、事务粒度三个硬约束

死锁不能靠重试兜底，得从写法上掐断循环依赖可能。最有效的办法就三条：访问表和行的顺序一致、让查询走索引、缩小事务范围。

具体怎么做：

• 多表更新/删除必须固定顺序，比如总按 users → orders → order_items 的顺序操作，别在 A 事务里先改 orders 再改 users，B 事务反过来
• UPDATE/delete 语句必须命中索引，否则会升级成表锁或间隙锁，大幅增加冲突概率；用 EXPLAIN ANALYZE 确认 type 是 ref 或 range，不是 ALL
• 事务里别混 DML 和耗时操作（如 http 调用、文件读写），也别放用户输入等待；把 BEGIN 尽量靠近第一个 SQL，COMMIT 尽量靠近最后一个
• 批量操作拆成小批次，比如一次更新 10 万行，分 1000 行一批，用 WHERE id BETWEEN ? AND ? 控制范围，避免锁住大片数据

死锁现场稍纵即逝，日志没开全、SQL 没打标、事务边界模糊 —— 这三处最容易让问题变成玄学。