PostgreSQL并发UPSERT触发唯一约束冲突问题排查

在高并发Go代码中执行PostgreSQL的UPSERT操作时，服务负载较高会出现duplicate key value violates unique constraint错误。

表结构定义 #

创建test_table表，包含id主键和id_secondary唯一键（作为第二标识），SQL如下：

CREATE TABLE "test_table" (
    "id" text NOT NULL,
    "id_secondary" text NOT NULL,
    "value" bool NOT NULL DEFAULT 'FALSE',
    PRIMARY KEY ("id")
);
CREATE UNIQUE INDEX "test_table_uc" ON "public"."test_table" USING BTREE ("id_secondary");

并发执行的UPSERT语句 #

同时执行以下两条语句：

INSERT INTO "test_table" ("id", "id_secondary", "value") VALUES('1', '1', 'false') 
ON CONFLICT ("id")
  DO UPDATE
  SET "value" = "excluded"."value";

INSERT INTO "test_table" ("id", "id_secondary", "value") VALUES('1', '1', 'true') 
ON CONFLICT ("id")
  DO UPDATE
  SET "value" = "excluded"."value";

预期最后执行的语句会覆盖value字段，但偶尔会收到duplicate key value violates unique constraint "test_table_uc"错误。

Go并发测试代码（使用testify） #

suite.Run("Concurrency", func() {
    db, err := sql.Open("postgres", "host=localhost port=35432 dbname=test sslmode=disable user=postgres")
    db.SetMaxOpenConns(10)
    suite.Require().NoError(err)

    for ii := 0; ii < 100; ii++ {
        wg := sync.WaitGroup{}
        wg.Add(2)

        go func() {
            _, err := db.Exec(`
                    INSERT INTO "test_table" ("id", "id_secondary", "value") VALUES($1, $2, 'false') 
                    ON CONFLICT ("id")
                        DO UPDATE
                            SET
                                "value" = "excluded"."value"
                `, ii, ii)
            suite.Require().NoError(err)
            wg.Done()
        }()

        go func() {
            _, err := db.Exec(`
                    INSERT INTO "test_table" ("id", "id_secondary", "value") VALUES($1, $2, 'true') 
                    ON CONFLICT ("id") 
                        DO UPDATE
                            SET
                                "value" = "excluded"."value"
                `, ii, ii)
            suite.Require().NoError(err)
            wg.Done()
        }()
        wg.Wait()
    }
})

测试说明：通过db.SetMaxOpenConns(10)允许并发SQL连接，用Go协程同时执行两条SQL语句，每次循环仅运行两条语句，id和id_secondary设为循环迭代值，等待两条语句返回后进入下一次循环，通常几次循环后就会触发错误。

环境信息：使用默认的read committed隔离级别，PostgreSQL版本为9.6.24。

疑问与文档参考 #

根据PostgreSQL文档说明：

ON CONFLICT DO UPDATE guarantees an atomic INSERT or UPDATE outcome; provided there is no independent error, one of those two outcomes is guaranteed, even under high concurrency. This is also known as UPSERT — “UPDATE or INSERT”.

我理解这意味着INSERT或UPDATE是原子执行的，不会出现写入相同主键数据的情况。但在示例中，若test_table_uc约束被违反，主键唯一性约束也应被破坏，这让我十分困惑，请问我忽略了什么？

问题原因与解决方案 #

核心原因 #

你的ON CONFLICT子句只指定了**主键id**作为冲突检测的目标，但表中还存在另一个唯一约束test_table_uc（基于id_secondary）。在高并发场景下，两个事务可能同时尝试插入相同id_secondary的记录：

1. 事务A和事务B都检查主键id，此时表中无该记录，都进入INSERT阶段；
2. 事务A先完成INSERT，持有主键和唯一键的锁；
3. 事务B尝试INSERT时，首先检测到主键id冲突，准备执行DO UPDATE；
4. 但此时事务B还会检查id_secondary的唯一约束，由于事务A已经插入了相同的id_secondary值，而事务B的INSERT操作在进入UPDATE分支前，并未针对id_secondary的约束做冲突处理，因此触发唯一键冲突错误。

简单来说：ON CONFLICT只处理了你指定的主键冲突，没覆盖其他唯一约束的冲突场景。

解决方案 #

有两种可行的修复方式：

1. 扩展ON CONFLICT的冲突目标，包含所有唯一约束
   修改UPSERT语句，将ON CONFLICT的目标改为覆盖主键和唯一键，或者直接使用ON CONFLICT ON CONSTRAINT指定需要处理的约束。比如：

   INSERT INTO "test_table" ("id", "id_secondary", "value") VALUES($1, $2, $3)
   ON CONFLICT ("id", "id_secondary")
     DO UPDATE SET "value" = "excluded"."value";
   

   或者明确指定唯一约束名称：

   INSERT INTO "test_table" ("id", "id_secondary", "value") VALUES($1, $2, $3)
   ON CONFLICT ON CONSTRAINT test_table_uc
     DO UPDATE SET "value" = "excluded"."value";
   

   由于你的业务逻辑中id和id_secondary是一一对应的，针对其中一个约束处理冲突即可覆盖另一个。

2. 使用行级锁提前抢占资源
   在执行UPSERT前，先通过SELECT ... FOR UPDATE锁定对应的行（如果存在），避免并发事务同时进入INSERT阶段。例如：

   // 先尝试锁定行
   _, err := db.Exec(`SELECT 1 FROM "test_table" WHERE "id" = $1 FOR UPDATE`, ii)
   if err != nil {
       // 处理错误
   }
   // 再执行UPSERT
   _, err = db.Exec(`INSERT INTO "test_table" ("id", "id_secondary", "value") VALUES($1, $2, $3) ON CONFLICT ("id") DO UPDATE SET "value" = "excluded"."value"`, ii, ii, 'false')
   

   这种方式会增加额外的查询开销，但能避免跨约束的冲突问题。

补充说明 #

PostgreSQL的ON CONFLICT仅会处理你明确指定的冲突目标（主键或唯一约束），其他未被指定的唯一约束冲突仍会触发错误，这是设计上的预期行为。文档中提到的“原子INSERT或UPDATE”是针对你指定的冲突目标而言，并非覆盖所有可能的约束冲突。

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