T-SQL SET XACT_ABORT ON

在SQL Server 2005中針對SET XACT_ABORT ON的測試

1.
SET XACT_ABORT ON
BEGIN TRAN
exec pr_plan_addnew_emper 49, 0.1, '3', '526275'
exec pr_plan_addnew_emper 49, '', '3', '526275' -- data type error
print @@TranCount
COMMIT TRAN

得到的回應是:

訊息 8114，層級 16，狀態 1，程序 pr_plan_addnew_emper，行 0
Error converting data type varchar to decimal.

Observation:
因為第二個exec指令有錯誤,所以連帶著整個TRAN block都被rollback

2.
SET XACT_ABORT OFF
BEGIN TRAN
exec pr_plan_addnew_emper 49, 0.1, '3', '526275'
exec pr_plan_addnew_emper 49, '', '3', '526275' -- data type error
print @@TranCount
COMMIT TRAN

回應:
訊息 8114，層級 16，狀態 1，程序 pr_plan_addnew_emper，行 0
Error converting data type varchar to decimal.
1

Observation:
在XACT_ABORT為OFF的狀態下,整個block並沒有被rollback,僅有產生錯誤的那一行指令有被rollback,因此我們所觀察到的@@TranCount為1

3.
SET XACT_ABORT ON
BEGIN TRAN
exec pr_plan_addnew_emper 49, 0.1, '3', '526275'
RaisError ( N'Uh Oh!', 16, 1 ) -- generate an error
print @@TranCount
COMMIT TRAN

回應:
訊息 50000，層級 16，狀態 1，行 4
Uh Oh!
1

Observation:
使用人工方式產生一個error,並未造成TRAN rollback,(error並沒有被catch到?還是error並沒有造成state changes或是resource lockings?)

4.
SET XACT_ABORT ON
BEGIN TRAN
exec pr_plan_addnew_emper 49, 0.1, '3', '526275'
exec pr_plan_addnew_emper 49, '', '3', '526275' -- data type error
RaisError ( N'Uh Oh!', 16, 1 ) -- generate an error
print @@TranCount
COMMIT TRAN

回應:
訊息 8114，層級 16，狀態 1，程序 pr_plan_addnew_emper，行 0
Error converting data type varchar to decimal.
(same as test case #1)

Observation:
一旦有非人工方式產生的error,整段code block會被interrupt and rollback,因此error message 'Uh Oh!' 並不會被print出來

結論:
使用XACT_ABORT ON,可以提供整個transaction block的rollback,但是使用XACT_ABORT OFF的話,可以做到individual的transaction rollback,各有各的好處,另外XACT_ABORT是T-SQL語法,所以使用範圍有其限制

Reference:
http://www.sommarskog.se/error-handling-II.html#XACT_ABORT