简单支付交易的 Database 设计与 Balance 更新

2026-10-24支付老李

结论

不要让 Controller 在每次交易时重新汇总所有 deposits 和 withdrawals，再覆盖 Balance。并发请求可能造成更新丢失，而且随着交易记录增加，汇总成本也会越来越高。

建议采用以下设计：

用一张统一的交易流水表记录充值和消费。
用 Account.Balance 缓存当前余额。
在同一个数据库事务中更新余额并插入流水。
消费时使用条件更新，防止余额变成负数。
定期汇总流水并核对余额，无需在每次交易时重新计算。

使用统一的交易流水表

充值和消费的数据结构基本相同，可以合并到一张表中，并用金额的正负表示资金方向：

充值使用正数，例如 100.00
消费使用负数，例如 -25.50

以下示例基于 SQL Server：

CREATE TABLE dbo.Account
(
    Id          bigint IDENTITY(1,1) PRIMARY KEY,
    CustomerId  bigint NOT NULL,
    Balance     decimal(18,2) NOT NULL
        CONSTRAINT DF_Account_Balance DEFAULT (0),

    CONSTRAINT CK_Account_Balance
        CHECK (Balance >= 0),

    CONSTRAINT FK_Account_Customer
        FOREIGN KEY (CustomerId) REFERENCES dbo.Customer(Id)
);

CREATE TABLE dbo.AccountTransaction
(
    Id                bigint IDENTITY(1,1) PRIMARY KEY,
    AccountId         bigint NOT NULL,
    Amount            decimal(18,2) NOT NULL,
    CreatedAt         datetime2(3) NOT NULL
        CONSTRAINT DF_AccountTransaction_CreatedAt
        DEFAULT (SYSUTCDATETIME()),
    Comment           nvarchar(200) NULL,
    ExternalReference varchar(100) NULL,

    CONSTRAINT CK_AccountTransaction_Amount
        CHECK (Amount <> 0),

    CONSTRAINT FK_AccountTransaction_Account
        FOREIGN KEY (AccountId) REFERENCES dbo.Account(Id)
);

CREATE INDEX IX_AccountTransaction_AccountId_CreatedAt
    ON dbo.AccountTransaction(AccountId, CreatedAt, Id);

CREATE UNIQUE INDEX UX_AccountTransaction_ExternalReference
    ON dbo.AccountTransaction(ExternalReference)
    WHERE ExternalReference IS NOT NULL;

ExternalReference 可以保存订单号、充值单号或请求编号。配合唯一索引，能够避免客户端重试造成重复扣款。

如果业务编号只需在同一个账户内保持唯一，可以改为对 (AccountId, ExternalReference) 建立唯一索引。

原子更新余额

余额更新和流水写入应当同时成功或同时失败。

CREATE PROCEDURE dbo.PostAccountTransaction
    @AccountId bigint,
    @Amount decimal(18,2),
    @Comment nvarchar(200) = NULL,
    @ExternalReference varchar(100) = NULL
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

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

    BEGIN TRY
        BEGIN TRANSACTION;

        UPDATE dbo.Account
        SET Balance = Balance + @Amount
        WHERE Id = @AccountId
          AND Balance + @Amount >= 0;

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

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

        INSERT INTO dbo.AccountTransaction
        (
            AccountId,
            Amount,
            Comment,
            ExternalReference
        )
        VALUES
        (
            @AccountId,
            @Amount,
            @Comment,
            @ExternalReference
        );

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

        THROW;
    END CATCH;
END;

调用方式如下：

-- 充值 100.00
EXEC dbo.PostAccountTransaction
    @AccountId = 10,
    @Amount = 100.00,
    @Comment = N'账户充值',
    @ExternalReference = 'TOPUP-20260901-001';

-- 消费 25.50
EXEC dbo.PostAccountTransaction
    @AccountId = 10,
    @Amount = -25.50,
    @Comment = N'购买商品',
    @ExternalReference = 'ORDER-20260901-008';

余额条件：

Balance + @Amount >= 0

这个条件和余额更新位于同一条 UPDATE 语句中。即使两个消费请求同时到达，SQL Server 也会对更新执行必要的并发控制，避免两个请求先读到相同的旧余额，再分别扣款。

ASP.NET 中的职责划分

Controller 只需校验请求格式并调用应用服务，不应查询全部流水、计算余额后再更新。应用服务负责调用数据库中的原子操作。

下面是一个简化的 ADO.NET 示例：

public async Task PostTransactionAsync(
    long accountId,
    decimal amount,
    string? comment,
    string externalReference,
    CancellationToken cancellationToken)
{
    await using var connection = new SqlConnection(_connectionString);
    await connection.OpenAsync(cancellationToken);

    await using var command =
        new SqlCommand("dbo.PostAccountTransaction", connection)
        {
            CommandType = CommandType.StoredProcedure
        };

    command.Parameters.Add(
        new SqlParameter("@AccountId", SqlDbType.BigInt)
        {
            Value = accountId
        });

    command.Parameters.Add(
        new SqlParameter("@Amount", SqlDbType.Decimal)
        {
            Precision = 18,
            Scale = 2,
            Value = amount
        });

    command.Parameters.Add(
        new SqlParameter("@Comment", SqlDbType.NVarChar, 200)
        {
            Value = (object?)comment ?? DBNull.Value
        });

    command.Parameters.Add(
        new SqlParameter("@ExternalReference", SqlDbType.VarChar, 100)
        {
            Value = externalReference
        });

    await command.ExecuteNonQueryAsync(cancellationToken);
}

使用 Entity Framework 时，也要明确开启数据库事务，并让余额更新和流水插入处于同一个事务中。如果只是读取余额、在 C# 中减去金额，然后保存余额，并发风险依然存在。

用流水汇总结果核对余额

可以通过流水重新计算理论余额：

SELECT
    AccountId,
    SUM(Amount) AS CalculatedBalance
FROM dbo.AccountTransaction
GROUP BY AccountId;

也可以检查缓存余额是否与流水汇总结果一致：

SELECT
    a.Id,
    a.Balance AS StoredBalance,
    COALESCE(SUM(t.Amount), 0) AS CalculatedBalance
FROM dbo.Account AS a
LEFT JOIN dbo.AccountTransaction AS t
    ON t.AccountId = a.Id
GROUP BY
    a.Id,
    a.Balance
HAVING a.Balance <> COALESCE(SUM(t.Amount), 0);

这类查询适合审计、对账和故障修复，不应放进每次交易请求的正常执行流程。

注意事项

初始余额也要写成一条充值流水，不能只修改 Account.Balance。
已入账的流水通常不应直接修改或删除。需要纠错时，应新增一条金额相反的冲正流水。
时间建议使用 datetime2 和 UTC，以免受到 datetime 精度较低和服务器时区的影响。
金额必须使用 decimal，不要使用 float 或 double。
每个业务操作都应提供唯一请求编号，并通过唯一约束保证幂等性。
不能只依赖前端检查余额。余额限制必须由数据库中的原子更新保证。
如果业务允许透支，需要按照实际信用额度调整 Balance >= 0 检查和条件更新。
如果以后需要处理冻结金额、退款、撤销、支付状态、跨账户转账或财务审计，就要增加状态并引入复式记账模型，不能继续使用简单的余额加减模型。