CTE单语句INSERT+UPDATE对比事务内分两次执行：差异及优势分析

假设有如下表结构：

create table public.my_table (
    id          uuid primary key,
    category_id uuid not null,
    name        text not null,
    is_active   bool not null,
    created_at timestamp with time zone not null default now(),
    updated_at timestamp with time zone not null default now()
);

业务规则要求：一个category_id只能存在一条is_active=true的记录。

我想到两种添加新活跃记录的实现方式：

方式1：使用CTE插入新记录并更新旧记录失效 #

WITH new_record AS (
    INSERT INTO my_table (id, category_id, name, is_active) VALUES (?, ?, ?, true)
    RETURNING id, category_id
)
UPDATE my_table
SET is_active  = false,
    updated_at = now()
FROM new_record
WHERE my_table.category_id = new_record.category_id
  AND my_table.id <> new_record.id
  AND my_table.is_active = true

方式2：在事务中执行独立的INSERT和UPDATE语句 #

BEGIN TRANSACTION;

INSERT INTO my_table (id, category_id, name, is_active) VALUES (?, ?, ?, true);

UPDATE my_table
   SET is_active  = false,
       updated_at = now()
   WHERE my_table.category_id = ?
         AND my_table.id <> ?
         AND my_table.is_active = true;

COMMIT;

我更倾向于第二种方式，因为它更简洁，但想请教：第一种方式相比第二种是否具备优势？

第一种CTE方案确实有几个比事务方案更突出的优势：

• 避免重复传参：CTE里的INSERT返回了新记录的id和category_id，UPDATE语句直接复用这些值，不需要像事务方案那样重复传入category_id和新记录的id，减少了参数传递的冗余，也降低了传参错误的概率（比如不小心传错了旧的id）。

• 原子性保障更简洁：虽然两种方案都能保证原子性（CTE的INSERT和UPDATE是单个语句，本身就是原子的；事务方案靠显式事务包裹），但CTE方案是单条SQL语句，不需要手动管理事务的开启和提交，在一些客户端工具或ORM框架中，单语句的执行更省心，也减少了因事务管理不当（比如忘记COMMIT）引发的问题。

• 一致性更强：在高并发场景下，CTE的单语句执行能减少中间状态的暴露。事务方案中，INSERT执行后到UPDATE执行前的短暂窗口里，数据库中会存在两条同category的活跃记录（虽然时间很短），如果有其他查询刚好在这个窗口读取数据，可能会读到不符合规则的结果；而CTE方案中，INSERT和UPDATE是原子执行的，不会出现这种中间不一致的情况。

• 锁的效率更高：单语句的CTE操作会更高效地获取和释放锁，相比事务中先INSERT再UPDATE的两步操作，减少了锁持有时间，降低了并发冲突的概率。

不过第二种事务方案的简洁性确实是优点，在低并发场景下完全够用。如果你的业务并发量不高，或者更看重代码的可读性，选第二种没问题；但如果要追求更高的一致性、更少的出错概率，第一种CTE方案更可靠。

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