PostgreSQL中SELECT ... FOR UPDATE并发长查询的执行逻辑分析

以下逻辑基于PostgreSQL、MySQL InnoDB等主流关系型数据库的行锁实现机制，完全匹配梳理的T1、T2并发执行场景：

核心疑问解答 #

• T1对行加锁的操作是否为原子性操作？
  不存在整批结果集的原子加锁。SELECT ... FOR UPDATE的锁获取是逐行流式完成的：数据库不会等整张查询的结果集全部扫描完成再统一加锁，而是扫描到第一行匹配的记录时，就尝试对该行加排他锁，加锁完成后再继续扫描下一行。
  单行层面的锁获取是原子的：针对某一条具体的行，加锁动作是原子完成的，不会出现“锁加了一半”的中间状态。

• 若T2完成结果集检索后尝试对行加锁但无法获取锁，在不使用NOWAIT参数的情况下，T2是否会等待直到T1释放锁？
  首先需要纠正一个常见认知误区：长查询不存在“先把所有匹配行全部检索完，最后统一加锁”的执行流程，实际是边扫描匹配行、边加锁的。
  不带NOWAIT的默认行为下，只要T2扫描到某一行已经被T1持有排他锁，就会立刻在这一行的锁等待队列上阻塞，不会继续向后扫描后续的匹配行。一旦T1提交或回滚释放锁，T2会重新评估当前行是否仍满足查询条件，再决定是加锁返回还是直接跳过。
  不存在“所有匹配行全部检索完成后才发现锁被占用，再统一等待”的情况。

• 若T2在T1做出任何行变更（例如将任务状态从NEW修改为IN_PROGRESS）之前就完成了查询执行，两个事务是否会检索到完全相同的结果集？
  不会。READ COMMITTED隔离级别下不存在“整个查询执行周期共用一个数据快照”的机制，语句级快照会在每次数据访问时自动更新。
  哪怕T2扫描某一行的瞬间，T1还没提交对这行的修改，只要T2尝试对该行加锁时发现锁被占用，就会阻塞等待T1释放锁。等T1提交后，T2会读到T1提交的最新版本数据，重新校验该行是否满足WHERE条件——如果T1已经把行状态从NEW改成了IN_PROGRESS，该行会直接从结果集中被剔除。
  换句话说，哪怕T2一开始扫描时看到这行是NEW状态，只要这行被其他事务加了锁，等锁释放后T2读取的永远是提交后的最新版本，不会硬套用扫描瞬间的旧快照凑结果集。

• 若T1对已加锁的行做了状态标记（例如将状态列从NEW修改为IN_PROGRESS），而T2的查询条件为检索NEW状态的行，那么搭配SKIP LOCKED使用时，T2是否会跳过这些已被标记的行？T2是否会在T1完成变更后重新评估自身的结果集？
  搭配SKIP LOCKED的场景下，T2遇到被其他事务持有排他锁的行时，根本不会阻塞等待，会直接跳过这些锁冲突的行，继续扫描下一行。
  分两种边界场景说明：

  • 如果T1持锁的行还未提交修改：T2遇到这行的锁就直接跳过，不会等待T1提交，自然也不会等到T1修改完状态再做判断。
  • 如果T1在T2扫描到这行之前就已经提交了修改：T2直接读到的就是IN_PROGRESS状态的行，本身就不满足NEW的查询条件，会被WHERE子句正常过滤，根本走不到锁判断的逻辑。
    关于结果集重新评估：不带SKIP LOCKED的普通SELECT ... FOR UPDATE，会在锁等待结束后重新评估行是否满足查询条件；但带SKIP LOCKED的查询因为不存在等锁的过程，所以不会触发“等锁结束后重新评估”的逻辑。

针对数据库任务队列场景的实操提示：

• 避免对无索引的表写全表扫描的长耗时SELECT ... FOR UPDATE SKIP LOCKED查询，尽量在status、created_at等查询条件列上建立合适的索引，把扫描范围缩到最小，减少扫描过程中遇到锁冲突带来的延迟。
• 任务状态更新时一定要带上原状态作为过滤条件，例如使用UPDATE job SET status = 'IN_PROGRESS' WHERE id = ? AND status = 'NEW'，避免并发场景下的状态覆盖问题。

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