SQL Server 儲存程序中的交易控制：TRY-CATCH 與 XACT_ABORT 最佳實務

在資料庫程式設計中，交易確保多筆 DML 操作的原子性。在 .NET 程式碼中使用 TransactionScope 很方便，但在 SQL Server 儲存程序內部，該如何正確實作交易控制？

基本範本：显式交易 + 錯誤檢查

CREATE PROCEDURE RegisterUser
    @usrName VARCHAR(30),
    @usrPasswd VARCHAR(30),
    @age INT,
    @PhoneNum VARCHAR(20),
    @Address VARCHAR(50)
AS
BEGIN
    BEGIN TRAN;                 -- 顯式開啟交易
    SET XACT_ABORT ON;          -- 關鍵：遇到執行錯誤自動回滾整個交易

    INSERT INTO [User] (UserName, UserPasswd) VALUES (@usrName, @usrPasswd);

    -- 舊版相容寫法：手動檢查 @@ERROR
    IF @@ERROR <> 0
    BEGIN
        ROLLBACK TRANSACTION;
        RETURN 0;               -- 失敗
    END
    ELSE
    BEGIN
        COMMIT TRANSACTION;
        RETURN 1;               -- 成功
    END
END;

關鍵點解析

SET XACT_ABORT ON 的重要性

• 預設 OFF：某些錯誤 (如主鍵衝突、轉型失敗) 只會終止當前陳述式，交易仍保持開啟，若不手動 ROLLBACK 會造成鎖定資源洩漏
• 設為 ON：任何執行階段錯誤都會導致整個交易自動回滾，並中斷批次執行，極大簡化錯誤處理
• 最佳實務：在所有含寫入操作的儲存程序開頭加上 SET XACT_ABORT ON;

現代寫法：TRY...CATCH 區塊 (SQL Server 2005+)

CREATE PROCEDURE RegisterUser_V2
    @usrName VARCHAR(30),
    @usrPasswd VARCHAR(30)
AS
BEGIN
    SET NOCOUNT, XACT_ABORT ON;
    BEGIN TRY
        BEGIN TRAN;
        INSERT INTO [User] (UserName, UserPasswd) VALUES (@usrName, @usrPasswd);
        -- 更多業務邏輯...
        COMMIT TRAN;
        RETURN 1;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRAN;
        -- 記錄錯誤資訊 (可選)
        -- INSERT INTO ErrorLog (...) VALUES (ERROR_NUMBER(), ERROR_MESSAGE(), ...);
        RETURN 0;
    END CATCH
END;

優點：結構化錯誤處理、可取得 ERROR_NUMBER()、ERROR_MESSAGE() 等詳細資訊、程式碼可讀性更高。

常見陷阱與注意事項

• 巢狀交易：SQL Server 不支援真正的巢狀交易，@@TRANCOUNT 只是計數器，內層 COMMIT 只遞減計數，只有最外層 COMMIT 真正提交；ROLLBACK 則直接回滾至最外層並歸零計數
• 不要在動態 SQL 字串中包裹交易：應在呼叫端 (應用程式或外層 SP) 控制交易邊界
• 交易保持時間越短越好：避免在交易中進行網路呼叫、大量計算或等待使用者輸入
• 死鎖處理：應用層捕捉錯誤碼 1205 並實作重試邏輯