您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server 積分系統存儲過程實戰：獲取積分、防重複、事務一致性完整案例

編輯：關於SqlServer

業務背景與表結構

某積分商城系統，核心四表：

GM_JF：客戶總積分表 (帳戶ID、可用積分、總積分、版本號...)
GM_JF_DETAIL：積分明細流水表 (帳戶ID、動作分類、卡號、獲取積分、狀態、描述、時間...)
GM_JF_ACTION_RULES：積分動作規則表 (分類編號、周期天數、周期內最大獲取次數...)
GM_JF_GOODS_RULES：積分商品兌換規則表

核心存儲過程：sp_GM_JF_AddScore (積分獲取/增加)

ALTER PROCEDURE [dbo].[sp_GM_JF_AddScore]
    @ACCOUNT_ID          VARCHAR(30),      -- 帳戶ID
    @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;

    DECLARE 
        @repetitionsCycle      FLOAT = 0,   -- 規則周期 (天)
        @repetitionsCycleSec   INT = 0,     -- 規則周期 (秒)
        @repetitionsFrequency  INT = 0,     -- 周期內最大允許次數
        @realFrequency         INT = 0,     -- 實際已獲取次數
        @USE_DATE              DATETIME = GETDATE();

    -- 1. 讀取規則：周期與頻率限制
    SELECT TOP 1 
        @repetitionsCycle = RepetitionsCycle,
        @repetitionsFrequency = RepetitionsFrequency
    FROM GM_JF_ACTION_RULES 
    WHERE AN_CategoryNumber = @JF_CategoryNumber;

    -- 2. 判斷是否重複獲取 (核心防刷邏輯)
    IF (@repetitionsCycle < 1)  -- 周期小於 1 天，按秒計算
    BEGIN
        SET @repetitionsCycleSec = CAST((@repetitionsCycle - 1) * 24 * 60 * 60 AS INT);
        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, -@repetitionsCycleSec, @USE_DATE), 120);
    END
    ELSE  -- 周期 >= 1 天，按天計算
    BEGIN
        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

    -- 3. 實際次數 >= 允許次數 → 重複獲取，直接返回 2
    IF (@realFrequency >= @repetitionsFrequency)
    BEGIN
        SET @StatusCode = 2;
        RETURN 2;
    END

    -- 4. 事務處理：寫明細 + 更新總積分表 (樂觀鎖版本號)
    DECLARE @count INT = 0;
    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
    );

    BEGIN TRANSACTION;
    BEGIN TRY
        -- 4.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);

        -- 4.2 讀取總積分表當前狀態 (加鎖讀取防併發)
        INSERT INTO @temp_table
        SELECT ACCOUNT_ID, JF_AMOUNT, TTL_JF_AMOUNT, Last_Update_Time, [Version]
        FROM GM_JF WITH (UPDLOCK, HOLDLOCK)  -- 關鍵：更新鎖+持有至事務結束
        WHERE ACCOUNT_ID = @ACCOUNT_ID;

        SELECT @count = COUNT(1) FROM @temp_table;

        -- 4.3 更新或插入總積分
        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 DECIMAL(16,2), @TTL_JF_AMOUNT DECIMAL(16,2), @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 AND [Version] = @Version;  -- 版本號檢查

            IF @@ROWCOUNT = 0
            BEGIN
                -- 版本號不匹配，併發衝突
                RAISERROR('併發衝突，請重試', 16, 1);
            END
        END

        COMMIT TRANSACTION;
        SET @StatusCode = 1;
    END TRY
    BEGIN CATCH
        SET @StatusCode = 0;
        IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
    END CATCH
END

核心設計模式解析

1. 規則驅動的防重複邏輯

規則表定義 RepetitionsCycle (周期) 和 RepetitionsFrequency (最大次數)。
支持「天級」和「秒級」兩種周期粒度，靈活適配「每日一次」、「每小時 3 次」等場景。
查詢明細表統計時間窗口內記錄數，與規則比較。

2. 表變量 + UPDLOCK/HOLDLOCK 實現樂觀鎖

先將總積分表當前行讀入表變量 (@temp_table)。
WITH (UPDLOCK, HOLDLOCK)：加更新鎖並持有到事務結束，防止併發讀取髒數據，配合版本號實現樂觀鎖。
更新時 WHERE Version = @Version，若行數為 0 則拋錯回滾。

3. 事務邊界清晰、錯誤處理完善

明確 BEGIN TRAN / COMMIT / ROLLBACK。
TRY...CATCH 捕獲異常，XACT_STATE() 判斷事務狀態決定是否回滾。
返回碼語義明確：0=系統錯誤, 1=成功, 2=業務重複。

4. 幂等性與重試友好

版本號機制天然支持應用層重試：衝突時拋錯，應用捕獲後重新調用存儲過程即可。
明細表可加唯一索引 (ACCOUNT_ID, JF_CategoryNumber, CARD_NUM, USE_DATE) 防止極端情況下重複寫入。

調用示例

DECLARE @StatusCode INT;

-- 測試：用戶 admin 完成動作 AN_JF_001_001 獲取 5 分
EXEC sp_GM_JF_AddScore 
    'admin', 'AN_JF_001_001', 1, 5, 'Normal', '首次登錄獎勵', @StatusCode OUTPUT;

PRINT '返回碼: ' + CAST(@StatusCode AS VARCHAR);  -- 1=成功, 2=重複, 0=失敗

擴展建議：積分消費/兌換存儲過程骨架

CREATE PROCEDURE dbo.sp_GM_JF_ConsumeScore
    @ACCOUNT_ID VARCHAR(30),
    @GoodsCode  VARCHAR(20),
    @Quantity   INT,
    @StatusCode INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRANSACTION;
    BEGIN TRY
        -- 1. 讀取商品規則 (所需積分、庫存、限購)
        -- 2. 讀取用戶總積分 (UPDLOCK)
        -- 3. 校驗：積分夠嗎、庫存夠嗎、限購超嗎
        -- 4. 扣減總積分 (版本號樂觀鎖)
        -- 5. 寫入消費明細 (GM_JF_DETAIL, HQ_JF_AMOUNT 存負數或單獨消費表)
        -- 6. 扣減商品庫存
        COMMIT TRANSACTION;
        SET @StatusCode = 1;
    END TRY
    BEGIN CATCH
        SET @StatusCode = 0;
        IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
    END CATCH
END