PostgreSQL事务日志插入顺序异常问题及解决方案

开发者阿泽

2026-8-19

问题概述

我遇到了事务日志表插入顺序不符合逻辑预期的问题：针对accountinstance+迭代号的存款记录，在逻辑上本应晚于该迭代号的创建记录，但实际却出现在了前面。

技术栈

使用PostgreSQL 13.x版本。

背景信息

我正在编写需保证原子性的资金存取代码，用于逻辑账户的存/取款操作。除了确保单条账户记录的存/取款操作原子性外，还需要在事务表中记录每个逻辑操作，每条操作对应一行记录。

部分原子操作会在事务表中生成两条记录：取款+存款（重置事件），即先取出资金再存入，需向事务表添加两行分别记录这两个动作。系统中也存在大量普通的存/取款操作，它们需要相对于账户当前余额保持原子性，且各自生成一条事务记录。

经大量测试，SQL查询已确保账户操作的原子性，所有存/取款动作顺序正确，未出现脏读或数据覆盖问题。但事务表中出现了引用尚未存在的迭代号和余额的记录。

预期的事务顺序应为：

重置步骤1（取款）
重置步骤2（存款）
对已完成重置的账户迭代号执行取款1

重置操作需连续出现，且早于任何引用该新迭代号的存/取款操作。

但实际有时会出现如下顺序：

对已完成重置的账户迭代号执行取款1
重置步骤1（取款）
重置步骤2（存款）

其中“取款1”记录的账户余额是正确的，仅能在“重置步骤2”执行完成后产生，且记录的迭代号为重置后的数值，但该查询生成的事务插入记录按时间戳排序却出现了上述错乱的顺序。

存款操作SQL

WITH
    accounts AS (
        SELECT id, iteration, balance_tenthoucents, subbalance_tenthoucents
        FROM accountinstances
        WHERE id = ANY($1)
        FOR UPDATE
    ),
    deposits AS (
        INSERT INTO accounttransactions (accountinstanceid, iteration, subwagerid, amount_tenthoucents, subbalance_tenthoucents, balance_tenthoucents, subbalance_balance_tenthoucents) VALUES
            ($2, (SELECT iteration FROM accounts WHERE id = $2), $3, $4, $5, (SELECT balance_tenthoucents+$4 FROM accounts WHERE id = $2), (SELECT subbalance_tenthoucents+$5 FROM accounts WHERE id = $2))
        RETURNING accountinstanceid, balance_tenthoucents, subbalance_balance_tenthoucents
    )
UPDATE accountinstances
SET
    balance_tenthoucents = deposits.balance_tenthoucents,
    subbalance_tenthoucents = deposits.subbalance_balance_tenthoucents
FROM deposits
WHERE accountinstances.id = deposits.accountinstanceid
RETURNING accountinstances.accountid, accountinstances.balance_tenthoucents/10000

重置操作SQL

WITH
    locked AS (
        SELECT
            accountinstances.id, accountinstances.iteration, accountinstances.accountid, accountinstances.balance_tenthoucents, accountinstances.subbalance_tenthoucents,
            ((accountinstances.balance_tenthoucents/10000)*10000) AS win_tenthoucents,
            (accounts.reset_balance * 10000) + COALESCE(accountinstances.subbalance_tenthoucents, 0) AS reset_balance,
            (CASE WHEN accounts.subbalance_pertenthou > 0 THEN -accountinstances.subbalance_tenthoucents ELSE NULL END) AS subbalance
        FROM accountinstances
        JOIN accounts ON accountinstances.accountid = accounts.id
        WHERE accountinstances.id = ANY($1)
        FOR UPDATE OF accountinstances
    ),
    wins AS (
        INSERT INTO accounttransactions (accountinstanceid, iteration, amount_tenthoucents, payoutid, balance_tenthoucents, subbalance_balance_tenthoucents)
            SELECT locked.id, locked.iteration, -locked.win_tenthoucents, $2, locked.balance_tenthoucents-locked.win_tenthoucents, locked.subbalance_tenthoucents
            FROM locked
        RETURNING id AS txid, accountinstanceid AS id, -amount_tenthoucents/10000 AS won, created
    ),
    resets AS (
        INSERT INTO accounttransactions (accountinstanceid, iteration, amount_tenthoucents, subbalance_tenthoucents, balance_tenthoucents, subbalance_balance_tenthoucents)
            SELECT wins.id, locked.iteration + 1, locked.reset_balance, locked.subbalance, locked.balance_tenthoucents-locked.win_tenthoucents+locked.reset_balance, (CASE WHEN locked.subbalance IS NOT NULL THEN 0 ELSE NULL END)
            FROM locked
            JOIN wins ON wins.id = locked.id
        RETURNING accounttransactions.accountinstanceid AS id, accounttransactions.iteration, accounttransactions.balance_tenthoucents, accounttransactions.subbalance_balance_tenthoucents, accounttransactions.balance_tenthoucents/10000 AS balance
    ),
    updates AS (
        UPDATE accountinstances
        SET
            iteration = resets.iteration,
            balance_tenthoucents = resets.balance_tenthoucents,
            subbalance_tenthoucents = resets.subbalance_balance_tenthoucents
        FROM resets
        WHERE accountinstances.id = resets.id
        RETURNING accountinstances.id AS id, accountinstances.accountid AS accountid, accountinstances.balance_tenthoucents/1000 AS balance
    )
SELECT wins.txid, updates.accountid, wins.won, updates.balance, wins.created FROM updates JOIN wins ON updates.id = wins.id

示例数据

8   2022-09-09 19:26:45.564463+00   4000000000  4000000000  -- 上一次重置的收尾，账户获得初始资金
9   2022-09-09 19:26:45.570226+00   100000      4000100000  -- 一笔存款
8   2022-09-09 19:26:45.574191+00   -4000000000 0           -- 从8到9重置的步骤1
9   2022-09-09 19:26:45.574191+00   4000000000  4000000000  -- 从8到9重置的步骤2

字段说明：

第1列：迭代号（iteration），默认值为1的索引，仅在重置事件（账户清空并重新充值）时递增，例如从8变为9。
第2列：created，默认值为now()的timestamptz类型字段。
第3列：delta，正数为存款，负数为取款。
第4列：balance，账户实例的当前余额。

异常点在于：迭代号为8时账户余额为4000000000，事务日志中显示一笔存款后余额变为4000100000，但该存款对应的是迭代号9，意味着从8到9的重置事件已经完成，而重置记录却出现在存款记录之后。

问题分析

推测可能的原因：

存款操作的INSERT在19:26:45.570226+00初始化，但需等待重置操作完成才能执行，却因启动更早而“预留”了时间戳，导致记录出现在重置记录之前。
重置操作先修改了accountinstances记录，但存款操作的事务日志先插入，重置的事务日志后插入。

排查更新

经测试，若不按时间戳排序事务记录，而是使用PostgreSQL内部的自然插入顺序，就能得到预期的顺序。但异常的存/取款记录的时间戳早于其所属的accountinstance迭代号的创建时间。这表明PostgreSQL在查询排队时就捕获了now()的值，而非在实际提交时捕获，导致时间戳早于最终引用的重置迭代号的创建时间。

需要将created字段的默认值改为在提交时延迟计算的PostgreSQL函数，而非now()。

解决方案

问题根源是PostgreSQL在事务开始时缓存now()的值，当事务因等待资源而延迟执行时，now()的时间戳会早于实际操作时的状态。经测试，改用clock_timestamp()替代now()可解决该问题。在查询中显式设置该值后，事务记录的顺序与逻辑时序一致，所有引用的迭代号都已存在于事务流中。

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

PostgreSQL事务日志插入顺序异常问题及解决方案

技术栈 #

背景信息 #

存款操作SQL #

重置操作SQL #

示例数据 #

问题分析 #

排查更新 #

解决方案 #

技术栈