PostgreSQL中如何让第二个事务排除第一个事务已选中的行？

为什么两个事务都返回第一行？ #

1. 缺少明确排序导致结果无确定性：你的查询未指定ORDER BY子句，PostgreSQL执行LIMIT 1时会优先选择物理存储中成本最低的行（通常是表的第一行），两次查询的执行计划完全一致，都会尝试获取同一行数据。
2. 默认锁等待机制：FOR UPDATE默认会等待被锁定的行释放锁。第二个事务启动后，发现第一行已被第一个事务锁定，会进入等待状态，直到第一个事务提交（pg_sleep(15)执行完毕）才会获取第一行的锁，最终返回的仍是第一行。

如何实现预期效果？ #

要让第二个事务跳过已锁定的行，直接获取下一条符合条件的记录，需做两个关键修改：

1. 添加明确的ORDER BY子句 #

必须指定排序规则，确保查询的行顺序固定可预测，避免数据库随机选择行。例如按user_name或user_age排序：

SELECT u FROM "user" u WHERE u.user_age > 10 ORDER BY user_name LIMIT 1 FOR UPDATE;

2. 使用SKIP LOCKED选项 #

默认FOR UPDATE会等待锁释放，而SKIP LOCKED会直接跳过已被其他事务锁定的行，返回下一个可用的符合条件的记录。修改后的事务SQL如下：

第一个事务：

BEGIN;
    SELECT u FROM "user" u WHERE u.user_age > 10 ORDER BY user_name LIMIT 1 FOR UPDATE;
    SELECT pg_sleep(15);
END;

第二个事务：

BEGIN;
    SELECT u FROM "user" u WHERE u.user_age > 10 ORDER BY user_name LIMIT 1 FOR UPDATE SKIP LOCKED;
END;

这样第一个事务会锁定并返回Kelvin的行，第二个事务启动后会跳过被锁定的Kelvin行，直接返回Thomas的行，无需等待第一个事务提交。

注意事项 #

• SKIP LOCKED是PostgreSQL 9.5及以上版本支持的语法，请确保你的数据库版本符合要求。
• 必须搭配ORDER BY使用，否则跳过锁定行后的结果仍无确定性。

内容的提问来源于stack exchange，提问作者Nguyễn Thánh Duy