SQL Server 含交易的預存程序設計實戰：確保資料一致性的關鍵模式

資料庫交易是作為單一邏輯工作單元的一系列操作——要麼完全執行，要麼完全不執行。在預存程序中引入交易控制，能保證多條 SQL 語句作為原子單位執行，維護資料完整性。本文透過實例解析交易預存程序的核心語法與最佳實務。

核心設定：XACT_ABORT 與 NOCOUNT

SET NOCOUNT ON;   -- 不回傳影響列數，減少網路負擔
SET XACT_ABORT ON; -- 關鍵：遇到執行錯誤自動回滾整個交易（預設 OFF）

為何必須開啟 XACT_ABORT？ 預設關閉時，某些錯誤（如轉型失敗、違反條件約束）僅終止當前陳述式，交易仍處於開啟狀態，導致後續邏輯在不一致狀態下繼續執行。開啟後，任何執行階段錯誤都會導致整個交易回滾並中止批次。

完整範例：巢狀交易與存檔點

CREATE PROCEDURE usp_DeleteWithTransaction
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    -- 步驟 1：獨立操作（不在交易保護範圍內）
    DELETE FROM Table1 WHERE Name = '';

    -- 步驟 2：開始主交易
    BEGIN TRAN Tran1;
    
    DELETE FROM Table1 WHERE Name = '';
    
    -- 步驟 3：建立存檔點
    SAVE TRAN Tran2;
    
    UPDATE Table2 SET Name = '' WHERE Id = '';
    
    -- 步驟 4：錯誤檢查與回滾邏輯
    IF @@ERROR <> 0
    BEGIN
        ROLLBACK TRAN Tran2;  -- 回滾至存檔點（僅撤銷 UPDATE）
        COMMIT TRAN Tran1;    -- 提交主交易（保留前兩個 DELETE）
    END
    ELSE
    BEGIN
        COMMIT TRAN Tran1;    -- 全部成功，提交主交易
    END
END

關鍵行為解析

1. SAVE TRAN 建立部分回滾點，ROLLBACK TRAN Tran2 僅撤銷存檔點之後的操作（本例中僅撤銷 UPDATE），之前的 DELETE 保留。
2. @@ERROR 回傳緊鄰前一陳述式的錯誤碼（0 表示成功）。
3. 外層交易提交 才真正將變更持久化；若外層回滾，所有內部操作（含已提交的內層交易）全數撤銷。

最佳實務建議

• 盡量簡化交易範圍，縮短鎖定時間。
• 避免在交易中進行遠端呼叫、耗時運算或等待使用者輸入。
• 搭配 TRY...CATCH 結構化錯誤處理（SQL Server 2005+），替代傳統 @@ERROR 檢查。
• 嚴禁在交易中使用 WAITFOR DELAY 等長時間等待語句。

現代寫法：TRY...CATCH 範例

CREATE PROCEDURE usp_ModernTransaction
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;
    
    BEGIN TRY
        BEGIN TRAN;
        
        DELETE FROM Table1 WHERE Name = '';
        UPDATE Table2 SET Name = '' WHERE Id = '';
        -- 更多操作...
        
        COMMIT TRAN;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRAN;
        
        -- 記錄錯誤資訊
        DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
        DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
        DECLARE @ErrorState INT = ERROR_STATE();
        RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
    END CATCH
END

這種寫法結構更清晰，錯誤資訊更完整，是現代 T-SQL 開發的推薦模式。