Postgres如何实现线程安全账本插入？并发不一致问题排查

我需要创建一个不可变账本数据库表，每行插入都要反映账户的最新状态。表结构如下：

CREATE TABLE account_ledger
(
    id                 SERIAL PRIMARY KEY,
    account_id         BIGINT not null,
    transaction_id     VARCHAR(255),
    transaction_amount DECIMAL(10, 2) not NULL,
    current_balance    DECIMAL(10, 2) not null constraint positive_balance CHECK (current_balance >= 0)
);

需要实现三个核心逻辑：

• 查询指定账户的最新记录；
• 无记录时默认余额为0；
• 插入新行时，余额为最新记录余额加上交易金额。

举个例子：初始状态有一条记录

id  | account_id | transaction_id | transaction_amount | current_balance 
1   | 123        | A1             | 100                | 100

插入一笔10元的贷方交易后，应该新增一行余额为110的记录。

但在多线程并发插入场景下，数据出现不一致：比如连续插入15次10元后，最终余额只有140或130。尝试过Read Uncommitted、Read Committed、Repeatable Read隔离级别都无法解决问题。

当前使用的查询语句如下：

WITH latest_data as (
  select
    al.current_balance as latest_balance,
    al.transaction_id as latest_transaction_id
    from account_ledger al
    where al.account_id = :accountId order by al.id desc limit 1 
    -- 试过加不加行锁都不行
    for update
)
-- 用latest_data里的最新行数据插入新记录
insert into account_ledger (account_id, transaction_id, transaction_amount, current_balance)
SELECT :accountId, :transactionId, :transactionAmount, coalesce (latest_data.latest_balance + :transactionAmount, :transactionAmount)
-- 左连接处理无记录的情况
from (select 1 as empty) e
left join latest_data on true
where :transactionAmount >= 0 or latest_data.latest_balance > ABS(:transactionAmount)
and (
 -- 仅当最新交易ID等于当前获取到的ID时才插入
    latest_data.latest_transaction_id is null or
    latest_data.latest_transaction_id = (select transaction_id from account_ledger cl where cl.account_id = :accountId order by cl.id desc limit 1)
)

使用Read Uncommitted隔离级别时，还出现了重复余额的记录。

请问：

1. 如何调整隔离级别或优化查询来实现一致的结果？
2. 如何分析当前查询的不一致行为？

当前查询的不一致根源在于并发场景下的竞态条件，具体问题点：

1. 双重查询最新记录导致的不一致：WITH子句用for update锁定了最新行，但WHERE条件里又重新查询了一次account_ledger的最新交易ID。第二次查询没有锁定，并发时可能读取到其他事务已提交的新记录，导致条件判断失效，允许不符合预期的插入。
2. 行锁作用范围有限：for update只锁定了WITH子句查询到的那一行，若其他事务插入新行，这个锁无法阻止新行的读取。第二次查询会拿到新行ID，导致当前事务插入条件成立，但计算余额时用的还是旧锁定行的余额，最终出现余额少加的情况。
3. 隔离级别无法解决逻辑竞态：Read Committed/Repeatable Read等隔离级别仅能解决脏读、不可重复读等问题，无法处理业务逻辑里的“先读后写”竞态，必须通过显式锁或原子化操作解决。

方案1：原子化INSERT...SELECT+行级锁 #

核心思路是把“读取最新余额+插入新行”做成原子操作，用锁确保并发时只有一个事务能成功。

步骤1：添加账户ID索引（提升锁效率）

CREATE INDEX idx_account_ledger_account_id ON account_ledger(account_id);

步骤2：重写插入语句，移除多余二次查询

WITH latest_data AS (
    SELECT current_balance
    FROM account_ledger
    WHERE account_id = :accountId
    ORDER BY id DESC
    LIMIT 1
    FOR UPDATE -- 锁定最新行，阻止其他事务读取修改
)
INSERT INTO account_ledger (account_id, transaction_id, transaction_amount, current_balance)
SELECT 
    :accountId, 
    :transactionId, 
    :transactionAmount, 
    COALESCE(l.current_balance, 0) + :transactionAmount
FROM (SELECT 1) AS dummy
LEFT JOIN latest_data l ON true
WHERE 
    -- 确保余额非负
    (:transactionAmount >= 0) OR (COALESCE(l.current_balance, 0) >= ABS(:transactionAmount))

关键改进：

• 去掉第二次查询最新交易ID的逻辑，避免竞态；
• FOR UPDATE锁定最新行后，其他并发事务必须等待当前事务提交才能读取该行，确保余额计算基于最新已提交状态；
• 用COALESCE(l.current_balance, 0)直接处理无记录场景，默认余额为0。

方案2：使用SERIALIZABLE隔离级别 #

如果使用PostgreSQL，SERIALIZABLE隔离级别会自动检测并发事务冲突，发现竞态时回滚其中一个事务，需应用层捕获异常并重试。

操作步骤：

1. 设置事务隔离级别：

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

2. 执行优化后的插入逻辑，应用层处理40001（序列化失败）错误并重试事务。

方案3：引入账户状态表（效率优化） #

若账本数据量较大，每次插入排序找最新行效率低，可新增account_balance表维护当前余额：

CREATE TABLE account_balance (
    account_id BIGINT PRIMARY KEY,
    current_balance DECIMAL(10,2) NOT NULL DEFAULT 0 CHECK (current_balance >=0)
);

插入账本时，先原子更新账户余额，再插入记录：

WITH updated_balance AS (
    UPDATE account_balance
    SET current_balance = current_balance + :transactionAmount
    WHERE account_id = :accountId
    RETURNING current_balance
    -- 无记录时插入初始余额
    ON CONFLICT (account_id) DO UPDATE 
        SET current_balance = account_balance.current_balance + :transactionAmount
    RETURNING current_balance
)
INSERT INTO account_ledger (account_id, transaction_id, transaction_amount, current_balance)
SELECT :accountId, :transactionId, :transactionAmount, (SELECT current_balance FROM updated_balance);

这种方式把余额计算的原子性交给UPDATE语句，效率更高，并发控制更可靠。

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