SQL Server XACT_ABORT and XACT_STATE

凌晨12:18  

SQL Server (從 2008 開始) 提供 TRANSACTION 交易功能，在 VITO の 學習筆記 文章裡針對 Transactions 已有詳細說明，本文會透過案例方式來了解 XACT_ABORT、TRANSACTION、XACT_STATE 彼此關係，在這之前，先認識幾個跟交易有關的功能與用途。

• XACT_ABORT

預設 OFF。用於執行階段的交易控制，當 T-SQL 執行發生錯誤同時 XACT_ABORT 為 ON，就會終止並回復整個交易；如果 XACT_ABORT 為 OFF，交易會繼續進行，但是會依據錯誤嚴重度(Severity)不同，有可能回復整個交易。若是發生錯誤之後才設定 XACT_ABORT 為 ON，已完成的交易仍會寫入，無法回復整個交易，只要修改 XACT_ABORT 為 ON，最後記得改回 OFF。

針對大部分 OLE DB 提供者 (包括 SQL Server) 的隱含或明確的交易，其中之資料修改陳述式的 XACT_ABORT 都必須設為 ON。 只有在提供者支援巢狀交易時，才不需要這個選項。

SET XACT_ABORT 的設定是在執行階段進行設定，而不是在剖析階段進行設定。若要檢視此設定的目前設定，請執行下列查詢。

DECLARE @XACT_ABORT VARCHAR(3) = 'OFF';  
IF ( (16384 & @@OPTIONS) = 16384 ) SET @XACT_ABORT = 'ON';  
SELECT @XACT_ABORT AS XACT_ABORT; 

• Database Engine 錯誤嚴重性

例如 Uncommittable transaction is detected at the end of the batch. The transaction is rolled back. 嚴重性層級 16 (指出使用者能夠更正的一般錯誤)，這項錯誤會讓 XACT_STATE 變成 -1。

TRY...CATCH 建構會捕捉嚴重性大於 10 而並未終止資料庫連接的所有執行錯誤。
嚴重性 0-10 的錯誤是資訊訊息，不會使執行動作跳出 TRY...CATCH 建構的 CATCH 區塊。
CATCH 區塊不會處理嚴重性通常是 20-25 的終止資料庫連接的錯誤，因為在連接終止時，會中止執行動作。

• TRY...CATCH
• BEGIN TRANSACTION
• SET NOCOUNT

預設 OFF。當 T-SQL 執行同時 SET NOCOUNT 為 ON，不會傳回計數，但仍會更新 @@ROWCOUNT 函式；如果 SET NOCOUNT 為 OFF，會傳回計數(如下圖)。針對執行 T-SQL 迴圈程序，或者預存程序包含多個 T-SQL，將 SET NOCOUNT 設為 ON 可以大幅提升效能，因為網路傳輸量會大幅降低。

• XACT_STATE

檢測目前是否有作用中的交易，以及是否需要進行回復交易，其中 XACT_STATE 函數回傳值主要有 1、0 和 -1 。
1  ：目前的要求具有使用中的使用者交易。
0  ：目前的要求沒有任何使用中的使用者交易。
-1 ：目前的要求有一項使用中的使用者交易，但發生錯誤，使交易被分類為無法認可的交易。

XACT_STATE 和 @@TRANCOUNT 函式都可用來偵測目前的要求是否有使用中的使用者交易。
@@TRANCOUNT 無法用來判斷交易是否已分類為無法認可的交易。
XACT_STATE 無法用來判斷是否有巢狀交易。

---

開始進入主題，先建立 [dbo].[emp] 資料表，欄位都不允許 null

CREATE TABLE [dbo].[emp](
    [EmpName] [varchar](50) NOT NULL ,
    [DepNo] int NOT NULL,
)

測試語法如下，寫入第 2 筆資料時發生錯誤
案例是控制 XACT_ABORT 與 TRANSACTION 這 2 個功能，觀察 XACT_STATE 的變化

SET XACT_ABORT ON;

BEGIN TRY

    SET NOCOUNT ON;

    BEGIN TRAN

        INSERT dbo.emp(EmpName,DepNo) Values ('Kemba1', 1)

        INSERT dbo.emp(EmpName,DepNo) Values ('Kemba2', null) --發生錯誤  

    COMMIT TRAN    

END TRY

BEGIN CATCH

    declare @xact_state int = XACT_STATE();

   IF (@xact_state) = 1    --transaction is committable.

    BEGIN

        PRINT 'XACT_STATE: '+CAST(@xact_state as char(2))

        PRINT 'NO ERRORS in TRANSATION'

       COMMIT TRAN

    END

   IF (@xact_state) = -1   --transaction is uncommittable.

    BEGIN

        PRINT 'XACT_STATE: '+CAST(@xact_state as char(2))

        PRINT 'ERRORS in TRANSATION'

        ROLLBACK TRAN

   END
    IF (@xact_state) = 0    --no transaction

    BEGIN

        PRINT 'XACT_STATE: '+CAST(@xact_state as char(2))

        PRINT 'NO TRANSATIONS'

    END

END CATCH

SET XACT_ABORT OFF;

案例 1：XACT_ABORT 為 ON，有設定 TRANSACTION

SET XACT_ABORT ON;
BEGIN TRY
    SET NOCOUNT ON;
    BEGIN TRAN
        INSERT dbo.emp(EmpName,DepNo) Values ('Kemba1', 1)
        INSERT dbo.emp(EmpName,DepNo) Values ('Kemba2', null) --發生錯誤  
    COMMIT TRAN    
END TRY    

 
發生錯誤時 XACT_STATE 為 -1，回復整個交易。

案例 2：XACT_ABORT 為 ON，沒有 TRANSACTION

SET XACT_ABORT ON;
BEGIN TRY
    SET NOCOUNT ON;
    --BEGIN TRAN
        INSERT dbo.emp(EmpName,DepNo) Values ('Kemba1', 1)
        INSERT dbo.emp(EmpName,DepNo) Values ('Kemba2', null) --發生錯誤  
    --COMMIT TRAN    
END TRY    

沒有 TRANSACTION，因此 XACT_STATE 為 0，第一筆資料有寫入。

案例 3：XACT_ABORT 為 OFF，有設定 TRANSACTION

SET XACT_ABORT OFF;
BEGIN TRY
    SET NOCOUNT ON;
    BEGIN TRAN
        INSERT dbo.emp(EmpName,DepNo) Values ('Kemba1', 1)
        INSERT dbo.emp(EmpName,DepNo) Values ('Kemba2', null) --發生錯誤  
    COMMIT TRAN    
END TRY    

 
有設定 TRANSACTION，XACT_STATE 為 1，但是交易過程既往不咎，第一筆資料有寫入。

案例 4：XACT_ABORT 為 OFF，沒有 TRANSACTION

SET XACT_ABORT OFF;
BEGIN TRY
    SET NOCOUNT ON;
    --BEGIN TRAN
        INSERT dbo.emp(EmpName,DepNo) Values ('Kemba1', 1)
        INSERT dbo.emp(EmpName,DepNo) Values ('Kemba2', null) --發生錯誤  
    --COMMIT TRAN    
END TRY    

 
沒有 TRANSACTION，發生錯誤時 XACT_STATE 為 0，沒有要求回復整個交易，第一筆資料有寫入。

參考來源：

1. SQL Server 2008 error handling best practice
2. SQL Server 錯誤處理 (2)
3. Why does SQL Server default XACT_ABORT to OFF? Can it be set to ON globally?
4. Why isn't SET XACT_ABORT ON the default behavior?