讓存儲過程事務「要麼全做，要麼全不做」

事務是數據一致性的基石。在存儲過程中正確使用事務，需掌握 XACT_ABORT、保存點、錯誤捕獲、嵌套事務計數等核心機制。本文提供生產級模板與反模式分析。

核心概念速覽

• ACID：原子性、一致性、隔離性、持久性。
• @@TRANCOUNT：當前會話活躍事務嵌套層數。BEGIN TRAN +1，COMMIT -1，ROLLBACK 歸零。
• XACT_ABORT ON：運行時錯誤（如主鍵衝突、轉換失敗）導致整個事務終止並回滾，否則僅回滾當前語句，後續語句繼續執行 —— 極易造成數據不一致。
• 保存點：SAVE TRANSACTION name 允許部分回滾至標記點，外層事務可繼續提交。

生產級存儲過程事務模板

CREATE PROCEDURE [dbo].[usp_TransferFunds]
    @FromAccount INT, @ToAccount INT, @Amount MONEY
AS
BEGIN
    SET NOCOUNT ON;           -- 抑制 DONE_IN_PROC 消息，減少網絡往返
    SET XACT_ABORT ON;        -- 核心：任何運行時錯誤直接回滾整個事務

    BEGIN TRY
        BEGIN TRANSACTION;    -- 開始外層事務

        -- 業務邏輯：扣款
        UPDATE Account SET Balance -= @Amount WHERE Id = @FromAccount;
        IF @@ROWCOUNT = 0 THROW 50001, '轉出賬戶不存在', 1;

        -- 保存點：入賬前標記
        SAVE TRANSACTION BeforeDeposit;

        -- 業務邏輯：入賬
        UPDATE Account SET Balance += @Amount WHERE Id = @ToAccount;
        IF @@ROWCOUNT = 0 THROW 50002, '轉入賬戶不存在', 1;

        -- 記錄流水
        INSERT INTO TransactionLog (FromAcct, ToAcct, Amount, TxnTime)
        VALUES (@FromAccount, @ToAccount, @Amount, SYSDATETIME());

        COMMIT TRANSACTION;   -- 全部成功，提交
    END TRY
    BEGIN CATCH
        -- 判斷是否有活躍事務
        IF @@TRANCOUNT > 0
        BEGIN
            -- 回滾到保存點 (若存在) 或完整回滾
            IF XACT_STATE() = 1 -- 事務可提交狀態
                ROLLBACK TRANSACTION BeforeDeposit; -- 部分回滾
            ELSE
                ROLLBACK TRANSACTION;               -- 完整回滾 (XACT_STATE() = -1 不可提交)
        END

        -- 記錄錯誤日誌 (可選)
        INSERT INTO ErrorLog (ProcName, ErrorNumber, ErrorMessage, ErrorTime)
        VALUES (OBJECT_NAME(@@PROCID), ERROR_NUMBER(), ERROR_MESSAGE(), SYSDATETIME());

        -- 重新拋出錯誤給調用方
        THROW;
    END CATCH
END;

關鍵模式對比

模式	代碼片段	優缺點
無 XACT_ABORT	SET XACT_ABORT OFF	PK 衝突後繼續執行 → 數據不一致 ❌
僅用 @@ERROR	IF @@ERROR<>0 ROLLBACK	SQL 2000 風格，易漏判、不支持嵌套、THROW 更強 ❌
TRY/CATCH + XACT_ABORT	模板代碼	結構化異常處理、支持嵌套、錯誤信息豐富 ✅
隱式事務	SET IMPLICIT_TRANSACTIONS ON	ANSI 標準，但易忘記 COMMIT，連接池復用風險高 ⚠️

進階陷阱與對策

• 分佈式事務 (MSDTC)：跨實例/跨數據庫需啟用 MSDTC，性能差、故障率高 → 業務層採用 Saga/最終一致性替代。
• 長事務阻塞：事務中避免遠程調用、大量計算、用戶交互；批量操作分批提交。
• 死鎖處理：捕獲 1205 錯誤，指數退避重試 3 次；統一表訪問順序、縮短事務、行級鎖優化。
• 觸發器隱式事務：觸發器運行在調用語句事務中，觸發器內 ROLLBACK 會回滾外層事務並中斷批次。

驗證腳本

-- 測試轉賬成功
EXEC usp_TransferFunds 1, 2, 100.00;
SELECT * FROM Account WHERE Id IN (1,2);

-- 測試轉入賬戶不存在 (觸發部分回滾)
EXEC usp_TransferFunds 1, 999, 50.00; -- 報錯，但账户1余额已恢复
SELECT * FROM Account WHERE Id = 1;

嚴格遵循模板，讓事務管理成為可控、可審計、可維護的工程實踐。