銀行轉帳與流水號生成：兩大高併發存儲過程生產級實戰

一、銀行轉帳存儲過程：ACID 保障、餘額檢查、錯誤碼回傳

CREATE PROCEDURE dbo.usp_TransferAccount
    @OutAccount INT,      -- 轉出帳號
    @InAccount INT,       -- 轉入帳號
    @Amount DECIMAL(19,4),-- 金額
    @ResultCode INT OUTPUT, -- 0=成功, 1=轉出不存在, 2=餘額不足, 3=轉入不存在, 99=系統錯誤
    @ResultMsg VARCHAR(200) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON; -- 任何執行錯誤自動回滾

    DECLARE @OutBalance DECIMAL(19,4);

    BEGIN TRY
        BEGIN TRANSACTION;

        -- 1. 鎖定轉出帳號列 (U 鎖升級 X 鎖)，檢查存在與餘額
        SELECT @OutBalance = Balance FROM dbo.Account WITH (UPDLOCK, ROWLOCK) WHERE AccountNo = @OutAccount;

        IF @OutBalance IS NULL
        BEGIN
            SET @ResultCode = 1; SET @ResultMsg = '轉出帳號不存在'; GOTO ErrHandler;
        END

        IF @OutBalance < @Amount
        BEGIN
            SET @ResultCode = 2; SET @ResultMsg = '餘額不足'; GOTO ErrHandler;
        END

        -- 2. 扣減轉出
        UPDATE dbo.Account SET Balance = Balance - @Amount WHERE AccountNo = @OutAccount;

        -- 3. 增加轉入 (若不存在可選擇建立或報錯)
        IF NOT EXISTS (SELECT 1 FROM dbo.Account WHERE AccountNo = @InAccount)
        BEGIN
            SET @ResultCode = 3; SET @ResultMsg = '轉入帳號不存在'; GOTO ErrHandler;
        END
        UPDATE dbo.Account SET Balance = Balance + @Amount WHERE AccountNo = @InAccount;

        -- 4. 記錄流水 (建議另寫獨立交易或異步，此處簡化)
        INSERT INTO dbo.TransferLog (OutAccount, InAccount, Amount, CreateTime)
        VALUES (@OutAccount, @InAccount, @Amount, SYSDATETIME());

        COMMIT TRANSACTION;
        SET @ResultCode = 0; SET @ResultMsg = '轉帳成功';
        RETURN 0;

    ErrHandler:
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
        RETURN @ResultCode;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
        SET @ResultCode = 99;
        SET @ResultMsg = '系統錯誤: ' + ERROR_MESSAGE();
        RETURN 99;
    END CATCH
END;

關鍵點解析

• `SET XACT_ABORT ON`：確保任何運行時錯誤 (除鎖死) 直接回滾。
• `WITH (UPDLOCK, ROWLOCK)`：`SELECT` 階段即加 `U` 鎖，防止併發讀取髒資料並減少死鎖機率。
• 固定鎖順序：永遠**先鎖轉出、後鎖轉入**，避免 AB-BA 死鎖。
• 輸出參數回傳業務狀態，避免 `RAISERROR` 衝擊外層交易。

二、高併發流水號生成存儲過程：日期前綴 + 當日遞增、無鎖表設計

CREATE PROCEDURE dbo.usp_GetSerialNo
    @Code VARCHAR(50),        -- 業務代碼 (如 'ORD', 'PAY')
    @SerialNo VARCHAR(30) OUTPUT,
    @ResultCode INT OUTPUT    -- 0=成功, 1=參數錯誤, 99=系統錯誤
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    BEGIN TRY
        BEGIN TRANSACTION;

        -- 1. 嘗試鎖定並更新 (單陳述式原子操作)
        --    若不存在，INSERT 失敗會拋錯，由 CATCH 處理
        UPDATE dbo.SerialNo WITH (UPDLOCK, ROWLOCK)
        SET CurrentValue = CASE
            WHEN DATE_PREFIX = CONVERT(CHAR(6), GETDATE(), 12) THEN CurrentValue + 1
            ELSE CONVERT(BIGINT, CONVERT(CHAR(6), GETDATE(), 12) + '000001')
        END,
        DATE_PREFIX = CONVERT(CHAR(6), GETDATE(), 12)
        WHERE Code = @Code;

        -- 2. 讀取更新後的值
        IF @@ROWCOUNT = 0
        BEGIN
            -- 首次使用，插入初始值
            INSERT INTO dbo.SerialNo (Code, Date_Prefix, CurrentValue, Prefix)
            VALUES (@Code, CONVERT(CHAR(6), GETDATE(), 12), CONVERT(BIGINT, CONVERT(CHAR(6), GETDATE(), 12) + '000001'), @Code);
        END

        SELECT @SerialNo = Prefix + CAST(CurrentValue AS VARCHAR(20))
        FROM dbo.SerialNo WHERE Code = @Code;

        COMMIT TRANSACTION;
        SET @ResultCode = 0;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
        SET @ResultCode = 99;
        -- 可選：記錄錯誤日誌
    END CATCH
END;

支援表結構

CREATE TABLE dbo.SerialNo (
    Code VARCHAR(50) PRIMARY KEY,      -- 業務代碼
    Prefix VARCHAR(10) NOT NULL,       -- 前綴 (通常同 Code)
    Date_Prefix CHAR(6) NOT NULL,      -- 日期前綴 yyyymm
    CurrentValue BIGINT NOT NULL,      -- 當日流水值
    UpdatedAt DATETIME2 DEFAULT SYSDATETIME()
);

無鎖表替代方案 (極高併發)：SEQUENCE (SQL 2012+)

CREATE SEQUENCE dbo.Seq_OrderNo
    AS BIGINT START WITH 1 INCREMENT BY 1
    MINVALUE 1 MAXVALUE 9999999999 CYCLE CACHE 1000;

-- 取號 (無需鎖表、極快)
SELECT NEXT VALUE FOR dbo.Seq_OrderNo;

結合日期前綴：`FORMAT(GETDATE(), 'yyyyMM') + RIGHT('000000' + CAST(NEXT VALUE FOR dbo.Seq_OrderNo AS VARCHAR(10)), 6)`

總結

生產環境務必配合**壓力測試** (模擬 1000+ 併發) 驗證吞吐與延遲，並建立**死鎖/超時監控告警**。