什么是SQL死锁？

当两个或多个数据库会话相互等待对方释放资源时，就会发生死锁。想象一下这样的场景：事务A锁定了表1并试图访问表2，同时事务B锁定了表2并试图访问表1，两者都在等待对方释放资源，形成了典型的"互相等待"僵局。

在数据库系统中，死锁会导致事务无限期挂起，严重影响系统性能和用户体验。特别是对于高并发系统，死锁问题更为常见且危害更大。

死锁的四大检测与预防策略

1. 设置合理的锁超时时间

原理分析：
通过设置锁等待超时参数，当事务等待锁的时间超过指定阈值时，系统会自动回滚该事务，释放其持有的所有锁，从而打破死锁循环。

实现方法：sql
-- MySQL设置锁等待超时为5秒
SET innodblockwait_timeout = 5;

-- SQL Server设置锁超时时间为2秒
SET LOCK_TIMEOUT 2000;

-- Oracle设置锁等待超时
ALTER SYSTEM SET resourcelimit=TRUE SCOPE=BOTH; ALTER PROFILE DEFAULT LIMIT IDLETIME 10;

最佳实践建议：
- 根据业务特点设置合理的超时时间，通常5-10秒为宜
- 对于关键业务操作，可适当延长超时时间
- 实现重试机制处理超时事务

2. 统一资源访问顺序

原理分析：
强制所有事务按照预定的顺序访问表和行，消除循环等待的可能性。这是预防死锁最有效的方法之一。

实现案例：
假设系统中有订单表(orders)和订单明细表(orderdetails)，规定所有事务必须先锁定orders表再锁定orderdetails表。

sql
-- 正确的事务顺序
BEGIN TRANSACTION;
SELECT * FROM orders WHERE orderid = 100 FOR UPDATE; SELECT * FROM orderdetails WHERE order_id = 100 FOR UPDATE;
-- 业务逻辑处理
COMMIT;

-- 错误的事务顺序(可能导致死锁)
BEGIN TRANSACTION;
SELECT * FROM orderdetails WHERE orderid = 100 FOR UPDATE;
SELECT * FROM orders WHERE order_id = 100 FOR UPDATE;
-- 业务逻辑处理
COMMIT;

注意事项：
- 需要在整个项目中贯彻一致的访问顺序
- 新增表时需要更新全局访问顺序规范
- 适用于表数量较少的中小型系统

3. 降低事务隔离级别

原理分析：
较高的隔离级别(如可序列化)会增加锁的持有时间和范围，从而增加死锁概率。适当降低隔离级别可以减少锁冲突。

隔离级别对比：
1. 读未提交(Read Uncommitted) - 无锁，性能最高但一致性最差
2. 读已提交(Read Committed) - 默认级别，平衡性能与一致性
3. 可重复读(Repeatable Read) - 保证同一事务内读取一致性
4. 可序列化(Serializable) - 完全隔离，性能最差

调整方法：sql
-- MySQL设置事务隔离级别
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- SQL Server设置隔离级别
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

适用场景：
- 对数据一致性要求不高的查询操作
- 报表类、统计分析等非关键业务
- 读多写少的应用场景

4. 使用死锁检测与自动处理机制

现代数据库的死锁处理：
主流数据库系统都内置了死锁检测机制，通常基于等待图(wait-for graph)算法定期扫描。

各数据库的死锁处理差异：
- MySQL InnoDB：自动检测并回滚代价最小的事务
- SQL Server：周期性检测，选择"牺牲者"回滚
- Oracle：自动检测，提供详细的死锁跟踪信息
- PostgreSQL：通过锁超时处理，没有内置死锁自动解决

死锁日志分析示例(MySQL)：

LATEST DETECTED DEADLOCK

2023-05-20 14:23:17 0x7f8e1418a700
*** (1) TRANSACTION:
TRANSACTION 123456, ACTIVE 5 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 10, OS thread handle 12345, query id 100 localhost root updating
UPDATE accounts SET balance = balance - 100 WHERE id = 1

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 100 page no 3 n bits 72 index PRIMARY of table test.accounts trx id 123456 lock_mode X locks rec but not gap waiting

*** (2) TRANSACTION:
TRANSACTION 123457, ACTIVE 3 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 11, OS thread handle 12346, query id 101 localhost root updating
UPDATE accounts SET balance = balance + 100 WHERE id = 2

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 100 page no 3 n bits 72 index PRIMARY of table test.accounts trx id 123457 lock_mode X locks rec but not gap

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 100 page no 4 n bits 72 index PRIMARY of table test.accounts trx id 123457 lock_mode X locks rec but not gap waiting

*** WE ROLL BACK TRANSACTION (1)

高级死锁预防技巧

1. 减少事务粒度：

   
   
   
   • 将大事务拆分为多个小事务
   • 避免在事务中包含用户交互
   • 减少单个事务涉及的资源数量

2. 使用乐观锁替代悲观锁：
   sql -- 乐观锁实现示例 UPDATE products SET stock = stock - 1, version = version + 1 WHERE product_id = 100 AND version = 5;

3. 合理设计索引：

   
   
   
   • 良好的索引可以减少锁范围
   • 避免全表扫描导致的锁升级
   • 使用覆盖索引减少锁争用

4. 应用层重试机制：
   python def execute_with_retry(sql, max_retries=3): for attempt in range(max_retries): try: return execute_sql(sql) except DeadlockError: if attempt == max_retries - 1: raise sleep(random.uniform(0.1, 0.5))

总结与最佳实践

死锁是数据库并发控制中不可避免的现象，但通过合理的策略可以显著降低其发生概率和影响。综合来看：

1. 对于新系统，统一资源访问顺序是最有效的预防措施
2. 对于现有系统，设置合理的锁超时和隔离级别是较易实施的方案
3. 定期分析死锁日志，识别并解决重复出现的死锁模式
4. 结合业务特点，选择合适的锁策略(悲观锁/乐观锁)

记住，完全消除死锁通常是不现实的，我们的目标是将其控制在可接受范围内，确保系统稳定高效运行。通过持续监控和优化，可以构建出既高性能又可靠的数据访问层。