搜尋本站文章

2018-12-01

Enabled SET XACT_ABORT ON in SSMS, 在 SSMS 啟用 XACT_ABORT



延續前一篇文章:BEGIN TRAN with XACT_ABORT

若需要以此 SSMS 為單元,所建立的 Connection,都要啟用 XACT_ABORT,可以在 SSMS 執行以下設定




影響範圍


  • 啟用後,僅套用在此 SSMS 新建立的 Connection,不影響 SQL Server。
  • 由此 SSMS 新建立的 Connection,即刻適用。但對既有的 Connection 則不受影響。

若有需要,可以關閉 SSMS,再度開啟 SSMS。




Enabled SET XACT_ABORT ON in SSMS, 
在 SSMS 啟用 XACT_ABORT


01. 在 SSMS ,點選上方工具選單,Tools ,選擇 Options。

02. 在 Options 視窗


  • 點選 Query Execution,SQL  Server,Advanced。
  • 或是,在 Search Options 方塊,輸入: XACT 關鍵字。


03. 在 Advanced \ Specify the advanced execution settings 對話方塊


  • 勾選: SET XACT_ABORT ON


-- figure 01_SSMS_SET_XACT_ABORT_ON




影響範圍


  • 啟用後,僅套用在此 SSMS 新發出的 Connection,不影響 SQL Server。
  • 由此 SSMS 發出的新 Connection,即刻適用。但對既有的 Connection 則不受影響。

若有需要,可以關閉 SSMS,再度開啟 SSMS。






Reference

BEGIN TRAN with XACT_ABORT
http://sharedderrick.blogspot.com/2018/11/begin-tran-with-xactabort.html

查詢是否有啟用 XACT_ABORT 選項
http://sharedderrick.blogspot.com/2008/09/xactabort.html

設定 user options 伺服器組態選項
https://docs.microsoft.com/zh-tw/sql/database-engine/configure-windows/configure-the-user-options-server-configuration-option?view=sql-server-2017

SET XACT_ABORT (Transact-SQL)
https://docs.microsoft.com/zh-tw/sql/t-sql/statements/set-xact-abort-transact-sql?view=sql-server-2017

@@TRANCOUNT (Transact-SQL)
https://docs.microsoft.com/zh-tw/sql/t-sql/functions/trancount-transact-sql?view=sql-server-2017

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