嵌套查询本身不加锁，死锁真正来自子查询执行时机引发的行锁顺序混乱；两个事务对同一组主键按不同路径加锁，形成AB-BA循环等待。
嵌套查询本身不加锁，死锁真正来自子查询执行时机引发的行锁顺序混乱——两个事务对同一组主键按不同路径加锁，形成 AB-BA 循环等待。
IN/EXISTS 子查询为什么在 UPDATE 中最危险
这类语句让优化器有极大自由度选择执行路径：可能先扫 t2 加锁再回表 t1，也可能先锁部分 t1 行再查 t2。一旦事务 A 和 B 走了相反路径，就卡死。
- 常见错误现象：
Deadlock found when trying to get lock，但EXPLAIN显示子查询走索引，仍死锁 - 根本原因：MySQL 5.7+ 默认启用
materialization，子查询结果落地为临时表，加锁分两批进行，顺序脱离主键控制 - 如果子查询含范围条件（如
WHERE created_at > '2026-01-01'），在REPEATABLE READ下还会触发间隙锁，锁住整个索引区间 - ORM 自动生成的子查询往往没
ORDER BY，导致每次 IN 列表内 ID 顺序随机，底层行锁获取顺序不可预测
相关子查询（correlated subquery）如何偷偷锁全表
像 SELECT * FROM orders o1 WHERE o1.id = (SELECT id FROM orders o2 WHERE o2.user_id = o1.user_id ORDER BY created_at DESC LIMIT 1) 这种写法，在 MySQL 5.7 下无法下推 LIMIT，会为每一行 o1 重新执行一次子查询，且无法利用索引高效定位——最终变成对整张 orders 表反复扫描。
- 即使只取 1 行，也可能对 10 万行加临键锁（next-key lock）
- 锁不是语法加的，是物化行为 + 全表扫描共同导致的；锁持续到整个外层语句结束才释放
-
EXISTS里没索引的关联字段（如o2.user_id = o1.user_id）会让驱动表每行都触发一次全表扫描，锁住大量无关行 - PostgreSQL 对
EXISTS优化更好，倾向用 Anti Join，但若递归 CTE 没终止条件，仍可能反复锁定同一行
怎么验证是不是子查询在搞鬼
别猜，直接看 SHOW ENGINE INNODB STATUS\G 的 LATEST DETECTED DEADLOCK 区块：
- 重点比对两个事务的
HOLDING和WAITING FOR部分：若一个事务HOLDING RECORD LOCKS on index `idx_status` of table `t2`，同时WAITING FOR RECORD LOCKS on PRIMARY of table `t1`，另一个反过来，基本可断定是子查询跨表加锁顺序冲突 - 注意看锁发生在哪个 index 上——死锁常发生在二级索引（如
t2.status），而非主表主键；若看到GAP LOCK或NEXT KEY LOCK出现在子查询涉及的索引上，说明范围条件+RR 隔离级正在放大锁粒度 - 执行前加
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED再试一次：如果死锁消失，大概率是间隙锁惹的祸
JOIN 重写不是银弹，但能控住加锁顺序
把 UPDATE t1 SET x=1 WHERE id IN (SELECT id FROM t2 WHERE status=1) 改成 UPDATE t1 JOIN t2 ON t1.id = t2.id SET t1.x = 1 WHERE t2.status = 1，关键不在语法变短，而在强制优化器以 t2 为主驱动表、按其主键顺序逐行访问。
- 必须确保
t2.status有索引，且t1.id是主键或唯一键，否则 JOIN 可能退化为全表匹配，锁更散 - 禁用物化仅作调试用：
SET optimizer_switch='materialization=off'，它可能让优化器改走半连接（semi-join），减少中间锁，但不保证稳定 - 最稳妥的拆法：先
SELECT id FROM t2 WHERE status=1 ORDER BY id拿到有序 ID 列表，再拼进UPDATE ... WHERE id IN (...)——这样外层一定按主键升序加锁，顺序可预期 - WITH 在 MySQL 8.0+ 中默认非物化，但写错照样锁全表：比如
WITH tmp AS (SELECT * FROM orders)再外层过滤，等于提前锁住所有行
真正难处理的不是语法怎么写，而是子查询里藏着的隐式排序、隐式物化、隐式间隙锁——这些行为在单次执行时看不出问题，一到并发就暴露无遗。线上排查时，优先盯死 SHOW ENGINE INNODB STATUS 里的锁资源归属，而不是盯着 SQL 看“逻辑对不对”。
