深入 SQL Server 錯誤處理與孤立交易問題剖析

一、錯誤捕捉的怪異行為

SQL Server 錯誤處理機制並非直觀：同為嚴重度 16，直接查詢不存在的表 SELECT * FROM 不存在的表 後檢查 @@ERROR 不會輸出錯誤訊息；但透過 EXEC 或 sp_executesql 動態執行同樣語句，@@ERROR 則能正確捕捉。示範碼：

-- 直接執行：無法捕捉
SELECT * FROM 不存在的表;
IF @@ERROR <> 0 PRINT '這個沒有輸出';

-- RAISERROR：可捕捉
RAISERROR('錯誤', 16, 1);
IF @@ERROR <> 0 PRINT '這個輸出了';

-- EXEC 動態執行：可捕捉
EXEC('SELECT * FROM 不存在的表');
IF @@ERROR <> 0 PRINT '這個輸出了';

-- sp_executesql：可捕捉
EXEC sp_executesql N'SELECT * FROM 不存在的表';
IF @@ERROR <> 0 PRINT '這個輸出了';

結論：對可疑 SQL 應使用 EXEC 或 sp_executesql 包裝，確保後續能捕捉異常終止的錯誤。

二、孤立交易產生與解決

2.1 產生機制

交易中發生編譯錯誤（如查詢不存在表）時，批次終止，IF @@ERROR <> 0 區塊不會執行，導致 ROLLBACK 未執行，@@TRANCOUNT 保持 1，形成孤立交易。重複執行會疊加，耗盡資源。

SELECT @@TRANCOUNT; -- 0
BEGIN TRAN;
SELECT * FROM 不存在的表; -- 編譯錯誤，批次中止
IF @@ERROR <> 0 BEGIN PRINT '沒有執行到這裡!'; IF @@TRANCOUNT <> 0 ROLLBACK TRAN; END
COMMIT TRAN;
SELECT @@TRANCOUNT; -- 變成 1，且重複執行持續遞增

2.2 現有手段緩解

在 BEGIN TRAN 前預先檢查並回滾殘留交易：

IF @@TRANCOUNT <> 0 ROLLBACK TRAN; -- 清理上次殘留
BEGIN TRAN;
...
COMMIT TRAN;

此法可限制孤立交易存活至下一次程序呼叫，但無法根治批次內部錯誤導致的問題。

三、SET XACT_ABORT 的效用與限制

SET XACT_ABORT ON 可使執行期錯誤（如違反 CHECK 約束）導致整個交易自動回滾並中止批次，避免孤立交易。但對編譯期錯誤（如物件不存在）無效，同樣會產生孤立交易。

CREATE TABLE table1 (a INT CHECK (a > 100));

-- 違反 CHECK 約束：XACT_ABORT ON 會自動回滾
SET XACT_ABORT ON;
BEGIN TRAN; INSERT table1 VALUES(10); -- 這裡不會被執行 COMMIT TRAN;

-- 編譯錯誤：XACT_ABORT 無效，仍產生孤立交易
SET XACT_ABORT ON;
BEGIN TRAN; INSERT 不存在的表 VALUES(10); -- 批次中止，交易殘留

最佳實務建議

1. 盡量使用 TRY...CATCH（SQL 2005+）統一錯誤處理，於 CATCH 區塊檢查 XACT_STATE() 決定回滾。
2. 動態 SQL 必用 sp_executesql 並搭配參數化，避免注入與編譯錯誤。
3. 長交易前顯式 IF @@TRANCOUNT > 0 ROLLBACK 清理環境。
4. 設定 SET XACT_ABORT ON 作為防禦性補充，但不可完全依賴。