PostgreSQL中替代带sequence序列的ON CONFLICT DO NOTHING的实现方案

核心疑问解答 #

• 同一条查询语句中两次调用nextval是否会返回不同值？
  是的，PostgreSQL中每一次nextval调用都会触发序列自增，你示例中SELECT列表和WHERE子句的两次nextval会返回两个连续的不同值，完全无法实现id存在性校验的逻辑，该写法不可用。
• 使用currval能否取到当前语句生成的序列值？并发调用是否会冲突？
  currval('hibernate_sequence')返回的是当前数据库会话中最近一次nextval调用生成的序列值，完全不受其他会话的序列操作影响，多个并发查询同时执行nextval也不会出现取值冲突，不会拿到其他会话生成的序列值。

正确改写实现 #

方案1：子查询预生成序列值 + WHERE NOT EXISTS（低冲突场景适用） #

该方案无需加锁，只有在极高并发下才会出现极小概率的唯一约束报错，适合冲突率低的业务场景：

INSERT INTO grade(id, name, group_id) 
SELECT t.id, t.name, ? 
FROM (
    SELECT nextval('hibernate_sequence') as id, name 
    FROM grade 
    WHERE group_id = ?
) t
WHERE NOT EXISTS (
    SELECT 1 FROM grade g1 WHERE g1.id = t.id
);

将nextval的生成逻辑放在内层子查询，外层直接用预生成的固定id做存在性校验，避免多次调用nextval导致的取值不一致问题。

方案2：表级排他锁 + 插入逻辑（高并发场景适用） #

如果需要完全避免并发插入导致的唯一约束报错，可以先对表加排他锁阻塞其他写入操作，再执行插入：

BEGIN;
LOCK TABLE grade IN EXCLUSIVE MODE;
INSERT INTO grade(id, name, group_id) 
SELECT t.id, t.name, ? 
FROM (
    SELECT nextval('hibernate_sequence') as id, name 
    FROM grade 
    WHERE group_id = ?
) t
WHERE NOT EXISTS (
    SELECT 1 FROM grade g1 WHERE g1.id = t.id
);
COMMIT;

补充说明 #

正常使用序列的场景下，nextval生成的值不会和表中已有id重复，只有当你手动修改过序列起始值、或者手动插入过大于序列当前值的id记录时，才需要做上述存在性校验。

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