根本原因是事务隔离级别导致子查询与外层语句快照时间点不一致；需用WITH+FOR UPDATE显式物化并加锁，或临时表分步加锁，而非依赖隔离级别。
嵌套查询里 SELECT 看到的数据，为什么和外面 UPDATE 改的不是同一份？
根本原因不是语法写错了，而是事务隔离级别没对齐。默认 READ COMMITTED 下，嵌套子查询可能在不同时间点快照数据，外层语句执行时底层数据已变——尤其是涉及 JOIN、IN 或 EXISTS 的嵌套结构。
- 典型现象：
DELETE FROM t1 WHERE id IN (SELECT id FROM t2 WHERE status = 'pending')删除行数少于预期，或报duplicate key错误（因子查询结果与主语句执行时状态不一致） - MySQL 8.0+ 和 PostgreSQL 默认都用
READ COMMITTED，但子查询是否复用快照取决于优化器路径，不可靠 - 别指望加
FOR UPDATE到子查询里——多数数据库不支持在子查询中加锁（MySQL 会报错You can't specify target table for update in FROM clause）
用 WITH + FOR UPDATE 锁住中间结果（PostgreSQL / MySQL 8.0.22+）
WITH 语句能显式物化子查询结果，并允许在其上加锁，这是最直接的解法。关键在于：必须把锁加在 WITH 定义的 CTE 上，而不是嵌套在 WHERE 里的子查询。
- PostgreSQL 示例：
WITH pending_ids AS ( SELECT id FROM orders WHERE status = 'pending' FOR UPDATE ) DELETE FROM orders WHERE id IN (SELECT id FROM pending_ids);
- MySQL 8.0.22+ 才支持
FOR UPDATE在 CTE 中使用；旧版本只能退回到临时表 + 显式锁 - 注意：CTE 必须是可更新的（不能含聚合、去重、窗口函数），否则
FOR UPDATE无效
MySQL 旧版本只能靠临时表 + LOCK TABLES 或事务内显式 SELECT ... FOR UPDATE
没有 CTE 加锁能力时，得手动拆成两步：先查出 ID 集合并锁住，再执行主操作。绕不开临时表或内存表，且必须确保整个过程在同一个事务里。
- 安全写法：
BEGIN; CREATE TEMPORARY TABLE tmp_pending AS SELECT id FROM orders WHERE status = 'pending'; SELECT id FROM tmp_pending FOR UPDATE; -- 这步实际触发行锁 DELETE FROM orders WHERE id IN (SELECT id FROM tmp_pending); DROP TEMPORARY TABLE tmp_pending; COMMIT;
- 别用
INSERT INTO ... SELECT ... FOR UPDATE—— MySQL 不允许在插入目标表上同时加锁 - 如果并发高，临时表名建议加会话唯一后缀（如
tmp_pending_12345），避免冲突
REPEATABLE READ 能不能一劳永逸？
不能。它只保证事务内多次读取结果一致，但不解决“子查询和主语句是否共享同一快照”的问题——尤其当子查询被优化器重写为关联或物化时，行为不可控。
- MySQL 在
REPEATABLE READ下仍可能对子查询单独开快照（比如IN子查询被转成 semi-join） - PostgreSQL 的
REPEATABLE READ实际是可序列化快照，但子查询若引用了外部表字段，仍可能读到新数据 - 真正保险的做法永远是：显式控制快照边界（
WITH）、显式控制锁范围（FOR UPDATE）、显式控制执行顺序（分步 + 临时表）
事务一致性不是靠隔离级别自动兜底的，嵌套查询天然打破语义连续性。最容易被忽略的是：你以为锁了子查询，其实什么也没锁住——因为数据库根本没把它当一个可锁定的单元。
