若需求是
- 遇到執行階段的錯誤，希望系統能自動回復 Rollback 整個 交易 Transaction。
- 則 請使用 BEGIN TRAN 並且設定 SET XACT_ABORT ON。
- 例如：資料表不存在、違反 外部索引鍵 等。
有啟用 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
Sample Code
20181126_BEGIN TRAN with XACT_ABORT
Reference
SET XACT_ABORT (Transact-SQL)
@@TRANCOUNT (Transact-SQL)
查詢是否有啟用 XACT_ABORT 選項
沒有留言:
張貼留言