PostgreSQL UPSERT性能差还频繁死锁？深入解析ON CONFLICT的锁行为与优化方案

UPSERT是PostgreSQL中非常实用的写法，一条语句同时完成“存在则更新、不存在则插入”的逻辑，替代了先查询再判断的传统两步操作。PostgreSQL从9.5版本开始引入INSERT ... ON CONFLICT语法，很多团队上线后却发现：低并发测试一切正常，一到生产环境高并发写入，就出现锁等待堆积、死锁报错、TPS骤降等问题。问题的根源不在于语法本身，而在于ON CONFLICT背后的锁行为和索引检测机制没有被正确理解。这篇文章从执行原理讲起，把锁的获取顺序、常见死锁场景、性能优化手段逐一拆开分析。

ON CONFLICT的执行原理：推测性插入与索引检测

理解UPSERT的锁行为，必须先弄清楚它是如何执行的。PostgreSQL对ON CONFLICT的处理采用了一种称为“推测性插入”（speculative insertion）的机制。执行器首先按照普通INSERT的流程，构造一条新元组并尝试插入堆表，同时在这条元组上打一个特殊标记，表示这是一次“试探性”的插入。

接下来，数据库会在指定的唯一索引上做检测。如果发现已存在冲突的键值，推测性插入会被中止，当前事务转而对那条已存在的行加行锁，然后根据DO UPDATE或DO NOTHING执行后续逻辑。如果没有冲突，推测性插入会被“转正”，变成一次真正的插入。这个设计避免了使用显式锁或Serializable隔离级别，让UPSERT在普通的Read Committed隔离级别下就能保证正确性。

但这里有一个非常关键的细节：冲突检测是针对唯一索引进行的，而不是针对逻辑上的“主键概念”。你在ON CONFLICT子句中指定的冲突目标必须是一个唯一索引或唯一约束，PostgreSQL会拿着这个索引去做检测。如果表上存在多个唯一索引，而写入的数据在不同索引上都有冲突可能，锁的竞争情况会明显恶化，这也是很多性能问题的源头。

DO UPDATE与DO NOTHING的锁行为差异

DO NOTHING并非“什么都不锁”

很多开发者误以为ON CONFLICT DO NOTHING完全不涉及锁，这是不准确的。当检测到冲突时，DO NOTHING同样需要先对冲突行加锁，确认这条行没有被其他事务修改后，才能安全地跳过。它只是跳过了更新动作，锁的获取流程和DO UPDATE基本一致。区别在于DO NOTHING拿到锁后立即释放更新意图，整体持锁时间更短，锁竞争的概率相对低一些，但绝不是零。

DO UPDATE持锁更久且可能触发锁升级排队

DO UPDATE在冲突时会对目标行加行级锁（ROW EXCLUSIVE级别），然后执行更新操作，更新本身还会写新的元组版本、维护索引。整个过程都在当前事务内，直到事务提交或回滚，行锁才释放。这意味着：如果你的事务里UPSERT之后还有其他耗时操作，冲突行的锁会被一直持有，其他并发碰到同一行的UPSERT就会排队等待。一个典型错误是把UPSERT和一堆远程调用、批量计算放在同一个大事务里，高并发下锁等待链条迅速变长，最终表现为数据库整体卡顿。

下面用一个简单例子说明两种写法的锁差异：

-- 模拟两张表的用户余额更新
-- DO UPDATE：冲突行会被加行锁，直到事务结束
INSERT INTO user_balance(user_id, balance)
VALUES (1001, 50)
ON CONFLICT (user_id)
DO UPDATE SET balance = user_balance.balance + EXCLUDED.balance;

-- DO NOTHING：同样会短暂加锁检测，但不执行更新
INSERT INTO user_balance(user_id, balance)
VALUES (1001, 50)
ON CONFLICT (user_id)
DO NOTHING;

高并发下死锁的典型场景与规避方法

UPSERT死锁最经典的场景是批量写入时的顺序不一致。假设事务A要UPSERT用户1和用户2，事务B要UPSERT用户2和用户1。A先锁住用户1，B先锁住用户2，随后A去请求用户2的锁、B去请求用户1的锁，双方互等，PostgreSQL检测到死锁后杀掉其中一个事务，抛出deadlock detected错误。

再看一个更隐蔽的场景：多行UPSERT与单行UPDATE混用。事务A批量UPSERT了id为1到100的行，事务B用UPDATE修改了id为50的行并持有锁，接着A执行到id为50时被B阻塞，而B后面又要更新id为30（已被A锁住），死锁形成。这类问题在消息队列消费、批量同步任务中特别常见。

规避死锁的核心手段是让所有事务以一致的顺序访问行：

-- 错误做法：values顺序随业务数据变化，锁顺序不可控
INSERT INTO stock(sku_id, qty) VALUES
  (30, 1), (10, 1), (50, 1)
ON CONFLICT (sku_id) DO UPDATE SET qty = stock.qty + EXCLUDED.qty;

-- 正确做法：先按冲突键排序，保证所有事务加锁顺序一致
INSERT INTO stock(sku_id, qty)
SELECT sku_id, qty FROM (
  VALUES (30, 1), (10, 1), (50, 1)
) AS t(sku_id, qty)
ORDER BY t.sku_id
ON CONFLICT (sku_id) DO UPDATE SET qty = stock.qty + EXCLUDED.qty;

除了排序之外，还有几个配合手段：第一，应用层对deadlock错误（SQLSTATE为40P01）实现自动重试，重试前加一个小的随机延迟，避免多个事务同节奏竞争；第二，把大事务拆小，单次UPSERT的批量控制在几百到几千行之间，缩短单把锁的持有时长；第三，避免在同一个事务里混合操作不同粒度的数据，比如既有全表UPDATE又有按行的UPSERT。

性能优化：索引设计、批量策略与参数调优

确认执行计划走了索引

UPSERT的冲突检测依赖唯一索引，如果执行计划显示走了顺序扫描，性能会灾难性下降。用EXPLAIN (ANALYZE, BUFFERS)检查UPSERT语句，正常情况下Insert节点之上会有一个Conflict Detection相关的索引扫描节点。如果发现Seq Scan，通常是冲突目标指定的索引与实际查询条件不匹配，或者统计信息过期，需要及时重建索引或执行ANALYZE。

控制单批大小与减少索引数量

批量UPSERT不是越大越好。批次过大时，单事务持有的锁数量多、时间长，死锁概率上升，WAL日志也会集中爆发。实践上单批500到5000行是常见的平衡区间。另外，表上的唯一索引越多，每次插入的索引维护成本和潜在冲突检测点就越多，只保留业务必需的唯一约束，冗余索引应坚决删除。

合理的重试与降级策略

即使做了排序，高并发下仍可能偶发序列化失败或死锁。建议在数据访问层封装统一的重试逻辑，识别40001（序列化失败）和40P01（死锁）错误码，指数退避后重试，同时对重试次数做上限保护。下面是一个伪代码示例：

import psycopg2
from time import sleep
from random import random

def upsert_with_retry(conn, sql, params, max_retry=3):
    for attempt in range(max_retry):
        try:
            with conn.cursor() as cur:
                cur.execute(sql, params)
            conn.commit()
            return
        except psycopg2.errors.DeadlockDetected:
            conn.rollback()
            if attempt == max_retry - 1:
                raise
            # 指数退避加随机抖动，避免重试风暴
            sleep(0.05 * (2 ** attempt) + random() * 0.05)

总结一下，UPSERT的性能与稳定性取决于三件事：理解推测性插入和行锁的获取机制、保证并发事务的加锁顺序一致、控制事务大小并配好重试策略。把这三点落实到代码和表设计中，ON CONFLICT完全可以在高并发写入场景下保持稳定的表现，同时享受它相对于先查后写的原子性优势。

PostgreSQL UPSERTON CONFLICT死锁修改时间：2026-09-05 15:02:41