如何处理PostgreSQL中两条并发的INSERT ON CONFLICT DO UPDATE查询

问题根因 #

你当前的SQL在ON CONFLICT分支中使用了独立子查询查询当前行的quantity值，当事务隔离级别为可重复读（多数数据库默认配置）时，子查询会读取事务开启时的快照数据。首次插入的事务未提交前，第二个并发请求的事务快照中不存在该主键对应的行，子查询返回null，最终更新后quantity值为null，后续所有累加操作都会持续返回null。

最优解决方案：直接优化SQL语句 #

不需要额外加锁，调整ON CONFLICT分支的写法，直接引用冲突行的当前值，数据库会自动读取冲突行的最新已提交数据，规避快照读的问题，性能损耗最低：

PostgreSQL 语法 #

INSERT INTO table1 (primary_key, quantity)
VALUES (:primary_key, :qty)
ON CONFLICT (primary_key) DO UPDATE
SET quantity = COALESCE(table1.quantity, 0) + EXCLUDED.quantity;

MySQL 语法 #

INSERT INTO table1 (primary_key, quantity)
VALUES (:primary_key, :qty)
ON DUPLICATE KEY UPDATE
quantity = COALESCE(quantity, 0) + VALUES(quantity);

语法说明 #

• EXCLUDED.quantity/VALUES(quantity)指代本次尝试插入的quantity值，也就是传入的:qty参数，不需要重复传参
• 直接引用冲突行的当前存储值，不需要额外写子查询
• COALESCE兜底如果行值为空的情况，默认从0开始累加，完全匹配你的业务需求

备选方案（业务逻辑复杂时可选用） #

• 应用层加分布式锁：以主键值为锁Key，调用接口时先抢分布式锁（比如RedisSETNX），抢到锁的请求再执行数据库操作，操作完成后释放锁，从源头避免并发请求同时到达数据库
• 数据库层面提前加锁：执行插入前先执行SELECT * FROM table1 WHERE primary_key = :primary_key FOR UPDATE，PostgreSQL可额外加ON CONFLICT DO NOTHING抢占行锁，确保同一时间只有一个请求操作对应主键行

内容的提问来源于stack exchange，提问作者Shyam Sundar N