关系数据库怎样防止死锁发生

在关系数据库（如 MySQL、PostgreSQL、Oracle、SQL Server 等）中，死锁通常出现在多个事务并发访问相同资源（行、表、索引）并以不同顺序加锁时。完全避免死锁很难，但可以通过设计和运维手段大幅减少死锁发生概率，或在发生后快速恢复。

下面从原理 → 预防策略 → 实战建议三个层面说明。

一、死锁产生的必要条件（回顾）

死锁通常同时满足：

1. 互斥：资源（如行锁）只能被一个事务持有
2. 持有并等待：事务持有一把锁，还在等另一把
3. 不可抢占：锁不能被强制剥夺
4. 循环等待：A 等 B，B 等 A

数据库无法消除前三条，但可以打破“循环等待”。

二、防止 / 减少死锁的核心方法

1️⃣ 统一加锁顺序（最重要）

所有事务以相同顺序访问表和行

❌ 容易死锁：

事务1：先锁 A，再锁 B
事务2：先锁 B，再锁 A

✅ 推荐：

所有事务都：先 A，后 B

实际做法：

• 多表更新时，固定表顺序
• 批量更新多行时，按主键排序后执行

-- 按主键顺序更新，减少死锁
UPDATE account SET balance = balance - 100 WHERE id IN (1,2,3);

2️⃣ 尽量减少锁的持有时间

锁持有越久，冲突概率越大。

建议：

• 事务中只放必要的 SQL
• 不要在事务中：
  • 调用外部接口
  • 做复杂计算
  • 等待用户输入
• 尽早 COMMIT 或 ROLLBACK

BEGIN;
UPDATE ...;
COMMIT;   -- 越快越好

3️⃣ 降低锁粒度

优先使用行锁而非表锁。

• 使用 InnoDB（支持行锁）
• 避免：

  LOCK TABLES
  SELECT ... FOR UPDATE（无索引）
  

• 确保 WHERE 条件走索引（否则可能升级为表锁）

4️⃣ 合理设计索引（非常关键）

没有索引 = 锁很多行甚至全表

✅ 建议：

• 高频 UPDATE / DELETE / SELECT FOR UPDATE 的字段必须建索引
• 避免函数、类型转换导致索引失效

-- 不好（可能锁全表）
UPDATE user SET status=1 WHERE DATE(create_time)='2024-01-01';

-- 好（走索引）
UPDATE user SET status=1 WHERE create_time BETWEEN '2024-01-01' AND '2024-01-02';

5️⃣ 控制事务隔离级别

不同隔离级别锁行为不同：

✅ 很多系统使用：

• MySQL：READ COMMITTED
• Oracle：READ COMMITTED（默认）

6️⃣ 避免热点行竞争

典型场景：

• 计数器表
• 库存单行
• 账户余额

优化方式：

• 拆分热点（如分桶计数）
• 异步处理
• 使用 UPDATE ... SET counter = counter + 1（原子操作）

7️⃣ 使用合适的锁类型

• 读多写少：用 MVCC（快照读）

  SELECT * FROM t;  -- 不加锁
  

• 必须加锁时：

  SELECT * FROM t WHERE id=1 FOR UPDATE;
  

避免不必要的：

SELECT ... LOCK IN SHARE MODE

8️⃣ 设置死锁超时 & 自动回滚

数据库通常自带死锁检测：

• MySQL：自动检测并回滚代价小的事务
• Oracle / SQL Server：类似机制

可调参数（示例）：

innodb_lock_wait_timeout = 5   # 秒

应用层要：

• 捕获死锁异常
• 自动重试（指数退避）

三、应用层最佳实践（总结清单）

✅ 固定加锁顺序
✅ 事务尽量小
✅ 索引必须合理
✅ 避免长事务
✅ 减少热点竞争
✅ 捕获死锁并重试
✅ 使用合适隔离级别

四、一句话总结

关系数据库防止死锁的核心是：统一访问顺序 + 缩短加锁时间 + 合理索引 + 控制事务粒度。

如果你愿意，我可以：

• 用 MySQL / Oracle / PostgreSQL 具体举例
• 分析你实际 SQL 或业务场景中的死锁风险