PostgreSQL多唯一索引下UPSERT并发引发唯一约束冲突问题

问题场景 #

我在PostgreSQL 11中有一张examples表，结构和索引如下：

| Column  | Type 
| id      | uuid
| core_id | character varying(255)
| name    | character varying(255)

Indexes: 
  "examples_core_id_pkey" PRIMARY KEY, btree (core_id)
  "examples_core_id_key" UNIQUE CONSTRAINT, btree (core_id)
  "examples_id_unique" UNIQUE CONSTRAINT, btree (id)

执行以下SQL实现插入/更新的幂等操作：

INSERT INTO examples
( id, core_id, name)
VALUES
( $id, $coreId, $name) 
ON CONFLICT (core_id)
DO UPDATE 
SET 
  name = $name  

当并发执行两条完全相同的插入操作（('abc', 'abc', 'somename')）时，偶尔会触发SequelizeUniqueConstraint错误：

duplicate key value violates unique constraint examples_id_unique

原本预期ON CONFLICT (core_id)会处理所有冲突，但实际在并发场景下却触发了id的唯一约束错误，重试后操作成功。

问题原因 #

PostgreSQL的ON CONFLICT子句仅会处理你明确指定的仲裁约束/索引对应的冲突。在这个场景中，我们只指定了core_id作为冲突目标，因此只有当core_id的唯一约束被违反时，才会执行DO UPDATE逻辑。

并发插入时的错误时序：

• 两条插入请求同时进入数据库，此时表中还没有该core_id的记录，两者都通过了core_id的冲突检查，进入插入阶段。
• 第一条请求成功插入记录并提交事务。
• 第二条请求尝试插入时，同时触发了core_id和id的唯一约束冲突，但由于id的约束不在ON CONFLICT的处理范围内，PostgreSQL会直接抛出examples_id_unique的错误，而非执行更新。

解决方案 #

1. 扩展ON CONFLICT的冲突目标 #

将id也加入冲突目标，确保任何唯一约束冲突都能触发更新：

INSERT INTO examples (id, core_id, name)
VALUES ($id, $coreId, $name) 
ON CONFLICT (core_id, id)
DO UPDATE SET name = $name

这种方式能覆盖core_id或id的冲突场景，适合业务上core_id与id绑定的情况。

2. 由数据库自动生成id #

如果业务允许，修改id字段为数据库自动生成UUID，避免客户端指定重复值：

-- 先确保安装uuid扩展（若未安装）
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

ALTER TABLE examples ALTER COLUMN id SET DEFAULT uuid_generate_v4();

之后插入时无需指定id，由数据库自动生成，从根源上消除id重复的可能。

3. 应用层加锁实现幂等 #

在插入前先查询并锁定对应core_id的记录，避免并发插入：

SELECT id FROM examples WHERE core_id = $coreId FOR UPDATE;
-- 根据查询结果，若存在则执行UPDATE，不存在则执行INSERT

这种方式需要额外的查询操作，性能略低于INSERT ... ON CONFLICT，适合并发量较低的场景。

4. 捕获错误重试 #

在应用层捕获SequelizeUniqueConstraint错误，针对examples_id_unique的约束冲突进行有限次数的重试。这种方式无需修改SQL或表结构，适合快速解决问题的场景。

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