萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

實用銀行轉帳預存程序與流水號產生器 (含並發控制)

銀行轉帳預存程序

採用顯式交易、餘額檢查、錯誤處理,確保 ACID。

USE [BankInfor]
GO
SET ANSI_NULLS ON; SET QUOTED_IDENTIFIER ON;
GO
ALTER PROCEDURE [dbo].[Transfer]
    @inAccount INT,      -- 轉入帳號
    @outAccount INT,     -- 轉出帳號
    @amount FLOAT        -- 金額
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @totalDeposit FLOAT;

    BEGIN TRY
        BEGIN TRANSACTION;

        -- 鎖定轉出帳戶列 (U 鎖升級 X 鎖),防止並發超扣
        SELECT @totalDeposit = total FROM Account WITH (UPDLOCK, ROWLOCK)
        WHERE AccountNum = @outAccount;

        IF @totalDeposit IS NULL
            THROW 50001, '轉出帳戶不存在', 1;

        IF @totalDeposit < @amount
            THROW 50002, '餘額不足,無法轉帳', 1;

        UPDATE Account SET total = total - @amount WHERE AccountNum = @outAccount;
        UPDATE Account SET total = total + @amount WHERE AccountNum = @inAccount;

        COMMIT TRANSACTION;
        PRINT '轉帳成功!';
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
        THROW;  -- 重拋錯誤供上層處理
    END CATCH
END

流水號產生預存程序 (高並發安全)

使用**單一資料表**儲存各業務代碼的當前流水值,交易內 `UPDATE` 取得鎖天然串行化,無需額外 `sp_getapplock`。

IF EXISTS (SELECT 1 FROM sysobjects WHERE id = OBJECT_ID('GetSerialNo') AND xtype = 'P')
    DROP PROC GetSerialNo
GO

CREATE PROCEDURE [dbo].[GetSerialNo]
    @sCode VARCHAR(50)  -- 業務代碼 (如 'ORD', 'INV')
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @sValue VARCHAR(16), @dToday DATETIME, @sQZ VARCHAR(50);

    BEGIN TRANSACTION;
    BEGIN TRY
        -- 1. 取得前綴
        SELECT @sQZ = sQZ FROM SerialNo WHERE sCode = @sCode;

        -- 2. 鎖定並讀取當前值 (若無則插入初始值)
        UPDATE SerialNo SET sValue = sValue WHERE sCode = @sCode;  -- 取得 U/X 鎖
        SELECT @sValue = sValue FROM SerialNo WHERE sCode = @sCode;

        IF @sValue IS NULL
        BEGIN
            -- 首次使用:年月日(YYMMDD) + 000001
            SET @sValue = CONVERT(VARCHAR(6), GETDATE(), 12) + '000001';
            UPDATE SerialNo SET sValue = @sValue WHERE sCode = @sCode;
            IF @@ROWCOUNT = 0  -- 並發插入競爭
                INSERT SerialNo (sCode, sValue, sQZ) VALUES (@sCode, @sValue, @sQZ);
        END
        ELSE
        BEGIN
            -- 檢查日期是否跨日
            SET @dToday = CONVERT(VARCHAR(6), GETDATE(), 12);
            IF LEFT(@sValue, 6) = @dToday
                SET @sValue = CONVERT(VARCHAR(16), CONVERT(BIGINT, @sValue) + 1);
            ELSE
                SET @sValue = @dToday + '000001';  -- 新一天重置

            UPDATE SerialNo SET sValue = @sValue WHERE sCode = @sCode;
        END

        COMMIT TRANSACTION;
        SELECT Result = @sQZ + @sValue;  -- 回傳完整流水號
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
        SELECT Result = 'Error';
    END CATCH
END

SerialNo 表結構

CREATE TABLE SerialNo (
    sCode VARCHAR(50) PRIMARY KEY,  -- 業務代碼
    sValue VARCHAR(16) NOT NULL,    -- 當前流水值 (YYMMDD + 6位序號)
    sQZ VARCHAR(50) NOT NULL        -- 前綴 (如 'ORD-')
);

關鍵設計點解析

  • UPDATE 先行鎖定:`UPDATE ... SET col = col WHERE pk = @val` 在讀取前先取得更新鎖 (U),有效避免「讀取-計算-更新」間隙的競爭條件。
  • 單一交易包裹:讀取、計算、寫回在同一交易,原子性保證。
  • 日期自動重置**:跨日自動歸零,無需排程清理。
  • 錯誤處理**:`TRY...CATCH` 確保交易回滾,回傳統一錯誤標記。
  • 擴充性**:`sQZ` 前綴支援業務區分 (如 `ORD-240115000001`)。

效能與替代方案

  • 極高並發 (萬級 TPS) 時,單表熱點可能成為瓶頸。可採用 **Sequence 物件** (SQL 2012+) 或 **HiLo 演算法** (應用端預取區間) 分散寫入壓力。
  • Sequence 範例:`CREATE SEQUENCE Seq_Order AS BIGINT START WITH 1 INCREMENT BY 1; SELECT NEXT VALUE FOR Seq_Order;`
copyright © 萬盛學電腦網 all rights reserved