Skip to content

Deadlock Detected

错误信息

                deadlock detected
              

快速摘要

Two 事务是 waiting for 每个 other's locks, 创建 circular dependency. 数据库 detects 这个 and kills one 事务.

为什么发生

  • Two 事务 lock 相同行 in opposite 顺序

  • 长-运行事务持有 locks 虽然 waiting for other locks

  • Application code 那个获取 locks in inconsistent 顺序

最小示例

✗ 错误代码
-- Transaction A: locks row 1, then row 2
BEGIN;
UPDATE accounts SET balance = balance - 10 WHERE id = 1;
UPDATE accounts SET balance = balance + 10 WHERE id = 2;
-- Transaction B (concurrent): locks row 2, then row 1 → DEADLOCK
✓ 修复代码
-- Both transactions lock in the same order (1 then 2)
BEGIN;
UPDATE accounts SET balance = balance - 10 WHERE id = 1;
UPDATE accounts SET balance = balance + 10 WHERE id = 2;
COMMIT;

如果两者都事务 lock 行 in 相同顺序, circular wait 是 broken and 否 deadlock occurs.

如何诊断

  • 检查数据库 logs for deadlock details — 哪个事务 and 查询是 involved

  • Identify resources (行/表) 每个事务是 locking

  • Determine 如果事务 lock resources in 不同顺序

如何修复

  • Retry killed 事务 — deadlocks 是经常 transient

  • Ensure 所有事务 lock resources in 相同顺序

  • Keep 事务短 到 reduce window for deadlocks

  • Use lower isolation level 如果 appropriate (e.g., READ COMMITTED 相反的 SERIALIZABLE)

如何预防

  • 总是获取 locks in consistent 顺序跨越所有事务

  • Keep 事务 as 短 as 可能

  • Use appropriate isolation levels — 做不 over-lock

  • Implement retry logic for 事务那个失败 due 到 deadlocks

相关资源

相关术语

相关课程

  • 事务

    学习关于事务 and locking.

← 返回语言