通过一次死锁现象，学习PostgreSQL中的UPSERT

在最近的一个项目中，遇到了一个典型的PostgreSQL并发问题：开发团队需要将程序生成的数据批量通过UPSERT操作写入表中，为提升效率采用了多线程并行处理，但多个线程处理的数据存在交集，结果却因频繁的死锁报错导致程序被终止。

通过这个问题，我们就来深入地学习一下PostgreSQL中的UPSERT特性。

什么是UPSERT

UPSERT即“UPDATE or INSERT”的简称，当插入数据时若触发唯一约束冲突（如主键或唯一索引重复），则自动执行更新操作的特性。

相比于先SELECT检查数据是否存在，再决定INSERT还是UPDATE的方式，UPSERT的整个操作减少了一次查询操作，在高并发场景下可以有明显的效率提升，并且该操作在数据库层面是原子性的。

在PG中，UPSERT功能通过INSERT ... ON CONFLICT ...语法来实现。例如我们想往tb_test表中插入一行数据，若这行数据的id字段与现有数据冲突，则更新原有数据，那么可以使用如下SQL：

INSERT INTO tb_test(id, val) 
    VALUES (1,'test')
    ON CONFLICT id
    DO UPDATE SET val=EXCLUDED.val;

上面的SQL中EXCLUDED是一个特殊的关键字，它代表了原本要插入但因为数据冲突而未成功插入的那行数据。

在使用ON CONFLICT语法时，我们还可以使用DO NOTHING，这代表数据发生冲突时，不做任何操作。

INSERT INTO tb_test(id, val) 
    VALUES (1,'test')
    ON CONFLICT id
    DO NOTHING;

普通的INSERT语句在数据冲突时，会报错“duplicate key value violates unique constraint”，并在数据库中留下一行死堆元。使用ON CONFLICT DO NOTHING时，数据冲突时不会报错，也不会产生死堆元。

而在PG中，使用UPSERT也是可以像普通的INSERT一样，在一次操作中处理多行数据的，其用法如下：

INSERT INTO tb_test(id, val) 
    VALUES (1,'test1'), (2,'test2'), (3,'test3'), ...
    ON CONFLICT id
    DO UPDATE SET val=EXCLUDED.val;

此外，从PG15版本（2022年10月发布）开始，也开始支持MERGE INTO
 语法，通过此语法也可以实现上述功能。

为什么会形成死锁

在通过一次UPSERT操作处理多行数据时，数据库的加锁机制是“逐行处理、逐行加锁”，而不是一次性对所有需要操作的行全部加锁。这就意味着UPSERT处理多行数据时，锁的获取顺序与语句中各行的排列顺序一致，如果多个并发事务处理的多行数据存在交集且排列顺序不同，就可能因锁的获取顺序交叉而形成死锁。

例如：事务1中的处理顺序为“行A→...→行B”，事务2中的处理顺序为“行B→...→行A”。

解决方案与优化建议

当多个线程处理的数据集存在交集时，若所有线程都按唯一约束字段的相同顺序（如升序）处理数据，则它们获取锁的顺序完全一致，不会形成循环等待。

在程序中，我们的开发小伙伴先将需要处理的数据先排序一遍，再使用UPSERT加载到数据库中，这个死锁的问题就不再出现啦。

同时，我们还建议“化整为零”，将大批次数据拆分为多个小批次数据来多次处理，这样可以降低单个事务的持锁时间，从而降低并发时的等待时间。

最后，欢迎感兴趣的各位加入DB演武场交流群，在这里可以交流PostgreSQL、Linux、Kingbase、openGauss等各类技术知识，期待您的加入。