MySQL技巧(八) :死锁解决与实战案例

在数据库高并发场景下，死锁是一个绕不开的经典难题。两个或多个事务相互持有对方需要的锁，导致都无法继续执行，就像两辆车在狭窄路口互不相让。本文将带你从原理到实战，掌握死锁的排查、解决和预防全流程。

一、死锁快速定位

当应用出现“Deadlock found when trying to get lock”错误时，第一时间需要通过数据库日志定位问题。

第一步：查看最近一次死锁详情

在MySQL命令行或IDE的Database Console中执行以下命令：

sql

SHOW ENGINE INNODB STATUS\G

重点关注输出中的LATEST DETECTED DEADLOCK部分，它会清晰展示：

• 发生死锁的两个事务及其SQL

• 各自持有的锁和等待的锁

• 被回滚的事务

第二步：查看当前锁等待情况

如果死锁正在发生，可以通过以下SQL实时监控：

sql

SELECT r.trx_id AS waiting_trx_id, r.trx_mysql_thread_id AS waiting_thread, r.trx_query AS waiting_query, b.trx_id AS blocking_trx_id, b.trx_mysql_thread_id AS blocking_thread, b.trx_query AS blocking_query FROM information_schema.innodb_lock_waits w INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

找到阻塞源头后，可以根据blocking_thread执行KILL操作：

sql

KILL 123; -- 替换为实际的thread_id

二、 经典死锁案例与解决方案

案例1：双事务交叉更新

这是最常见的死锁场景——两个事务以相反的顺序更新相同的两张表。

场景重现：

• 事务A：先更新订单表，再更新库存表

• 事务B：先更新库存表，再更新订单表

解决方案：统一资源访问顺序

最有效的做法是在代码层面约定所有事务都按照相同的顺序操作数据库表或行记录。

java

// ✅ 好做法：统一先处理订单，再处理库存 public void updateOrderAndStock(String orderId, String productId) { transactionTemplate.execute(status -> { ordersMapper.updateStatus(orderId, "PAID"); inventoryMapper.reduceStock(productId, 1); return null; }); } // ❌ 坏做法：不同事务顺序不一致，容易死锁

如果业务无法统一顺序，可以考虑在应用层引入分布式锁（如Redis、ZooKeeper），保证同一时间只有一个线程在处理某条关联数据。

案例2：范围查询导致的间隙锁死锁

当使用WHERE条件进行范围更新时，InnoDB会添加间隙锁，锁住条件范围内的不存在的记录，多个事务的范围条件重叠时极易死锁。

解决方案：缩小锁粒度

• 方案A：将范围更新改为单条更新

sql

-- 原来：范围更新 UPDATE user_points SET points = points + 10 WHERE user_id BETWEEN 2 AND 6; -- 改为：逐条更新（按固定顺序） UPDATE user_points SET points = points + 10 WHERE user_id = 2; UPDATE user_points SET points = points + 10 WHERE user_id = 3; -- ... 依次执行

• 方案B：使用 ORDER BY 确保加锁顺序

-- 原来：范围更新 UPDATE user_points SET points = points + 10 WHERE user_id BETWEEN 2 AND 6; -- 改为：逐条更新（按固定顺序） UPDATE user_points SET points = points + 10 WHERE user_id = 2; UPDATE user_points SET points = points + 10 WHERE user_id = 3; -- ... 依次执行

案例3：唯一键冲突导致的死锁

并发执行INSERT ... ON DUPLICATE KEY UPDATE时，如果插入相同的唯一键，两个事务会先尝试插入（加插入意向锁），检测到冲突后转为更新锁，容易形成循环等待。

解决方案：先锁定，再操作

sql

-- 使用 SELECT ... FOR UPDATE 显式锁定行 BEGIN; SELECT * FROM user_account WHERE mobile = '13800138000' FOR UPDATE; IF found THEN UPDATE user_account SET balance = balance + 100 WHERE mobile = '13800138000'; ELSE INSERT INTO user_account (mobile, balance) VALUES ('13800138000', 100); END IF; COMMIT;

或者将唯一键冲突的业务逻辑异步化，通过消息队列串行处理，彻底避免并发冲突。

三、 预防死锁的最佳实践

死锁无法100%消除，但可以通过以下实践大幅降低发生概率。

1. 事务设计原则

• 短事务：尽量减少事务中SQL的数量，不要在事务中执行远程调用、复杂计算等耗时操作

• 低隔离级别：如果业务允许，使用READ COMMITTED代替默认的REPEATABLE READ，减少间隙锁

  sql

  SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

• 精确更新：更新语句尽量使用主键或唯一索引作为条件，避免全表扫描或大范围锁表

2. 索引优化

确保UPDATE和DELETE语句的WHERE条件使用了索引，否则行锁可能升级为表锁，极大增加死锁概率。

sql

-- 检查SQL执行计划，确保 type 为 ref 或 eq_ref，避免 ALL EXPLAIN UPDATE order_detail SET status = 1 WHERE order_no = 'ORD123456';

如果索引未命中，应添加合适的复合索引：

sql

ALTER TABLE order_detail ADD INDEX idx_order_no_status (order_no, status);

3. 重试机制

即使做好了预防，死锁在高并发下仍可能偶发。业务代码中应实现死锁重试机制，让被回滚的事务自动重试。

python

# Python示例：带重试的数据库操作 import time from functools import wraps def retry_on_deadlock(max_retries=3, delay=0.1): def decorator(func): @wraps(func) def wrapper(*args, **kwargs): for attempt in range(max_retries): try: return func(*args, **kwargs) except Exception as e: if 'Deadlock found' in str(e) and attempt < max_retries - 1: time.sleep(delay * (2 ** attempt)) # 指数退避 continue raise return None return wrapper return decorator @retry_on_deadlock(max_retries=3) def update_order_status(order_id, status): with db.transaction(): db.execute("UPDATE orders SET status = %s WHERE id = %s", (status, order_id))

四、 总结与快速排查清单

当出现死锁时，可以按以下步骤快速排查和处理：

核心原则：死锁是并发场景的正常现象，关键在于快速发现、分析原因和优雅重试。通过合理的索引设计、统一的事务顺序和健全的重试机制，可以将死锁的影响降到最低。