PostgreSQL触发器更新余额表的竞态问题及解决方案咨询

系统设计背景 #

我维护一套金融系统，用户持有代币并可添加交易记录，核心需求是保证代币余额及平均买入价绝对准确。为此设计三张表：

• token：存储代币基础信息
• transaction：存储代币交易记录
• balance：存储代币余额快照，避免每次查询都重新汇总所有交易，通过PostgreSQL触发器自动更新

现有触发器实现 #

以下是transaction表INSERT操作的触发函数：

DECLARE
    old_balance NUMERIC(17, 8);
    old_mean_price NUMERIC(17, 8);
    old_local_mean_price NUMERIC(17, 8);
    new_balance NUMERIC(17, 8);
    new_mean_price NUMERIC(17, 8);
    new_local_mean_price NUMERIC(17, 8);
BEGIN
    -- 禁止插入回溯交易，避免破坏余额表数据
    IF EXISTS (
        SELECT * FROM transaction
        WHERE
          token_id = NEW.token_id
          AND date > NEW.date
      ) THEN
      RAISE EXCEPTION 'There is already a newer transaction for token %', NEW.token_id;
    END IF;

    -- 获取该代币最新余额
    SELECT
      amount,
      mean_price,
      local_mean_price
    INTO
      old_balance, old_mean_price, old_local_mean_price
    FROM balance
    WHERE
      token_id = NEW.token_id
      AND date <= NEW.date
    ORDER BY date DESC
    LIMIT 1;

    -- 若无余额记录则初始化为0
    old_balance := COALESCE(old_balance, 0);
    old_mean_price := COALESCE(old_mean_price, 0);
    old_local_mean_price := COALESCE(old_local_mean_price, 0);

    -- 计算新的余额与均价
    IF NEW.side = 'buy' THEN
      new_balance := old_balance + NEW.quantity;
      new_mean_price := (old_balance * old_mean_price + NEW.quantity * NEW.unit_price) / new_balance;
      new_local_mean_price := (old_balance * old_local_mean_price + NEW.quantity * NEW.local_unit_price) / new_balance;
    ELSIF NEW.side = 'sell' THEN
      new_balance := old_balance - NEW.quantity;
      new_mean_price := old_mean_price;
      new_local_mean_price := old_local_mean_price;
    ELSE
      RAISE EXCEPTION 'Side is invalid %', NEW.side;
    END IF;

    -- 更新balance表
    IF NOT EXISTS (
        SELECT * FROM balance
        WHERE
          date = NEW.date
          AND token_id = NEW.token_id
        ) THEN
      INSERT INTO balance
         (date, token_id, amount, mean_price, local_mean_price)
      VALUES
        (
          NEW.date,
          NEW.token_id,
          new_balance,
          new_mean_price,
          new_local_mean_price
        );
    ELSE
      UPDATE balance
      SET
         amount = new_balance,
         mean_price = new_mean_price,
         local_mean_price = new_local_mean_price
      WHERE
         date = NEW.date
         AND token_id = NEW.token_id;
    END IF;

    RETURN NULL;
END;

触发器核心功能 #

• 禁止插入回溯交易（日期晚于已有交易的记录），防止余额数据混乱
• 根据交易类型（买入/卖出）计算新的余额与均价，更新balance表
• 同一代币同一日期的交易，合并更新对应的余额记录

并发竞态问题 #

当多个事务并发插入同一代币的交易时，会出现数据错误：

1. 启动事务T1、T2
2. T1插入交易记录，触发器执行并向balance插入新记录（未提交）
3. T2插入同代币交易，触发器无法看到T1未提交的变更，使用旧数据计算余额
4. T2提交后，balance表中的数据与实际交易序列不符，出现错误

尝试的不完善方案 #

方案1：使用SELECT FOR UPDATE锁定余额记录 #

将查询余额的语句改为加锁查询，但存在三个问题：

• 首次交易时balance表无对应记录，无法锁定
• 事务只能看到启动时的快照数据，即使等待锁释放，仍会读取陈旧数据
• 若T1回滚，T2已基于错误数据完成计算，balance数据依然错误

方案2：延迟触发器到事务提交时执行 #

将触发器设置为DEFERRABLE INITIALLY DEFERRED，在事务提交时执行，此时能看到所有已提交的变更，解决竞态问题，但事务内无法读取到最新的balance数据，必须等待事务提交后才能获取更新结果。

问题解答 #

1. 方案2是否真能解决所有竞态问题？ #

方案2能解决并发插入导致的balance数据不一致的核心竞态问题，但需要注意几个细节：

• 必须保留"禁止回溯交易"的检查逻辑：延迟触发器在提交时执行检查，此时能看到所有已提交的交易记录，若存在更晚的交易，会正确抛出异常，避免回溯交易破坏余额。
• 同日期同代币的并发交易：第一个事务提交后，balance表已存在对应记录，第二个事务的触发器执行时会进入UPDATE分支，基于最新的余额计算，结果正确。
• 局限性：事务内无法实时获取更新后的balance，这是延迟触发器的固有特性，但最终数据一致性是完全保证的。

不存在遗漏的竞态场景，只要触发器逻辑正确，方案2能确保balance数据的准确性。

2. 既能解决问题又能尽快更新balance的方法 #

最优方案是对代币行加排他锁，实现同代币交易的串行执行，具体修改触发器：

在触发器开头添加以下代码，锁定对应的代币行：

-- 锁定代币行，保证同一代币的交易串行执行，避免并发竞态
PERFORM * FROM token WHERE id = NEW.token_id FOR UPDATE;

该方案的优势：

• 解决首次交易无balance记录的问题：通过锁定token行，强制同代币的交易排队执行，每个事务都能看到前一个事务提交后的最新数据。
• 避免陈旧数据读取：因为事务串行执行，每个事务查询balance时，前一个事务的变更已提交，能获取到最新的余额与均价。
• 处理回滚场景：若T1回滚，锁释放后T2会重新读取最新数据，计算结果正确。
• 事务内可实时读取更新后的balance：触发器在事务内执行，插入/更新balance后，同一事务内的后续查询能获取到最新数据。

其他可选方案：

• 可串行化隔离级别：将数据库隔离级别设置为SERIALIZABLE，PostgreSQL会自动检测并发冲突并回滚其中一个事务，应用层需处理重试逻辑。此方案无需修改触发器，但会增加重试次数，适合并发量较低的场景。
• 幂等性余额计算：放弃实时维护balance，改为在查询前检查是否有未处理的交易，按需重新计算并更新balance。此方案复杂度较高，适合查询频率远低于交易频率的场景。

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