搜尋本站文章

2018-11-27

BEGIN TRAN with XACT_ABORT



若需求是

  • 遇到執行階段的錯誤,希望系統能自動回復 Rollback 整個 交易 Transaction。
  • 則 請使用 BEGIN TRAN 並且設定 SET XACT_ABORT ON。


當 SET XACT_ABORT 是 ON 時,如果 Transact-SQL 陳述式產生執行階段錯誤,就會終止和回復整個交易。

  • 例如:資料表不存在、違反 外部索引鍵 等。


有啟用 SET XACT_ABORT ON,當遇到 執行階段錯誤時,系統自動回復 Rollback 目前交易,查詢 @@TRANCOUNT = 0。






BEGIN TRAN with enable XACT_ABORT


當 SET XACT_ABORT 是 ON 時,

  • 如果 Transact-SQL 陳述式產生執行階段錯誤,就會終止和回復整個交易。
  • 例如:資料表不存在、違反 外部索引鍵 等
  • SET XACT_ABORT 不會影響到如語法錯誤之類的編譯錯誤。


01. 僅使用 BEGIN TRAN,執行 Script:

  • 資料表 Products 存在,第 1 句 SELECT 執行成功,有回傳資料。
  • 但 SELECT 資料表 Products_XXX 不存在,執行時系統回傳 Error Message 208: Invalid object name。


問題:

  • 第 2 句 SELECT 執行失敗,但先前已經啟用 BEGIN TRAN,這會造成什麼影響呢?

-- figure 01_BEGIN TRAN without XACT_ABORT



02. 使用 @@TRANCOUNT ,檢視目前連線已經啟用 BEGIN TRAN 的數量。

  •  回傳 1,表示已經有 1 個 BEGIN TRAN 存在。
  • 若沒有 BEGIN TRAN,預設回傳 0。

-- figure 02_Have opened a transaction, @@TRANCOUNT = 1




03. 若嘗試去關閉該連線。

在 SSMS 產生警示的對話方塊,該連線有 未認可的交易(uncommitted transaction)。
點選 No,可以 退回 Rollback Transaction。

-- figure 03_Get_uncommitted_transaction





BEGIN TRAN with SET XACT_ABORT ON


當 SET XACT_ABORT 是 ON 時,

  • 如果 Transact-SQL 陳述式產生執行階段錯誤,就會終止和回復整個交易。
  • 例如:資料表不存在、違反 外部索引鍵 等
  • SET XACT_ABORT 不會影響到如語法錯誤之類的編譯錯誤。


01. 在新的連線上,使用 SET XACT_ABORT ON 與 BEGIN TRAN。

  • 資料表 Products 存在,第 1 句 SELECT 執行成功,有回傳資料。
  • 但 SELECT 資料表 Products_XXX 不存在,執行時系統回傳 Error Message 208: Invalid object name。

問題:

  • 先前已經使用 SET XACT_ABORT ON 與 BEGIN TRAN。其中,第 2 句 SELECT 因資料表 不存在 而執行失敗,這會造成什麼影響呢?

-- figure 11_Get_Error




02. 使用 @@TRANCOUNT ,檢視目前連線已經啟用 BEGIN TRAN 的數量。


  •  回傳 0,表示沒有任何 BEGIN TRAN 存在。
  • 若沒有 BEGIN TRAN,預設回傳 0。

這是因為當 SET XACT_ABORT 是 ON 時,
如果 Transact-SQL 陳述式產生執行階段錯誤,就會終止和回復整個交易。

-- figure 12_ @@TRANCOUNT = 0



03. 對比兩者的差異


  • 兩邊 SQL Script 執行後,在第 2 句 SELECT 資料表 Products_XXX 不存在,系統回傳 Error Message 208: Invalid object name。
  • 有啟用 SET XACT_ABORT ON,當遇到 執行階段錯誤時,系統自動回復 Rollback 目前交易,查詢 @@TRANCOUNT = 0。



-- figure 21_Compare