SQL Server Billing Implementation：用户余额管理最佳实践

明确结论

不要把 Users.Credit 当作唯一的财务数据，也不需要在每次查询余额时，重新汇总用户开户以来的所有交易。

更稳妥的设计是：

• 用不可随意修改的交易流水记录充值、消费、退款、奖励和冲正，并将其作为事实来源。
• 单独保存当前余额，供高频查询使用。
• 在同一个数据库事务中写入流水并更新余额。
• 定期汇总流水，用于对账和发现异常，但不能靠它维持实时一致性。
• 金额使用 decimal 或以最小货币单位表示的整数，不要使用 float、real。
• 对重要的 billing system，优先采用复式记账，不要只依赖一列正负金额组成的简单流水。

也就是采用”流水账本 + 实时余额快照”：平时可以快速查询余额，同时保留审计和恢复所需的完整记录。

为什么不能只保存 Users.Credit

只保存余额、不保留交易流水，会直接导致以下问题：

• 无法说明余额为何是当前数值。
• 难以可靠处理退款、撤销、重复请求和人工调整。
• 并发更新或程序异常可能造成余额丢失。
• 出现差错后，没有足够的信息重建余额。
• 无法进行财务对账和审计。
• 无法清楚区分赠送 credit、实际充值、消费和退款。

例如，两个请求同时执行下面的逻辑：

SELECT Credit FROM dbo.Users WHERE UserId = @UserId;
-- 应用程序计算新余额
UPDATE dbo.Users SET Credit = @NewCredit WHERE UserId = @UserId;

它们可能读到相同的旧余额。后提交的更新会覆盖先提交的结果，这就是典型的 lost update。

推荐的数据模型

简单的站内余额系统可以分别保存账户、流水和业务请求。

CREATE TABLE dbo.Accounts
(
    AccountId       bigint         NOT NULL PRIMARY KEY,
    UserId          bigint         NOT NULL UNIQUE,
    CurrentBalance  decimal(19, 4) NOT NULL,
    CurrencyCode    char(3)        NOT NULL,
    RowVersion      rowversion     NOT NULL,

    CONSTRAINT CK_Accounts_Balance
        CHECK (CurrentBalance >= 0)
);
GO

CREATE TABLE dbo.AccountTransactions
(
    TransactionId       bigint          IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    AccountId           bigint          NOT NULL,
    Amount              decimal(19, 4)  NOT NULL,
    TransactionType     varchar(30)      NOT NULL,
    BusinessReference   varchar(100)     NOT NULL,
    CreatedAtUtc        datetime2(3)     NOT NULL
        CONSTRAINT DF_AccountTransactions_CreatedAtUtc
        DEFAULT SYSUTCDATETIME(),
    CreatedBy            nvarchar(100)   NULL,
    Description          nvarchar(500)   NULL,

    CONSTRAINT FK_AccountTransactions_Accounts
        FOREIGN KEY (AccountId) REFERENCES dbo.Accounts(AccountId),

    CONSTRAINT CK_AccountTransactions_Amount
        CHECK (Amount <> 0),

    CONSTRAINT UQ_AccountTransactions_BusinessReference
        UNIQUE (AccountId, BusinessReference)
);
GO

CREATE INDEX IX_AccountTransactions_AccountId_TransactionId
    ON dbo.AccountTransactions(AccountId, TransactionId)
    INCLUDE (Amount, TransactionType, CreatedAtUtc);
GO

字段和金额可以按以下规则使用：

• 充值、奖励和退款记为正数。
• 消费和扣款记为负数。
• BusinessReference 保存支付订单号、购买订单号或请求的幂等键。
• CurrencyCode 表示账户币种，不同币种不能直接汇总。
• CurrentBalance 保存流水汇总后的实时余额。

如果业务只处理固定精度的货币，也可以使用 bigint 保存最小货币单位，例如以”分”为单位。无论采用哪种方式，整个系统都要使用一致的精度和舍入规则。

在同一个事务中记账和更新余额

所有余额变化都应通过一个受控入口完成，例如 stored procedure。不要让各个业务模块直接修改余额。

下面的过程会锁定账户并检查余额，然后写入交易流水，最后原子更新当前余额。

CREATE OR ALTER PROCEDURE dbo.PostAccountTransaction
    @AccountId          bigint,
    @Amount             decimal(19, 4),
    @TransactionType    varchar(30),
    @BusinessReference  varchar(100),
    @CreatedBy           nvarchar(100) = NULL,
    @Description         nvarchar(500) = NULL
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    IF @Amount = 0
        THROW 50001, 'Transaction amount must not be zero.', 1;

    BEGIN TRY
        BEGIN TRANSACTION;

        -- 幂等处理：相同业务请求不能重复入账
        IF EXISTS
        (
            SELECT 1
            FROM dbo.AccountTransactions WITH (UPDLOCK, HOLDLOCK)
            WHERE AccountId = @AccountId
              AND BusinessReference = @BusinessReference
        )
        BEGIN
            COMMIT TRANSACTION;
            RETURN;
        END;

        -- 使用单条 UPDATE 完成扣款和余额校验
        UPDATE dbo.Accounts
        SET CurrentBalance = CurrentBalance + @Amount
        WHERE AccountId = @AccountId
          AND CurrentBalance + @Amount >= 0;

        IF @@ROWCOUNT = 0
        BEGIN
            IF NOT EXISTS
            (
                SELECT 1
                FROM dbo.Accounts
                WHERE AccountId = @AccountId
            )
                THROW 50002, 'Account does not exist.', 1;

            THROW 50003, 'Insufficient balance.', 1;
        END;

        INSERT INTO dbo.AccountTransactions
        (
            AccountId,
            Amount,
            TransactionType,
            BusinessReference,
            CreatedBy,
            Description
        )
        VALUES
        (
            @AccountId,
            @Amount,
            @TransactionType,
            @BusinessReference,
            @CreatedBy,
            @Description
        );

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF XACT_STATE() <> 0
            ROLLBACK TRANSACTION;

        THROW;
    END CATCH;
END;
GO

调用示例：

-- 充值 100.00
EXEC dbo.PostAccountTransaction
    @AccountId = 1001,
    @Amount = 100.00,
    @TransactionType = 'DEPOSIT',
    @BusinessReference = 'payment:PAY-20260831-001';

-- 消费 25.50
EXEC dbo.PostAccountTransaction
    @AccountId = 1001,
    @Amount = -25.50,
    @TransactionType = 'PURCHASE',
    @BusinessReference = 'order:ORD-20260831-009';

更新 Accounts 和插入 AccountTransactions 位于同一个事务中，因此不会出现”余额已经扣除，流水却没有写入”或相反的半完成状态。

唯一约束是防止重复入账的最后一道保障。即使支付平台重复通知、客户端重试或服务超时，同一个业务事件也不会被重复记账。实际系统还要处理一种情况：相同的 BusinessReference 再次提交时，如果金额或类型与原记录不同，系统应返回冲突错误，不能直接按成功处理。

查询余额不需要每次汇总全部流水

正常查询直接读取实时余额：

SELECT
    CurrentBalance,
    CurrencyCode
FROM dbo.Accounts
WHERE AccountId = @AccountId;

这通常只需要一次索引查找。汇总流水主要用于对账：

SELECT
    a.AccountId,
    a.CurrentBalance AS StoredBalance,
    COALESCE(SUM(t.Amount), 0) AS CalculatedBalance,
    a.CurrentBalance - COALESCE(SUM(t.Amount), 0) AS Difference
FROM dbo.Accounts AS a
LEFT JOIN dbo.AccountTransactions AS t
    ON t.AccountId = a.AccountId
WHERE a.AccountId = @AccountId
GROUP BY
    a.AccountId,
    a.CurrentBalance;

定时任务可以执行类似的检查，但发现差异后不宜直接覆盖余额。通常应按以下流程处理：

• 记录差异及相关账户。
• 阻止或限制可疑账户继续发生资金变化。
• 查找缺失、重复或错误的交易。
• 通过冲正流水修复问题。
• 保留完整的处理记录。

如果定时任务每晚都要大量”修正”余额，说明实时写入链路存在缺陷。调度任务不能承担维持一致性的主要责任。

流水应尽量只追加，不应直接修改

已经完成的财务流水，原则上不应执行普通的 UPDATE 或 DELETE。需要取消一笔扣款时，应追加一笔与原交易关联的反向交易：

PURCHASE   -50.00
REVERSAL   +50.00

可以增加下面的字段来表示这种关联：

ALTER TABLE dbo.AccountTransactions
ADD ReversesTransactionId bigint NULL;
GO

ALTER TABLE dbo.AccountTransactions
ADD CONSTRAINT FK_AccountTransactions_Reversal
    FOREIGN KEY (ReversesTransactionId)
    REFERENCES dbo.AccountTransactions(TransactionId);
GO

这样既能恢复余额，也不会抹掉原始操作。管理员奖励和人工调整同样要生成明确的交易记录，写明操作人、原因和审批编号等信息，不能直接修改 CurrentBalance。

数据库权限也要遵守同一原则。业务账号只应获得执行记账 stored procedure 的权限，不应拥有直接更新余额或删除流水的权限。

余额、可用余额和冻结金额要分开

业务稍微复杂一些，通常就不能只使用一个 balance。支付授权、退款处理中和订单预占等场景，可能需要区分：

• PostedBalance：已经正式入账的余额。
• PendingBalance：尚未完成的交易金额。
• AvailableBalance：当前允许消费或提现的金额。
• ReservedAmount：为订单、提现等操作冻结的金额。

一种常见的关系是：

AvailableBalance = PostedBalance - ReservedAmount

具体定义要由业务规则决定，不能只看字段名称。需要明确区分”已入账”、“处理中”和”可使用”。否则即使 SQL 没有问题，用户看到的余额仍可能不符合业务预期。

大规模流水不应依赖无限期全量汇总

流水规模增大后，可以通过以下方式降低对账成本：

• 为 AccountId 和递增的 TransactionId 建立合适的索引。
• 按时间或业务周期生成不可变的结算快照。
• 在快照中保存对应的最后一笔 TransactionId。
• 对账时从最近的快照开始，只汇总之后产生的流水。
• 根据实际数据量和维护需求，按日期对大型流水表进行分区。
• 将报表分析和实时记账负载分开，避免复杂查询影响在线交易。

例如，某个快照的余额是 1000.00，对应的最后一笔流水为 TransactionId = 500000，后续可以这样重算：

SELECT
    @SnapshotBalance + COALESCE(SUM(Amount), 0)
FROM dbo.AccountTransactions
WHERE AccountId = @AccountId
  AND TransactionId > @LastTransactionId;

快照只用于提高性能，原始流水仍是审计依据。

对重要 billing system 使用复式记账

如果系统涉及真实货币、提现、商户结算、税务或多方资金归属，简单的”用户流水正负金额”通常不够。更可靠的选择是复式记账：

• 每个业务事件生成一个 journal entry。
• 每个 journal entry 至少包含两条 posting。
• 所有 posting 的借贷合计必须平衡。
• 用户余额、平台收入、现金和退款负债等资金分别记录在不同账户中。

从概念上看，一笔用户充值可能记为：

Debit   Cash / Payment Clearing
Credit  Customer Stored-Value Liability

一次消费可能记为：

Debit   Customer Stored-Value Liability
Credit  Revenue or Merchant Payable

复式记账能说明资金从哪里来、流向哪里，还能通过平衡约束发现遗漏。借方和贷方的具体方向取决于账户类别和实际采用的会计模型，不能脱离账务设计机械套用。

SQL Server 可以负责事务性落库和约束。账务科目、结算、退款、税费及收入确认规则，则应由熟悉业务和会计要求的人员共同设计。

还需要重点处理的边界条件

可用于生产环境的 billing implementation 至少还要覆盖以下问题：

• 幂等性：每个外部支付事件和业务请求都必须有唯一标识。
• 并发控制：扣款必须使用原子条件更新或明确的锁定策略。
• 死锁重试：事务应尽量短小，应用层可以对可重试错误进行有限次数的重试。
• 交易状态：pending、posted、failed、reversed 等状态需要清晰的转换规则。
• 外部对账：内部流水要与支付渠道、银行或商户结算数据核对。
• 时间记录：数据库时间建议保存为 UTC，并区分事件发生时间和入账时间。
• 货币精度：明确币种、最小单位、舍入方式及税费计算顺序。
• 透支规则：是否允许负余额应由业务规则决定，不能只依赖默认值。
• 审计信息：记录业务来源、操作人、关联订单和冲正关系。
• 备份恢复：定期验证备份能否真正恢复。
• 监控告警：持续监控余额差异、重复幂等键、失败交易和异常人工调整。

迁移现有系统时的建议

旧系统没有完整流水，因此无法只凭当前的 Credit 还原历史交易。迁移时需要明确这一限制，不能生成虚构的历史记录。

可以为每个账户建立一笔期初余额：

OPENING_BALANCE +当前 Credit

这笔记录应包含迁移批次、迁移时间和来源说明。系统上线后，所有新的余额变化都要通过统一的记账过程，同时写入流水和余额。迁移通常包括以下步骤：

1. 暂停旧系统的余额写入，或建立可靠的增量捕获机制。
2. 复制当前余额，并生成期初流水。
3. 核对账户数量、余额总额和异常负值。
4. 切换所有充值、消费、退款和奖励入口。
5. 禁止应用程序直接更新余额字段。
6. 上线自动对账和异常告警。

对于旧系统缺失的历史交易，只能根据订单、支付渠道或其他日志尽可能补录。如果无法验证，就应将其保留为”期初余额”，不能把它当成具有完整明细的历史交易。

实施原则总结

实际设计不需要在”保存余额”和”保存流水”之间二选一。两者承担不同的职责：

• 流水用于追溯、审计和重建余额。
• 当前余额用于快速查询和处理并发扣款。
• 数据库事务保证两者同步变化。
• 唯一约束和幂等键用于防止重复记账。
• 定期对账用于发现问题，不能用来掩盖问题。
• 错误应通过冲正交易修复，不应直接篡改历史。
• 涉及真实资金和复杂结算时，应采用复式记账。

还可以参考 Martin Fowler 对 Accounting Patterns、Transaction Script 和 Event Sourcing 的讨论，以及 SQL Server 官方文档中关于 transactions、locking、row versioning、isolation levels、decimal 数据类型、索引和 THROW 的说明。如果系统处理真实资金，还需要结合所在地区的会计、税务、支付合规和数据留存要求进行专业评审。

备注：内容仅供参考。