如何在嵌套查询中使用FOR UPDATE？场景及锁位置疑问

场景回顾 #

给定查询语句用于筛选可执行7号任务的空闲工人：

SELECT * FROM WORKER,
   (
       SELECT worker_id, time_to_work FROM JOB 
       WHERE job_id = 7 AND time_to_work < now() 
   ) AS work
   WHERE WORKER.worker_id = work.worker_id AND WORKER.occupied = 0

后续需执行两项操作：

• 将选中工人的occupied字段设为1；
• 将这些工人对应的7号任务的time_to_work设置为24小时后。

核心问题：未加FOR UPDATE时，并发事务可能重复分配同一工人。

1. 应当将FOR UPDATE放置在何处？ #

应将FOR UPDATE添加到外层查询末尾，同时建议调整查询结构为显式JOIN，确保同时锁定WORKER和JOB表中符合条件的目标行。示例：

SELECT w.*, j.time_to_work
FROM WORKER w
JOIN JOB j ON w.worker_id = j.worker_id
WHERE j.job_id = 7 AND j.time_to_work < now() AND w.occupied = 0
FOR UPDATE;

这样会直接锁定WORKER表中occupied=0的目标工人行，以及JOB表中对应7号任务的行，避免其他事务修改这些数据。

2. 将FOR UPDATE放在子查询中是否能解决问题？ #

不能。子查询中的FOR UPDATE仅会锁定JOB表的行，但WORKER表的目标行未被锁定。其他事务仍可修改WORKER的occupied字段，导致同一工人被重复分配给不同客户端。

3. 将FOR UPDATE放在外层查询或同时放在两层是否会引发死锁？ #

• 仅外层加FOR UPDATE：只要所有事务遵循相同的执行顺序（先执行该查询锁定行，再执行后续更新操作），一般不会引发死锁。因为事务会按统一顺序锁定WORKER和JOB的行，不会出现循环等待的情况。
• 同时在两层加FOR UPDATE：完全没必要，反而会增加死锁风险。子查询先锁定JOB行，外层再锁定WORKER行；若另一个事务先锁定WORKER行再尝试锁定JOB行，就会形成循环等待，触发死锁。

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