嵌套查询本身不加锁，但会让优化器失去对加锁顺序的控制；其根本原因是子查询未走索引时触发全表扫描并逐行加临键锁，导致多个事务以不同顺序加锁形成环形等待。
为什么嵌套查询容易触发死锁
嵌套查询本身不加锁，但会让优化器失去对加锁顺序的控制。MySQL 5.7+ 默认可能把 IN (SELECT ...) 物化为临时表，若子查询没走索引，就会全表扫描并逐行为每一行加临键锁（Next-Key Lock）——锁住的不只是匹配行，还有间隙，范围远超业务需要。两个事务一个先扫 t2 再更新 t1，另一个反向操作，就形成环形等待。
常见错误现象：ERROR 1213 (40001): Deadlock found when trying to get lock 频繁报错，且 SHOW ENGINE INNODB STATUS\G 显示两个事务各自持有部分行锁、互相等待对方释放。
- 子查询中用函数（如
DATE(created_at)）、OR或隐式类型转换（如INT字段传字符串），都会让索引失效 -
EXPLAIN FORMAT=JSON中key为NULL或rows_examined接近表总行数，说明没走索引 - MySQL 8.0+ 支持子查询内
ORDER BY，但不解决锁顺序问题，仅影响结果排序
用 JOIN 重写 IN/EXISTS 是最有效手段
把 UPDATE t1 SET x = 1 WHERE id IN (SELECT id FROM t2 WHERE status = 'pending') 改成 JOIN，不是语法糖，是强制优化器放弃物化路径、走嵌套循环，并显式控制关联顺序和锁粒度。
正确写法示例：UPDATE t1 JOIN t2 ON t1.id = t2.id SET t1.x = 1 WHERE t2.status = 'pending'
- 必须确保
t1.id和t2.status分别有索引；更优方案是建复合索引(status, id) - 避免在
JOIN条件里用计算字段，如ON t1.id = CAST(t2.ref_id AS SIGNED)，会跳过索引 - 若
t2数据量大，加LIMIT 500分批更新，每批后COMMIT，防止单次锁住上万行
拆成两步：先查 ID 再批量更新
当 JOIN 不适用（比如子查询含 GROUP BY、窗口函数或跨库），最稳妥的方式是把逻辑拆到应用层：先执行窄查询拿到主键列表，再拼 IN 批量更新。这能彻底规避子查询物化与锁扩散风险。
- 第一步
SELECT id FROM t2 WHERE status = 'pending' ORDER BY id——ORDER BY是关键，确保所有事务按相同顺序获取 ID，收敛加锁路径 - 第二步
UPDATE t1 SET x = 1 WHERE id IN (1,2,3,...)的 ID 列表长度不能超过max_allowed_packet，超长必须分批 - 不要用
SELECT * FROM t1 WHERE id IN (SELECT ...)替代JOIN，MySQL 5.6–5.7 对这种写法优化极差，极易锁膨胀
别忽略隔离级别和事务边界
READ COMMITTED 能缩小锁范围（InnoDB 不加间隙锁），但它不能根除嵌套死锁——因为子查询物化时机可能变化，加锁顺序仍不可控。而且，如果业务依赖 REPEATABLE READ 的一致性快照（比如多次读中间状态），降级隔离级别反而引发逻辑错误。
- 仅对当前会话生效需显式设置：
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED - 事务越短越好：嵌套查询别拖进大事务，尤其避免在事务里做 HTTP 调用、文件读写或复杂计算
- 禁用子查询物化有时反而是解法：
SET optimizer_switch='materialization=off'可强制走索引嵌套循环，减少锁范围
真正难处理的，是那些锁路径被触发器、存储过程或跨分片查询隐藏起来的情况——这时候 SHOW ENGINE INNODB STATUS 里的锁信息和执行堆栈，比任何经验都管用。
