T-SQL筆記8_SET XACT_ABORT使用_查檢XACT_ABORT的設定狀態
XACT_ABORT:
指定當 SQL Server 陳述式產生執行階段錯誤時，Transact-SQL 是否自動回復目前的交易。
MS SQL 預設 XACT_ABORT是OFF的
指令語法:
SET XACT_ABORT { ON | OFF }
若在進行資料庫系統開發時遇到一些執行語句
是要做批次(一次多筆)的更新、新增、刪除的時候
往往會透過所謂的「Transaction」 交易進行。
(以 BEGIN TRANSACTION 開頭 COMMIT TRANSACTION結尾，中間包覆的指令即交易行為若遇到異常時，可進行資料 Rollback 。)
但通常交易出現異常時，仍可能導致部分資料寫入資料表中。
此時就可執行此命令，強制約束SQL Server
當 SET XACT_ABORT 被Turn ON 時，如果 Transact-SQL 陳述式產生執行階段錯誤，就會終止和回復整個交易。
當 SET XACT_ABORT 被Turn OFF 時，在某些情況下，只會回復產生錯誤的 Transact-SQL 陳述式，交易會繼續進行。
這裡用一個小測試案例來講解
產生只有一個Column的簡單Table
CREATE TABLE t(P INT UNIQUE)
然後我故意在塞第二筆時候多塞
使其發生中途錯誤
這裡我如果加入SET XACT_ABORT Off 結果也會一樣
也應證了預設XACT_ABORT是被Turn Off的
如果是SET XACT_ABORT ON
則會恢復整組交易發生之前狀態(也就是完全沒有塞入半筆)
若要查看目前XACT_ABORT的設定是否有被開啟
則可以使用
指令:
DBCC USEROPTIONS
傳回目前連接在使用中 (已設定) 的 SET 選項。
若是有設定Turn On就會顯示在此結果集當中。
這樣看起來感覺好像我只要開頭把它開起來就好了
幹嘛還要自己寫ROLLBACK語句呢?
再來看一個案例
產生員工薪資表、員工功號表，功號為主鍵(只能唯一)
這裡故意塞重複工號在交易執行過程中
此時若我改成TURN OFF則會變成如此結果
事實上若沒有多加那段 if @@trancount > 0 就rollback就進DB了
此外此問題也有人在Stackoverflow上發問
回答人分享
事實上SQL Server Try catch 方式在rollback 仍有風險
因為try catch 並非100% 能抓到所有例外
https://stackoverflow.com/questions/21030099/are-these-code-snippets-equivalent-set-xact-abort-on-vs-try-catch-rollback
A TRY…CATCH construct catches all execution errors that have a severity higher than 10 that do not close the database connection.
So, try catch does not catch all possible errors. You can use xact_abort on in addition to try catch.
try/catch give you more flexibility, i.e., you are not limited to just a rollback when something is not happy.
Reference:
BEGIN TRAN with XACT_ABORT
http://sharedderrick.blogspot.com/2018/11/begin-tran-with-xactabort.html
[SQL Server]Talking about XACT_ABORT
https://dotblogs.com.tw/ricochen/2018/01/21/034210
SQL Server - 交易控制 - XACT_ABORT
http://caryhsu.blogspot.com/2011/01/sql-server-xactabort.html
How Does XACT_ABORT Work In SQL
https://www.c-sharpcorner.com/uploadfile/suba.venkat/how-does-xactabort-work-in-sql/
留言
張貼留言