关联表更新导致的 DB 死锁：原理分析与解决方案

在数据库操作中，死锁是一个常见且棘手的问题，尤其在并发场景下。本文将通过一个实际案例，深入分析关联表更新操作导致 MySQL 死锁的原因，并提供切实可行的解决方案。

问题背景

近期在项目中遇到一个典型的数据库死锁问题：1段代码包含2个更新操作（均按 ID 更新表，其中一个是关联表更新），当两个线程同时调用时，频繁出现死锁。

数据库环境：MySQL 8.x

案例重现

相关 SQL 语句

会话 1：

START TRANSACTION;
update account_xxx set update_at='2025-09-11 10:38:58.311' where id = 4595  #2025-09-11 10:38:58.348
  
update account t,
        (
        select
        sum(amount) amount, project_id, payee_node nodeId
        from account_xxx
        where project_id = 990 and is_delete = 0
and cost_type in (1,2,3,4,5,6)
        and payee_node in ( 31154)
        group by project_id, payee_node
        ) t1
        set t.account_amount = t1.amount, t.update_at = now()
        where t.project_id = t1.project_id and t.org_node_id = t1.nodeId
        and t.id in (  1484)
         
commit
rollback

会话 2：

START TRANSACTION;
update account_xxx set update_at='2025-09-11 10:51:41.311' where id = 4596  #2025-09-11 10:51:41.356
 update account t,
        (
explain
        select
        sum(amount) amount, project_id, payee_node nodeId
        from account_xxx  
        where project_id = 990 and is_delete = 0
        and cost_type in (1,2,3,4,5,6)
        and payee_node in ( 31223)
        group by project_id, payee_node
) t1
        set t.account_amount = t1.amount, t.update_at = now()
        where t.project_id = t1.project_id and t.org_node_id = t1.nodeId
        and t.id in (  1736)
         
commit
rollback

死锁产生步骤

死锁都是account_xxx表互相操作导致，通过模拟执行，死锁产生过程如下：

1. 会话 1 执行第一个 SQL，持有表中 id=4595 记录的 X 排他锁
2. 会话 2 执行第一个 SQL，持有表中 id=4596 记录的 X 排他锁
3. 会话 1 执行第二个 SQL，需要获取表中 id=4595 和 4596 记录的 S 共享锁，此时因 4596 被会话 2 持有 X 锁而进入等待
4. 会话 2 执行第二个 SQL，需要获取表中 id=4595 和 4596 记录的 S 共享锁，此时因 4595 被会话 1 持有 X 锁而进入等待
5. 数据库检测到死锁，终止其中一个事务，抛出错误：1213 - Deadlock found when trying to get lock; try restarting transaction

死锁原理分析

锁竞争分析

通过查询数据库锁状态（如下 SQL），我们可以清晰看到锁的持有和等待情况：

SELECT
  -- 锁的基本信息
  p.lock_type,                -- 锁类型：TABLE（表级锁）、RECORD（行级锁）
  p.lock_mode,                -- 锁模式：S（共享锁）、X（排他锁）、IS（意向共享）、IX（意向排他）等
  p.lock_status,              -- 锁状态：GRANTED（已持有）、WAITING（等待中）
  p.lock_data,                -- 锁数据：行级锁显示锁定的行主键；表级锁为 NULL
  
  -- 关联事务信息
  t.trx_id AS 事务ID,
  t.trx_state AS 事务状态,     -- RUNNING（运行中）、LOCK WAIT（等待锁）等
  t.trx_started AS 事务开始时间,
  
  -- 关联会话/连接信息
  s.id AS 会话ID,
  s.user AS 数据库用户,
  s.host AS 客户端地址,
  s.db AS 所属数据库,
  s.command AS 会话状态,       -- Sleep、Query、Update 等
  s.time AS 会话持续时间,
  s.info AS 当前执行SQL        -- 正在执行的SQL（可能为 NULL）

FROM
  performance_schema.data_locks p
LEFT JOIN performance_schema.data_lock_waits w
  ON p.engine_transaction_id = w.requesting_engine_transaction_id
LEFT JOIN information_schema.innodb_trx t
  ON p.engine_transaction_id = t.trx_id
LEFT JOIN information_schema.processlist s
  ON t.trx_mysql_thread_id = s.id

WHERE
  p.lock_status = 'GRANTED'  -- 只显示「已持有」的锁（排除等待中的锁）
and s.db = 'xxxdb'
ORDER BY
  t.trx_started DESC, p.lock_type, p.lock_mode;

分析发现，问题的核心在于第二步的关联更新操作：

• 会话 1 已经持有 4595 的 X 锁，需要获取 4596 的 S 锁
• 会话 2 已经持有 4596 的 X 锁，需要获取 4595 的 S 锁
• 双方互相等待对方持有的锁，形成典型的循环等待，导致死锁

索引使用问题

进一步分析发现，死锁产生的深层原因是 MySQL 在执行关联查询时，因查询条件复杂，错误地选择了索引，导致锁定范围扩大：

• 原本应该只锁定各自需要的记录（会话 1 锁定 4595，会话 2 锁定 4596）
• 由于索引选择不当，两个会话在执行关联更新时都试图锁定 4595 和 4596 两条记录
• 这直接导致了锁竞争范围的扩大，增加了死锁概率

死锁日志验证

通过SHOW ENGINE INNODB STATUS命令查看死锁日志，关键内容如下：

LATEST DETECTED DEADLOCK



*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 34097 page no 169 n bits 112 index PRIMARY of table xxxdb.account_xxx trx id 3920385408 lock_mode X locks rec but not gap



*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 34097 page no 169 n bits 112 index PRIMARY of table xxxdb.account_xxx trx id 3920385408 lock mode S locks rec but not gap waiting





*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 34097 page no 169 n bits 112 index PRIMARY of table xxxdb.account_xxx trx id 3920392281 lock_mode X locks rec but not gap



*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 34097 page no 169 n bits 112 index PRIMARY of table xxxdb.account_xxx trx id 3920392281 lock mode S locks rec but not gap waiting

日志明确显示：

• 两个事务都持有一条记录的 X 排他锁
• 同时都在等待获取对方持有记录的 S 共享锁
• 这种循环等待关系正是死锁的典型特征

解决方案

针对上述问题，我们可以采取以下解决方案：

1. 强制指定索引

通过FORCE INDEX提示，强制 MySQL 使用合适的索引，精准定位需要操作的数据，减少锁定范围：

使用合适的索引后，每个事务只会锁定自己需要的记录，避免了不必要的锁竞争。

2. 避免关联表更新

尽量避免在更新操作中使用关联查询，可拆分为两步：

1. 先查询出需要的数据
2. 再进行更新操作

这种方式可以减少锁的持有时间和范围，降低死锁概率。

3. 统一锁定顺序

如果业务允许，可调整更新顺序，确保所有事务都按相同的顺序获取锁，从根本上避免循环等待：

1. 对需要更新的记录按 ID 排序
2. 按从小到大（或固定顺序）的顺序进行更新

总结

关联表更新操作容易因索引选择不当导致锁定范围扩大，在并发场景下极易引发死锁。解决这类问题的核心在于：

1. 优化索引使用，精准锁定必要的数据
2. 减少关联更新，拆分复杂操作
3. 统一锁定顺序，避免循环等待

通过本文介绍的方法，可以有效解决关联表更新导致的 MySQL 死锁问题，提高系统的并发能力和稳定性。在实际开发中，还需结合具体业务场景，选择最合适的解决方案。