SQL Server 怪癖深度解析：錯誤處理陷阱與孤立事務成因與對策

前言

SQL Server T-SQL 錯誤處理存在反直覺行為：同樣嚴重級 16 錯誤，批次執行 vs 動態 SQL 執行，`@@ERROR` 捕獲結果截然不同；嚴重錯誤導致批次中止，留下未提交/未回滾的「孤立事務」洩漏資源。本文實測演示三大怪癖，提供 `EXEC`/`sp_executesql` 包裝、`@@TRANCOUNT` 守衛、`SET XACT_ABORT ON` 三大對策。

一、 錯誤捕獲怪癖：批次 vs 動態 SQL

-- 1. 直接批次執行：嚴重錯誤(16)導致批次中止，後續 @@ERROR 檢查**不執行**
SELECT * FROM NotExistTable;
IF @@ERROR <> 0 PRINT '不會輸出'; -- 批次已終止

-- 2. RAISERROR 主動拋錯：可捕獲
RAISERROR('test', 16, 3);
IF @@ERROR <> 0 PRINT '會輸出';

-- 3. EXEC 包裝：錯誤在子批次發生，外層繼續執行，@@ERROR 可捕獲
EXEC('SELECT * FROM NotExistTable');
IF @@ERROR <> 0 PRINT '會輸出';

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

結論： 可疑 SQL **必須用 `EXEC` 或 `sp_executesql` 包裝**，將錯誤限制在子批次，外層才能透過 `@@ERROR` 捕獲並處理。

二、 孤立事務成因演示

SELECT @@TRANCOUNT; -- 0
BEGIN TRAN;
SELECT * FROM NotExistTable; -- 嚴重錯誤，批次中止
IF @@ERROR <> 0
BEGIN
    PRINT '不會執行到這裡!';
    IF @@TRANCOUNT <> 0 ROLLBACK TRAN; -- 不會執行
END
COMMIT TRAN; -- 不會執行

SELECT @@TRANCOUNT; -- 1 !!! 孤立事務殘留
-- 重複執行 @@TRANCOUNT 遞增，資源洩漏嚴重

嚴重錯誤 (編譯/解析/嚴重執行錯誤) 導致**批次立即中止**，`ROLLBACK`/`COMMIT` 及後續邏輯**完全不執行**，事務計數器 `@@TRANCOUNT` 保持 >0 狀態，連線池復用時導致嚴重資源洩漏與鎖競爭。

三、 現有手段解決孤立事務：入口守衛

-- 程序/批次入口強制清理殘留事務
IF @@TRANCOUNT > 0 ROLLBACK TRAN; -- 確保乾淨起點

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

-- 執行後 @@TRANCOUNT 歸 0，重複執行不再遞增

雖為權宜之計，但能有效限制孤立事務壽命「僅存活到下次調用」。

四、 SET XACT_ABORT ON：部分場景的自動回滾

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

-- 1. XACT_ABORT ON：違反約束 (執行錯誤) → 自動回滾整個事務，後續不執行
SET XACT_ABORT ON;
BEGIN TRAN;
INSERT T1 VALUES (10); -- 違反 CHECK
PRINT '不會輸出';
COMMIT TRAN; -- 不會執行
-- 事務自動回滾，@@TRANCOUNT 歸 0

-- 2. XACT_ABORT OFF：違反約束 → 僅該語句失敗，事務繼續，可提交
SET XACT_ABORT OFF;
BEGIN TRAN;
INSERT T1 VALUES (10);
PRINT '會輸出';
COMMIT TRAN; -- 事務提交成功 (除失敗語句外)

-- 3. 限制：編譯/解析錯誤 (如表不存在) XACT_ABORT ON 也無效，仍產生孤立事務
SET XACT_ABORT ON;
BEGIN TRAN;
INSERT NotExistTable VALUES (10); -- 編譯期解析失敗
PRINT '不會輸出';
COMMIT TRAN;
-- 孤立事務依然產生

五、 總結與最佳實踐

1. 動態 SQL 包裝： 所有外部輸入/動態構造 SQL 必須用 `sp_executesql` (支援參數化) 或 `EXEC()` 執行，外層檢查 `@@ERROR`。
2. 入口事務清理： 預存程序/批次開頭 `IF @@TRANCOUNT > 0 ROLLBACK TRAN;`。
3. 預設開啟 XACT_ABORT： 連線層級設定 `SET XACT_ABORT ON`，確保執行期錯誤 (約束違反、轉換失敗、死鎖受害者) 自動回滾事務。
4. TRY...CATCH (SQL 2005+)： 現代結構化異常處理，取代 `@@ERROR` 檢查模式，支援 `ERROR_NUMBER()`/`ERROR_MESSAGE()`/`XACT_STATE()` 精細控制。
5. 避免長事務： 縮小事務範圍，降低孤立風險與鎖持有時間。

展望： Microsoft 承諾下一版本 (Yukon/SQL 2005) 內建結構化異常處理 (`TRY...CATCH`)，徹底解決此類怪癖。