SQL Server 錯誤處理陷阱與孤立事務產生原因、偵測與解決方案

一、錯誤處理的三種行為差異

測試環境：查詢不存在的表。

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

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

-- 3. EXEC / sp_executesql 包裝：@@ERROR 捕捉
EXEC('SELECT * FROM 不存在的表');
IF @@ERROR <> 0 PRINT '這個輸出了';

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

結論：編譯期錯誤（物件不存在）直接執行不會設定 @@ERROR，必須透過 EXEC 或 sp_executesql 動態執行才能在後續捕捉。

二、孤立事務產生與偵測

-- 重現孤立事務
SELECT @@TRANCOUNT; -- 0
BEGIN TRAN;
SELECT * FROM 不存在的表;  -- 編譯錯誤，批次終止
IF @@ERROR <> 0
BEGIN
    PRINT '沒有執行到這裡!';
    IF @@TRANCOUNT <> 0 ROLLBACK TRAN;
END
COMMIT TRAN;  -- 不會執行
SELECT @@TRANCOUNT; -- 變成 1，重複執行疊加，資源洩漏

因編譯錯誤導致批次中止，ROLLBACK 區塊未執行，@@TRANCOUNT 殘留遞增。

三、現有手段緩解

-- 程序開頭檢查並清理殘留事務
IF @@TRANCOUNT <> 0 ROLBACK TRAN;

BEGIN TRAN;
... 業務邏輯 ...
COMMIT TRAN;

雖能避免疊加，但無法解決當次事務已孤立的問題。

四、SET XACT_ABORT ON 的效力與限制

CREATE TABLE T1 (A INT CHECK (A > 100));

-- XACT_ABORT ON：違反 CHECK 觸發批次終止並自動回滾
SET XACT_ABORT ON;
BEGIN TRAN;
INSERT T1 VALUES (10);  -- 違反約束
PRINT '這裡沒有被執行';
COMMIT TRAN;  -- 不會執行

-- XACT_ABORT OFF：僅陳述式失敗，批次繼續
SET XACT_ABORT OFF;
BEGIN TRAN;
INSERT T1 VALUES (10);
PRINT '這裡被執行';  -- 輸出
COMMIT TRAN;  -- 提交成功（雖 INSERT 失敗）

限制：XACT_ABORT 對編譯錯誤（物件不存在）無效，同樣會產生孤立事務。

五、最佳實務建議

1. 所有動態 SQL 使用 sp_executesql 並搭配 TRY...CATCH (SQL 2005+) 捕捉錯誤。
2. 程序/批次開頭檢查 @@TRANCOUNT 並清理。
3. 關鍵業務交易啟用 SET XACT_ABORT ON 確保運行期錯誤自動回滾。
4. 避免在大批次中混合 DDL/DML 導致編譯錯誤中斷流程。