Postgres中带SELECT的WITH UPDATE能否规避竞态条件？

你的CTE方案已经安全可靠，无需多语句事务 #

你当前的CTE查询完全能避免竞态条件，根本不需要额外的多语句事务——Postgres里单个SQL语句本身就是原子事务，整个操作要么完全执行成功，要么完全回滚，不会出现中间状态。

其中FOR UPDATE子句是关键：它会在CTE的SELECT阶段就锁定选中的那一行，其他并发执行的请求会被阻塞，直到当前语句执行完毕、锁被释放。等其他请求拿到锁再执行查询时，该行的handler_server已经被设置为非NULL值，自然不会被再次选中，完美避免了多个容器同时抢占同一行的问题，刚好适配你PDO不能多语句绑定参数的限制。

子查询形式的UPDATE方案同样安全 #

如果你想改用子查询写法，只要保留行锁逻辑，同样是安全的，示例如下：

UPDATE request
SET status = :new_status, handler_server = :handler_server
WHERE id = (
    SELECT id
    FROM request
    WHERE handler_server IS NULL AND status = :old_status
    ORDER BY created_at ASC
    LIMIT 1
    FOR UPDATE
)

如果你的业务场景允许跳过已被锁定的行（不想让请求等待锁释放），可以把FOR UPDATE改成FOR UPDATE SKIP LOCKED，这样并发请求会直接跳过被其他容器锁定的行，去抢占下一个符合条件的行，适合高并发场景下提升吞吐量。

不管是CTE还是子查询写法，只要是单语句+行锁的组合，都能确保原子性，彻底解决竞态问题，不用纠结多语句事务的问题。

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