SQL Server 积分增减存储过程实现与防重复逻辑详解

1. 核心数据架构与设计目标

在设计积分获取逻辑前，我们需要明确支撑该功能的四张核心数据表。这些表定义了积分的来源、消耗规则以及最终的用户资产状态：

• GM_JF（客户账户积分表）：存储用户的当前可用积分总额、历史累计积分以及用于并发控制的版本号。注意：实际项目中建议将金额字段设置为 decimal 类型以避免浮点数精度丢失问题。
• GM_JF_DETAIL（积分消费/获取明细表）：记录每一次积分变动的流水，包括变动原因、关联单据号（CARD_NUM）等，用于对账与审计。
• GM_JF_ACTION_RULES（积分动作规则表）：定义不同活动类型的积分策略，最关键的是设置周期内允许的最大获取次数（RepetitionsFrequency）和周期时长（RepetitionsCycle），这是防重复获取的核心依据。
• GM_JF_GOODS_RULES（积分商品规则表）：通常用于积分兑换商品时的汇率或抵扣规则，在本题中主要作为扩展参考。

2. 存储过程核心逻辑深度解析

以下代码展示了如何在一个事务块中完成“规则校验 -> 写入流水 -> 更新总额”的完整流程。重点在于其如何处理边界条件和并发安全。

-- =============================================
-- 测试调用示例
-- declare @StatusCode int = 1;
-- exec sp_GM_JF_AddScore 'admin','AN_JF_001_001',1,5,0,'首次注册赠送',@StatusCode output
-- print @StatusCode -- 返回 1 代表成功，2 代表触发防重复限制
-- =============================================

ALTER PROCEDURE [dbo].[sp_GM_JF_AddScore]
    @ACCOUNT_ID        varchar(30),
    @JF_CategoryNumber varchar(15),
    @CARD_NUM          int,
    @HQ_JF_AMOUNT      int,
    @State             varchar(16),
    @USE_DESC          varchar(400),
    @StatusCode        int output  -- 输出状态：0:失败(异常) 1:成功 2:触发防重复规则
AS
BEGIN
    SET NOCOUNT ON;

    -- 1. 获取规则配置
    declare 
        @repetitionsCycle       float=0,   -- 规则周期天数
        @repetitionsFrequency   int=0,     -- 周期内允许最大次数
        @realFrequency          int=0;     -- 当前已获取次数

    declare @USE_DATE datetime = GETDATE();

    -- 从规则表查询对应的防重限制
    select top(1) 
        @repetitionsCycle=RepetitionsCycle,
        @repetitionsFrequency=RepetitionsFrequency 
    from GM_JF_ACTION_RULES 
    where AN_CategoryNumber=@JF_CategoryNumber;

    -- 2. 核心校验：判断是否重复获取积分
    /*
     * 逻辑说明：统计在最近N个周期内的获取次数。
     * 如果 >= 规则允许的频次，则拒绝本次请求并返回状态码 2。
     */
    if(@repetitionsCycle < 1)
    BEGIN
        -- 情况A：周期小于一天的毫秒级或小时级控制
        set @repetitionsCycle_second = (@repetitionsCycle-1)*24*60*60;
        select @realFrequency=COUNT(1) 
        from GM_JF_DETAIL 
        where ACCOUNT_ID=@ACCOUNT_ID 
          and JF_CategoryNumber=@JF_CategoryNumber 
          and USE_DATE <= @USE_DATE 
          and USE_DATE >= CONVERT(varchar(19),DATEADD(SECOND,-@repetitionsCycle_second,@USE_DATE),120);
    END
    ELSE
    BEGIN
        -- 情况B：按天计算的周期控制
        select @realFrequency=COUNT(1) 
        from GM_JF_DETAIL 
        where ACCOUNT_ID=@ACCOUNT_ID 
          and JF_CategoryNumber=@JF_CategoryNumber 
          and USE_DATE <= @USE_DATE 
          and USE_DATE >= CONVERT(varchar(10),DATEADD(DAY,-(@repetitionsCycle-1),@USE_DATE),120);
    END

    if(@realFrequency >= @repetitionsFrequency)
    begin
        set @StatusCode = 2; -- 提示前端：不可重复获取
        return 2;
    end

    -- 3. 执行事务：确保明细插入与余额更新同生共死
    begin tran;

    -- Step 1: 插入详情流水 (原子操作的第一步)
    insert into GM_JF_DETAIL 
    (ACCOUNT_ID, JF_CategoryNumber, CARD_NUM, HQ_JF_AMOUNT, [State], USE_DESC)
    values 
    (@ACCOUNT_ID, @JF_CategoryNumber, @CARD_NUM, @HQ_JF_AMOUNT, @State, @USE_DESC);

    -- Step 2: 读取当前余额并进行增量更新
    -- 这里使用临时表变量暂存读取结果，模拟了部分ORM框架的事务隔离行为
    declare @temp_table table 
    (
        ACCOUNT_ID varchar(30),
        JF_AMOUNT decimal(16,2),
        TTL_JF_AMOUNT decimal(16,2),
        Last_Update_Time datetime,
        [Version] int
    );

    -- 尝试获取当前用户已有的记录
    insert into @temp_table 
    select ACCOUNT_ID, JF_AMOUNT, TTL_JF_AMOUNT, Last_Update_Time, [Version] 
    from GM_JF 
    where ACCOUNT_ID=@ACCOUNT_ID;

    declare @count int = count(1) from @temp_table;

    IF(@count=0)
    begin
        -- 用户首次获取积分，直接初始化余额
        insert into GM_JF 
        (ACCOUNT_ID, JF_AMOUNT, TTL_JF_AMOUNT)
        values 
        (@ACCOUNT_ID, @HQ_JF_AMOUNT, @HQ_JF_AMOUNT);
    end
    else
    begin
        -- 用户已有积分，采用读取后更新的逻辑
        declare @JF_AMOUNT int, @TTL_JF_AMOUNT int, @Version int;

        select @JF_AMOUNT=JF_AMOUNT, @TTL_JF_AMOUNT=TTL_JF_AMOUNT, @Version=[Version] 
        from @temp_table 
        where ACCOUNT_ID=@ACCOUNT_ID;

        -- 更新总积分表：累加值 + 递增版本号
        update GM_JF 
        set 
            JF_AMOUNT=(@JF_AMOUNT+@HQ_JF_AMOUNT),
            TTL_JF_AMOUNT=(@TTL_JF_AMOUNT+@HQ_JF_AMOUNT),
            Last_Update_Time=GETDATE(),
            [Version]=(@Version+1)
        where ACCOUNT_ID=@ACCOUNT_ID;
    end

    -- 提交事务
    Commit tran;
    set @StatusCode = 1; -- 操作成功

    -- 错误捕获回滚
    IF(@@ERROR <> 0)
    BEGIN
        set @StatusCode = 0; -- 操作失败
        ROLLBACK tran;
    END
END;

3. 关键技术与最佳实践点评

第1步：防御型查询与时间窗口设计

在 if(@repetitionsCycle<1) 分支中，代码处理了一种特殊的边界情况：当配置的小数天（例如 0.5 天即12小时，或者更小的数值表示秒级/毫秒级高频活动）时，SQL Server 需要手动将其转换为秒进行日期加减运算。这种双重判断结构有效涵盖了从长期促销（按天）到短时抢红包（按秒）的不同业务场景。

第2步：显式事务与原子性保障

整个过程包裹在 begin tran ... Commit tran 中。这是一个关键的工程实践。即使 INSERT INTO GM_JF_DETAIL 成功，如果在后续更新用户余额时发生错误（如磁盘满、死锁），整个操作将被 ROLLBACK。这保证了流水存在但余额未加或余额加了但没流水这类脏数据永远不会产生。

第3步：关于并发安全的局限性分析

虽然代码使用了事务，但其更新逻辑属于经典的“读-改-写”模式：
1. 先 `select` 读出 @JF_AMOUNT；
2. 再 `update` 写入 @JF_AMOUNT + @HQ_JF_AMOUNT。

**风险提醒**：在极高并发下（如秒杀活动），两个线程可能在同一时刻都读到了相同的旧版本金额，导致其中一笔增加被覆盖。上述代码末尾虽然增加了 `[Version]` 字段并在更新时加1，但在 T-SQL 标准写法中，如果没有在 `WHERE` 子句中严格约束 `where Version = @OldVersion`，这个版本号仅具有记录意义而**不具备强制并发互斥的能力**。对于生产环境的高频交易场景，强烈建议直接使用 `UPDATE GM_JF SET TTL_JF_AMOUNT = TTL_JF_AMOUNT + @HQ_JF_AMOUNT WHERE ACCOUNT_ID = @ACCOUNT_ID` 这种方式，让数据库引擎自身处理加法的原子性。

4. 总结与注意事项

本示例提供了一个完整的积分获取闭环逻辑，特别是针对“防刷单”的时间窗口算法具有一定的通用参考价值。但在落地生产环境时，请注意以下几点：

• 数据类型修正：原始草稿中将 JF_AMOUNT 声明为 int，若涉及金额拆分（角/分），应改为 decimal(18,2) 或 decimal(16,2)。
• 索引优化：确保 GM_JF_DETAIL 表上有复合索引 (ACCOUNT_ID, JF_CategoryNumber, USE_DATE)，否则频繁的区间计数会导致全表扫描，严重拖慢性能。
• 事务超时控制：在实际运行长事务时，建议在连接字符串或 SQL 层设置合理的 CommandTimeout。