如何避免事务中INSERT与UPDATE语句引发的InnoDB死锁问题

首先咱们先理清楚死锁的真实原因——你推测的方向有点偏差，不是第一个事务锁定table_b导致第二个无法插入，而是两个并行事务在执行UPDATE关联查询时，锁的获取顺序不一致，形成了循环等待。

具体来说，当你执行UPDATE table_a a JOIN table_b b ON ...的时候，InnoDB会根据执行计划扫描表并加锁。假设事务1先插入table_b的行B1，然后执行UPDATE时先锁定table_a的一批行，再去锁定table_b的B1行；而事务2插入table_b的行B2，执行UPDATE时先锁定table_a的另一批行，再去锁定table_b的B2行。如果此时事务1需要等待事务2持有的table_a行锁，事务2又需要等待事务1持有的table_b行锁，死锁就发生了。

接下来给你几个可行的解决方案，按优先级排序：

1. 重构SQL，避免UPDATE时关联table_b #

既然你是先插入table_b的行，那完全可以在插入后拿到这行的关键值（比如如果join_col是唯一键，直接用some_value；或者插入后获取自增主键、column_b的值），然后把UPDATE语句改成不需要JOIN的形式。

比如：

-- 第一步：插入table_b，同时获取column_b的值（假设join_col是唯一键）
INSERT INTO table_b (join_col) VALUES (some_value);
SET @b_col = (SELECT column_b FROM table_b WHERE join_col = some_value);

-- 第二步：直接更新table_a，不需要关联table_b
UPDATE table_a 
SET column_c = column_c + 1 
WHERE join_col = some_value 
  AND column_a = @b_col;

这样做的好处是，UPDATE语句不会去扫描和锁定table_b的其他行，锁的范围大大缩小，而且每个事务的锁获取顺序完全一致（先锁table_b的插入行，再锁table_a的匹配行），从根源上避免死锁。

2. 给关联字段添加合适的联合索引 #

如果必须保留JOIN的写法，一定要给table_a和table_b添加联合索引，让MySQL能快速定位到需要更新的行，减少锁的行数：

• 给table_a创建联合索引：INDEX idx_a_join_col_column_a (join_col, column_a)
• 给table_b创建联合索引：INDEX idx_b_join_col_column_b (join_col, column_b)
  索引能让UPDATE语句避免全表扫描，只锁定真正需要修改的行，降低死锁发生的概率。

3. 调整事务隔离级别为READ COMMITTED #

MySQL 5.7默认的隔离级别是REPEATABLE READ，这个级别下InnoDB会使用间隙锁来防止幻读，这会增加锁的范围。如果你的业务可以接受READ COMMITTED级别（大部分业务场景都可以），可以修改隔离级别：

-- 会话级别临时修改
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- 或者在my.cnf里全局配置
transaction-isolation = READ-COMMITTED

READ COMMITTED级别下，InnoDB不会使用间隙锁（除了外键和唯一索引的场景），锁的范围更小，能有效减少死锁。

4. 添加死锁重试机制 #

即使做了上面的优化，极端情况下还是可能出现死锁（比如高并发场景）。所以在业务代码里，你需要捕获Deadlock found的异常，然后自动重试整个事务，重试次数建议设置3-5次即可。

最后提醒一下：死锁的核心是锁的获取顺序不一致，所以只要让所有事务都按照相同的顺序获取锁，就能从根源上解决问题，上面的第一个方案就是最直接的实现方式。

内容的提问来源于stack exchange，提问作者Jacobian