PostgreSQL单条Upsert因多列唯一约束引发死锁问题排查

1. 什么是speculative token？ #

speculative token是PostgreSQL为优化Upsert（INSERT ... ON CONFLICT）并发性能引入的乐观并发控制机制。执行Upsert时，数据库会先生成一个投机性令牌，在不立即加排他锁的前提下尝试完成数据写入。如果过程中检测到唯一键冲突，这个令牌会用于后续冲突处理逻辑（比如触发DO UPDATE或抛出冲突错误）。它的核心目的是减少锁竞争，但在存在重复唯一性约束的场景下，可能因逻辑冲突引发异常。

2. 单条Upsert为何会触发deadlock？ #

单条Upsert本身不会主动引发死锁，但重复的唯一性约束/索引会改变加锁逻辑：
你的表同时存在主键（foo_id:date格式）和(foo_id, date)唯一索引，PostgreSQL处理Upsert时需要同时检查这两个约束，并对对应的索引条目加锁。并发场景下，不同事务的加锁顺序可能不一致：比如事务A先锁定主键索引的目标条目，再尝试锁定唯一索引的条目；事务B则先锁定唯一索引的条目，再尝试锁定主键索引的条目。这种循环等待的锁依赖关系，就会触发死锁。即使是单条操作，高并发下这种锁顺序的不确定性也会导致死锁频繁发生。

3. 该多列唯一约束引发问题的具体原因是什么？ #

你的主键id是foo_id:date的拼接值，本质上已经通过主键约束保证了(foo_id, date)组合的唯一性——每个foo_id+date组合只会对应一个唯一的id值。但额外添加的(foo_id, date)唯一索引，造成了冗余的唯一性约束，直接引发两个问题：

• 冲突检测逻辑重复执行：数据库需要同时检查主键和唯一索引的冲突，导致speculative token的冲突概率大幅提升，频繁抛出唯一键冲突错误；
• 锁顺序不一致引发死锁：两个唯一性约束对应不同的索引，并发事务对这两个索引的加锁顺序可能相反，形成循环等待的死锁条件；
  移除这个冗余的唯一索引后，数据库只需要处理主键约束的唯一性检查，加锁逻辑单一，不会出现锁顺序冲突，因此死锁和唯一键错误都消失了。

相关补充信息 #

表结构示例 #

CREATE TABLE table_a (
    id VARCHAR PRIMARY KEY, -- 格式如 '123:2024-05-20'
    foo_id INT NOT NULL,
    date DATE NOT NULL,
    -- 其他业务字段
    CONSTRAINT index_foo_date UNIQUE (foo_id, date)
);

典型错误日志 #

ERROR: deadlock detected
DETAIL: Process 1234 waits for ShareLock on transaction 5678; blocked by process 5678.
Process 5678 waits for ShareLock on transaction 1234; blocked by process 1234.
CONTEXT: while inserting tuple (0,12) in relation "table_a"

ERROR: duplicate key value violates unique constraint "index_foo_date"
DETAIL: Key (foo_id, date)=(123, 2024-05-20) already exists.

本地测试情况 #

• 保留index_foo_date时，并发执行单条Upsert操作，10次测试中有6次出现死锁或唯一键冲突；
• 移除index_foo_date后，相同并发量下执行Upsert，无任何错误发生。

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