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