PostgreSQL / 系统课时
用唯一约束与 UPSERT 保证唯一性
用唯一约束与 UPSERT 保证唯一性
并发创建同一业务对象时,真正危险的不是 SQL 写错,而是“先查不存在、再插入”之间存在时间窗口。本课把唯一约束作为最终裁决点,再用 INSERT ... ON CONFLICT 把冲突处理和返回值设计成可重复执行的原子路径。
学习目标
- 解释为什么应用层“先查后插”无法单独保证唯一性
- 使用
UNIQUE与ON CONFLICT原子处理并发创建 - 根据“创建或更新”“只创建”两种语义设计可靠返回值
让唯一约束承担最终裁决
假设两个请求几乎同时创建同一个邮箱用户。事务 A 和事务 B 都可能先执行查询,并且都看到“当前没有这行”;如果数据库没有唯一约束,随后两次 INSERT 都能成功。即使查询和插入放在事务里,READ COMMITTED 也不会自动把这段业务逻辑变成互斥区。
正确边界是把业务唯一性落到数据库:例如 UNIQUE (email)。并发事务可以同时尝试插入,但唯一索引会对相同键进行冲突仲裁;一个事务成为赢家,另一个事务等待其结果,再根据 ON CONFLICT 规则更新、跳过或报错。这样唯一性不依赖“查询时有没有看到行”。

用 ON CONFLICT 合并创建与更新
如果业务语义是“没有就创建,已有就更新允许覆盖的字段”,直接使用 UPSERT。下面示例在当前会话创建临时表,连续提交两次同一邮箱;第二次不会产生第二行,而是更新已有行并通过 RETURNING 返回最终主键。
CREATE TEMP TABLE user_account (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email text NOT NULL UNIQUE,
display_name text NOT NULL
);
INSERT INTO user_account (email, display_name)
VALUES ('alice@example.com', 'Alice')
ON CONFLICT (email) DO UPDATE
SET display_name = EXCLUDED.display_name
RETURNING id, email, display_name;
INSERT INTO user_account (email, display_name)
VALUES ('alice@example.com', 'Alice v2')
ON CONFLICT (email) DO UPDATE
SET display_name = EXCLUDED.display_name
RETURNING id, email, display_name;预期输出
CREATE TABLE
id | email | display_name
------+-------------------+-------------
<ID> | alice@example.com | Alice
(1 row)
INSERT 0 1
id | email | display_name
------+-------------------+-------------
<ID> | alice@example.com | Alice v2
(1 row)
INSERT 0 1两次结果中的 <ID> 应相同;实际 ID 值由当前会话的序列状态决定。并发执行相同语句时,失败的一方不需要捕获唯一键异常再重新拼接更新逻辑,冲突分支已经由同一条 INSERT 定义。
返回值要跟业务语义一致
DO UPDATE ... RETURNING 会返回最终插入或更新后的行,因此特别适合调用方必须拿到规范主键、版本号或最终字段的场景。不要用 PostgreSQL 系统列去猜“这次到底是插入还是更新”;那会把业务逻辑绑定到实现细节。
如果业务要求“已有记录绝不能被修改”,应改用 ON CONFLICT DO NOTHING。需要注意:发生冲突时 DO NOTHING RETURNING ... 不会返回现有行。对默认 READ COMMITTED,可以在同一事务中先执行 INSERT ... DO NOTHING RETURNING id,若没有返回,再用下一条 SELECT 读取已提交的赢家行;下一条语句会取得新的快照。
约束边界不能遗漏
UNIQUE 约束表达的键必须和业务唯一性的定义一致。默认唯一约束允许多个 NULL;如果业务把 NULL 也视为同一个值,可使用支持该语义的 NULLS NOT DISTINCT 唯一约束。若只有“有效状态”的记录需要唯一,则更适合条件唯一索引,而不是把状态判断留给应用层查询。
ON CONFLICT 也不会替你决定哪些字段可以被覆盖。对账户、订单等对象,冲突分支若盲目写入全部请求字段,可能把较新的数据覆盖成旧值。应让唯一键负责“对象身份”,让 DO UPDATE 只修改业务明确允许覆盖的列。
在 REPEATABLE READ 中,事务内后续查询不会像 READ COMMITTED 那样刷新快照,因此“DO NOTHING 后再查已有行”的两语句技巧不能按同样前提理解;需要重启事务、重试,或采用符合当前隔离级别的冲突处理方案。无论隔离级别如何,业务唯一性都应首先由数据库唯一约束保证。