SQL Server 異常處理與孤立事務深度解析

一、錯誤處理機制的陷阱：同樣是 Severity 16，行為卻大不同

SQL Server 的錯誤處理行為取決於錯誤發生的「批次」與「執行上下文」。單一批次內的語句錯誤（如查詢不存在的表）預設只會中止該語句，後續語句繼續執行，@@ERROR 僅在緊接的下一句有效。而動態 SQL（EXEC、sp_executesql）發生錯誤時，會導致整個批次終止，外層才能捕捉到錯誤。

行為對比示範

-- 1. 直接查詢不存在的表：僅該語句失敗，批次繼續
SELECT * FROM not_exist_table;
IF @@ERROR <> 0 PRINT '這個不會輸出'; -- @@ERROR 已歸零
GO

-- 2. RAISERROR 主動拋出：可被捕捉
RAISERROR('測試錯誤', 16, 1);
IF @@ERROR <> 0 PRINT '這個會輸出';
GO

-- 3. EXEC 動態 SQL：批次終止，外層可捕捉
EXEC('SELECT * FROM not_exist_table');
IF @@ERROR <> 0 PRINT '這個會輸出';
GO

-- 4. sp_executesql 同理
EXEC sp_executesql N'SELECT * FROM not_exist_table';
IF @@ERROR <> 0 PRINT '這個會輸出';

結論：若需在預存程序中統一捕捉異常，建議將高風險 SQL 包裝在 EXEC 或 sp_executesql 中執行，再檢查 @@ERROR（或改用 TRY...CATCH）。

二、孤立事務：隱形的資源殺手

1. 孤立事務如何產生

當事務內發生「批次終止型」錯誤（如動態 SQL 錯誤、嚴重度 ≥ 20 的錯誤），事務進入不可提交狀態（doomed transaction），但 @@TRANCOUNT 不會自動歸零。若錯誤處理邏輯未正確回滾，事務計數器持續累加，導致鎖定資源不釋放、交易記錄暴增、甚至封鎖其他連線。

-- 重現孤立事務
SELECT @@TRANCOUNT; -- 0
BEGIN TRAN;
SELECT * FROM not_exist_table; -- 批次終止型錯誤
IF @@ERROR <> 0
BEGIN
    PRINT '錯誤處理區塊根本執行不到！';
    IF @@TRANCOUNT <> 0 ROLLBACK TRAN; -- 不會執行
END
COMMIT TRAN; -- 這行也不會執行
SELECT @@TRANCOUNT; -- 變成 1！重複執行會不斷累加

2. 解決方案：入口處檢查並清理

在預存程序開頭強制回滾殘留事務，確保每次呼叫從乾淨狀態開始：

CREATE PROCEDURE usp_SafeOperation
AS
BEGIN
    -- 關鍵：清理上層呼叫遺留的孤立事務
    IF @@TRANCOUNT > 0 ROLLBACK TRAN;

    BEGIN TRAN;
    BEGIN TRY
        -- 業務邏輯
        INSERT INTO dbo.TestTable (Col1) VALUES ('Test');
        -- 故意製造錯誤
        SELECT * FROM not_exist_table;
        COMMIT TRAN;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRAN;
        -- 記錄錯誤、拋出或處理
        THROW; -- SQL 2012+ 重新拋出原始錯誤
    END CATCH
END

三、SET XACT_ABORT ON：強制事務原子性的利器

開啟 SET XACT_ABORT ON 後，任何執行階段錯誤（包括約束違反、轉換失敗、死鎖受害者等）都會導致**整個事務自動回滾**並中止批次，無需手動檢查 @@ERROR。

CREATE TABLE dbo.Table1 (a INT CHECK (a > 100));
GO

SET XACT_ABORT ON;
BEGIN TRAN;
    INSERT INTO Table1 VALUES (10); -- 違反 CHECK 約束
    PRINT '這行不會執行'; -- 批次已終止
COMMIT TRAN; -- 不會執行
GO

-- 檢查結果：資料未寫入，事務已回滾
SELECT * FROM Table1; -- 空結果

適用場景與限制

• ✅ 適合：簡單事務、批次作業、遺留代碼快速加固。
• ❌ 不適合：需部分提交、複雜錯誤處理邏輯、需在 CATCH 區塊中繼續執行後續邏輯的場景（改用 TRY...CATCH）。
• ⚠️ 注意：XACT_ABORT 不影響編譯錯誤（如語法錯誤）、啟用後無法使用 SAVE TRANSACTION 建立儲存點。

最佳實務總結

1. 優先使用 TRY...CATCH（SQL 2005+）結構化異常處理，搭配 XACT_STATE() 判斷事務狀態（1=可提交、-1=不可提交需回滾、0=無事務）。
2. 預存程序入口處加入 IF @@TRANCOUNT > 0 ROLLBACK TRAN 防禦孤立事務。
3. 關鍵業務明確開啟 SET XACT_ABORT ON 確保原子性。
4. 避免在應用層手動管理事務（如 ADO.NET BeginTransaction），改由資料庫端封裝，減少網路往返導致的事務長時間開啟風險。