PAYATHON 2026

如何表示当前状态并维护数据与 referential integrity？

支付小周

结论

一致性确实需要关注，但在 LOANS 中直接保存 StatusID 并不算设计错误。这是常见的“当前状态快照”做法。真正需要解决的是：数据库能否保证状态更新与事件写入在同一个原子操作中完成。

不建议根据普通的 lastEvent 推导状态，因为最后发生的事件可能只是 payment、备注修改等不会改变状态的事件。更准确的设计是：

  • LOAN_EVENTS 记录所有业务事件。
  • LOAN_STATE_CHANGES 只记录引起状态变化的事件及变化结果。
  • 可选的 LOAN_CURRENT_STATE 指向最近一次状态变化事件。
  • 所有相关写入都在同一个事务或存储过程中完成。

采用这种结构后，既能查询当前状态，也能找到造成该状态的具体事件。组合外键还可以确保该事件属于同一笔贷款。

为什么单独保存 StatusID 仍可能合理

例如：

LOANS
-----
LoanID
StatusID

LOAN_EVENTS
-----------
EventID
LoanID
EventTypeID
OccurredAt

这里的两个外键只能分别保证：

  • StatusID 是合法状态。
  • LOAN_EVENTS.LoanID 指向一笔存在的贷款。

它们无法保证“这笔贷款处于 defaulted 状态，是因为发生了相应的 default 事件”。这是跨表业务约束，普通外键无法直接表达。

不过，这种设计并非不可接受。只要状态和事件只能通过一个存储过程更新，而且两个操作处于同一个事务中，一致性就能得到可靠保证。很多系统采用这种方式，因为查询当前状态很方便。

风险来自应用程序分别执行以下操作：

UPDATE LOANS SET StatusID = @Defaulted WHERE LoanID = @LoanID;

以及：

INSERT INTO LOAN_EVENTS (...) VALUES (...);

其中任何一步失败，或者某段代码绕过正常流程，都可能产生不一致的数据。

推荐的数据模型

可以把“状态变化”建模为普通事件的子类型：

CREATE TABLE LOAN_STATES
(
    StateID      int          NOT NULL PRIMARY KEY,
    StateCode    varchar(30)  NOT NULL UNIQUE,
    StateName    varchar(100) NOT NULL
);

CREATE TABLE EVENT_TYPES
(
    EventTypeID   int          NOT NULL PRIMARY KEY,
    EventTypeCode varchar(30)  NOT NULL UNIQUE,
    EventTypeName varchar(100) NOT NULL
);

CREATE TABLE LOANS
(
    LoanID     int         NOT NULL PRIMARY KEY,
    CreatedAt  datetime    NOT NULL
        CONSTRAINT DF_LOANS_CreatedAt DEFAULT GETUTCDATE()
);

CREATE TABLE LOAN_EVENTS
(
    EventID       bigint       IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    LoanID        int          NOT NULL,
    EventSequence int          NOT NULL,
    EventTypeID   int          NOT NULL,
    OccurredAt    datetime     NOT NULL
        CONSTRAINT DF_LOAN_EVENTS_OccurredAt DEFAULT GETUTCDATE(),

    CONSTRAINT FK_LOAN_EVENTS_LOANS
        FOREIGN KEY (LoanID) REFERENCES LOANS(LoanID),

    CONSTRAINT FK_LOAN_EVENTS_EVENT_TYPES
        FOREIGN KEY (EventTypeID) REFERENCES EVENT_TYPES(EventTypeID),

    CONSTRAINT UQ_LOAN_EVENTS_LoanSequence
        UNIQUE (LoanID, EventSequence),

    CONSTRAINT UQ_LOAN_EVENTS_LoanEvent
        UNIQUE (LoanID, EventID)
);

CREATE TABLE LOAN_STATE_CHANGES
(
    EventID      bigint NOT NULL PRIMARY KEY,
    LoanID       int    NOT NULL,
    FromStateID  int    NULL,
    ToStateID    int    NOT NULL,

    CONSTRAINT FK_LOAN_STATE_CHANGES_EVENT
        FOREIGN KEY (LoanID, EventID)
        REFERENCES LOAN_EVENTS(LoanID, EventID),

    CONSTRAINT FK_LOAN_STATE_CHANGES_FROM_STATE
        FOREIGN KEY (FromStateID) REFERENCES LOAN_STATES(StateID),

    CONSTRAINT FK_LOAN_STATE_CHANGES_TO_STATE
        FOREIGN KEY (ToStateID) REFERENCES LOAN_STATES(StateID),

    CONSTRAINT UQ_LOAN_STATE_CHANGES_LoanEvent
        UNIQUE (LoanID, EventID)
);

普通 payment 只需写入 LOAN_EVENTS。如果 payoff 使贷款变为 paid，同一个 EventID 也要写入 LOAN_STATE_CHANGES。

这种方式比根据 EventTypeID 临时推断状态更可靠。事件类型说明“发生了什么”，ToStateID 明确记录“该事件当时产生了什么状态结果”。即使后续业务规则发生变化，历史事件的含义也不会随之改变。

直接从历史记录计算当前状态

如果数据量和查询频率允许，可以不保存当前状态指针，直接查询最后一次状态变化：

SELECT TOP 1
       sc.ToStateID,
       sc.EventID,
       e.EventTypeID,
       e.OccurredAt
FROM LOAN_STATE_CHANGES AS sc
JOIN LOAN_EVENTS AS e
  ON e.LoanID = sc.LoanID
 AND e.EventID = sc.EventID
WHERE sc.LoanID = @LoanID
ORDER BY e.EventSequence DESC;

排序时应使用明确的业务顺序字段，不能只依赖 OccurredAt。SQL Server 2005 的 datetime 精度有限，两个事件可能具有相同时间。补录事件的业务时间也可能早于录入时间。

EventSequence 应表示事件应用到该贷款的先后顺序，并通过 UNIQUE (LoanID, EventSequence) 防止重复。

这个方案不保存冗余状态，因此不会出现 LOANS.StatusID 与历史记录不一致的问题。代价是每次读取当前状态都要查询历史表。面对大量列表查询、报表或高并发访问时，可能仍需缓存当前状态。

使用当前状态指针

如果需要提高读取速度，可以增加一个一对一的当前状态表：

CREATE TABLE LOAN_CURRENT_STATE
(
    LoanID             int    NOT NULL PRIMARY KEY,
    StateChangeEventID bigint NOT NULL,

    CONSTRAINT FK_LOAN_CURRENT_STATE_LOAN
        FOREIGN KEY (LoanID) REFERENCES LOANS(LoanID),

    CONSTRAINT FK_LOAN_CURRENT_STATE_EVENT
        FOREIGN KEY (LoanID, StateChangeEventID)
        REFERENCES LOAN_STATE_CHANGES(LoanID, EventID)
);

查询当前状态：

SELECT sc.ToStateID,
       sc.EventID
FROM LOAN_CURRENT_STATE AS cs
JOIN LOAN_STATE_CHANGES AS sc
  ON sc.LoanID = cs.LoanID
 AND sc.EventID = cs.StateChangeEventID
WHERE cs.LoanID = @LoanID;

组合外键：

FOREIGN KEY (LoanID, StateChangeEventID)
REFERENCES LOAN_STATE_CHANGES(LoanID, EventID)

这个组合外键可以防止贷款 A 的当前状态指针错误地引用贷款 B 的状态变化事件。

它也避免了 LOANS 和 LOAN_EVENTS 之间的直接循环引用。数据可以按以下顺序创建：

  1. 插入 LOANS。
  2. 插入初始事件。
  3. 插入对应的 LOAN_STATE_CHANGES。
  4. 插入 LOAN_CURRENT_STATE。

这些操作应放在同一个事务中。

通过事务完成状态变化

状态事件、状态变化记录和当前状态指针必须一起提交或一起回滚：

BEGIN TRY
    SET XACT_ABORT ON;
    BEGIN TRANSACTION;

    DECLARE @EventID bigint;
    DECLARE @NextSequence int;

    SELECT @NextSequence = ISNULL(MAX(EventSequence), 0) + 1
    FROM LOAN_EVENTS WITH (UPDLOCK, HOLDLOCK)
    WHERE LoanID = @LoanID;

    INSERT INTO LOAN_EVENTS
    (
        LoanID,
        EventSequence,
        EventTypeID,
        OccurredAt
    )
    VALUES
    (
        @LoanID,
        @NextSequence,
        @EventTypeID,
        GETUTCDATE()
    );

    SET @EventID = SCOPE_IDENTITY();

    INSERT INTO LOAN_STATE_CHANGES
    (
        EventID,
        LoanID,
        FromStateID,
        ToStateID
    )
    VALUES
    (
        @EventID,
        @LoanID,
        @FromStateID,
        @ToStateID
    );

    IF EXISTS
    (
        SELECT 1
        FROM LOAN_CURRENT_STATE WITH (UPDLOCK, HOLDLOCK)
        WHERE LoanID = @LoanID
    )
    BEGIN
        UPDATE LOAN_CURRENT_STATE
        SET StateChangeEventID = @EventID
        WHERE LoanID = @LoanID;
    END
    ELSE
    BEGIN
        INSERT INTO LOAN_CURRENT_STATE
        (
            LoanID,
            StateChangeEventID
        )
        VALUES
        (
            @LoanID,
            @EventID
        );
    END;

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

    DECLARE @ErrorMessage nvarchar(4000);
    SET @ErrorMessage = ERROR_MESSAGE();

    RAISERROR(@ErrorMessage, 16, 1);
END CATCH;

这段示意代码兼容 SQL Server 2005。实际实现还应在写入前锁定并读取当前状态，确认 @FromStateID 与数据库中的当前状态一致，避免两个用户同时对同一笔贷款执行相互冲突的操作。

还应验证合法的状态转换

事件存在，并不代表状态转换在业务上一定合法。例如，系统可能不允许贷款从 voided 重新变为 open。

可以增加一张允许转换表：

CREATE TABLE LOAN_STATE_TRANSITIONS
(
    FromStateID int NOT NULL,
    ToStateID   int NOT NULL,

    CONSTRAINT PK_LOAN_STATE_TRANSITIONS
        PRIMARY KEY (FromStateID, ToStateID),

    CONSTRAINT FK_TRANSITIONS_FROM_STATE
        FOREIGN KEY (FromStateID) REFERENCES LOAN_STATES(StateID),

    CONSTRAINT FK_TRANSITIONS_TO_STATE
        FOREIGN KEY (ToStateID) REFERENCES LOAN_STATES(StateID)
);

状态变更存储过程先进行检查：

IF NOT EXISTS
(
    SELECT 1
    FROM LOAN_STATE_TRANSITIONS
    WHERE FromStateID = @CurrentStateID
      AND ToStateID = @ToStateID
)
BEGIN
    RAISERROR('Invalid loan state transition.', 16, 1);
    RETURN;
END;

初始状态可以交给专门的“创建贷款”流程处理，因为它没有通常意义上的 FromStateID。

主要注意事项

  • 不要把普通 lastEvent 当作当前状态事件。最后发生的 payment 很可能不会改变状态。
  • 不要只根据事件类型推导历史状态。相同事件在不同上下文中可能产生不同结果，业务规则也可能随时间变化。
  • 当前状态指针只能证明它指向一个合法且属于该贷款的状态事件。仅凭外键，无法证明它一定指向时间顺序上的最后一个事件。这个约束需要由事务化存储过程、触发器或查询时的动态计算来保证。
  • 不要把可修改的业务时间作为唯一排序依据。应使用贷款内单调递增的 EventSequence。
  • 事件和状态历史通常应视为 append-only。需要修正错误时，最好增加冲正或纠正事件，不要直接修改或删除旧事件。
  • 应禁止应用账号直接更新相关表，只授予其执行存储过程的权限，否则任何约束方案都有可能被绕过。
  • SQL Server 的 CHECK constraint 不能通过子查询验证另一张表，因此这类跨表时序规则无法只靠一个 CHECK 完成。

如果系统更看重查询性能和实现简单，可以在 LOANS 中保留 StatusID，同时保存造成该状态的 StateChangeEventID，并在同一个事务中维护两者。如果更看重审计一致性，可以把状态变化历史作为事实来源，再通过查询或 LOAN_CURRENT_STATE 指针获取当前状态。

备注：内容仅供参考。