Postgres删除与插入竞态条件处理咨询（Node.js+Kafka订单场景）

• 事务逻辑存在天然竞态漏洞：两个处理同一orderId的事务并行执行时，会先后进入事务执行DELETE操作，此时先启动的事务尚未提交，后启动的事务无法感知到前者未提交的删除、插入操作，会得到DELETE返回0行的结果，待两个事务先后提交后，就会出现重复的订单记录。
• 事务隔离级别使用错误：同一个事务内重复执行BEGIN语句本身就会触发数据库报错，就算正确配置了SERIALIZABLE隔离级别，高并发场景下会产生大量序列化失败的异常，如果你没有配套的失败重试逻辑，不仅解决不了问题，还会导致大量更新请求直接失败，性能也远低于普通隔离级别。

方案1：加事务级排他锁（改动最小，性能最优） #

在事务最开头、DELETE操作执行前，针对当前orderId加排他锁，强制同订单的事务串行执行，完全规避竞态。推荐使用PostgreSQL自带的事务级咨询锁，不需要依赖表中已有记录：

-- 放在事务最开头执行，$1为当前orderId
SELECT pg_advisory_xact_lock(hashtext($1));

事务提交或回滚后锁会自动释放，同orderId的后续事务会在这一步阻塞，直到前一个同订单事务执行完成，不会出现并行执行的问题。

方案2：替换先删后插为UPSERT逻辑 #

给transaction_on_orders表添加orderId相关的唯一约束，用INSERT ... ON CONFLICT的原子UPSERT逻辑替代先删后插，性能更优也不会出现竞态：

-- 示例，唯一约束为(order_id, detail_id)的场景
INSERT INTO transaction_on_orders (order_id, detail_id, sku, amount, update_time)
VALUES ($1, $2, $3, $4, $5)
ON CONFLICT (order_id, detail_id) DO UPDATE
SET sku = EXCLUDED.sku, amount = EXCLUDED.amount, update_time = EXCLUDED.update_time;

如果是整单全量替换，可以在UPSERT完本次所有订单明细后，删除同orderId下不在本次明细列表中的旧记录即可。

方案3：Kafka消费端串行化同订单事件 #

在Kafka生产端配置分区规则，将同一个orderId的所有事件路由到同一个分区，消费端对单个分区使用单线程消费，从消息处理的源头保证同一订单的事件按顺序串行执行，完全避免并行处理的竞态问题，这是消息队列处理同实体更新事件的通用最优方案。

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