大事务中JOIN更新易锁多行,因InnoDB按执行计划顺序对多表实例加锁且驱动表不可控;拆为“查+改”后,SELECT只读快照、UPDATE按主键顺序加锁,避免死锁。

大事务里用JOIN更新为什么容易锁住一堆行?
因为JOIN在执行时,InnoDB会按执行计划顺序对**多个别名/表实例**分别加锁,而优化器选的驱动表不可控。比如UPDATE u1 JOIN u2,可能先锁u2.id=100再锁u1.id=200;另一个事务反着来,就卡死。更麻烦的是:没索引时会触发全表扫描+间隙锁,本不该锁的索引空档也被占着。
拆成“查+改”两步后锁行为怎么变?
把原来一条UPDATE ... JOIN拆成两个独立操作,核心变化是锁粒度和顺序可控了:
- 第一步只做
SELECT id FROM ... ORDER BY id,不加写锁,只读快照 - 第二步用
WHERE id IN (...)批量更新,并显式加ORDER BY id——InnoDB会按主键递增顺序加锁,彻底避免交叉等待 - 应用层能统一控制ID列表排序逻辑,不同事务不再“各走各路”
哪些JOIN场景必须拆?
不是所有JOIN都要拆,但以下情况建议立刻切分:
-
UPDATE或DELETE语句中含JOIN,且涉及高并发写入(如订单状态同步、用户积分计算) - EXPLAIN显示驱动表
type为ALL或index,说明连接列缺失索引 - WHERE条件跨两个表(如
o.created_at > ? AND u.state = 'active'),导致锁范围不可预估 - 事务里混用了
SELECT FOR UPDATE+UPDATE JOIN,读写路径不一致
拆分后要注意什么?
代价是多一次网络往返,但换来确定性加锁。真正容易被忽略的是数据一致性边界:
- 查出ID列表到执行UPDATE之间,中间数据可能被其他事务改掉——得靠业务幂等性兜底(比如
SET status = 'processed' WHERE status = 'pending') - IN列表不能太大,超1000个ID建议分批,否则SQL解析开销陡增
- 别在拆分后的UPDATE里再嵌套子查询或函数,否则
ORDER BY id失效,锁序又乱了

















