SQL Server XACT_ABORT and XACT_STATE
SQL Server (從 2008 開始) 提供 TRANSACTION 交易功能，在 VITO の 學習筆記 文章裡針對 Transactions 已有詳細說明，本文會透過案例方式來了解 XACT_ABORT、TRANSACTION、XACT_STATE 彼此關係，在這之前，先認識幾個跟交易有關的功能與用途。
預設 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 的設定是在執行階段進行設定，而不是在剖析階段進行設定。若要檢視此設定的目前設定，請執行下列查詢。
檢測目前是否有作用中的交易，以及是否需要進行回復交易，其中 XACT_STATE 函數回傳值主要有 1、0 和 -1 。
1 ：目前的要求具有使用中的使用者交易。
0 ：目前的要求沒有任何使用中的使用者交易。
-1 ：目前的要求有一項使用中的使用者交易，但發生錯誤，使交易被分類為無法認可的交易。
XACT_STATE 和 @@TRANCOUNT 函式都可用來偵測目前的要求是否有使用中的使用者交易。
@@TRANCOUNT 無法用來判斷交易是否已分類為無法認可的交易。
XACT_STATE 無法用來判斷是否有巢狀交易。
開始進入主題，先建立 [dbo].[emp] 資料表，欄位都不允許 null
測試語法如下，寫入第 2 筆資料時發生錯誤
案例是控制 XACT_ABORT 與 TRANSACTION 這 2 個功能，觀察 XACT_STATE 的變化
案例 1：XACT_ABORT 為 ON，有設定 TRANSACTION
發生錯誤時 XACT_STATE 為 -1，回復整個交易。
案例 2：XACT_ABORT 為 ON，沒有 TRANSACTION
沒有 TRANSACTION，因此 XACT_STATE 為 0，第一筆資料有寫入。
案例 3：XACT_ABORT 為 OFF，有設定 TRANSACTION
有設定 TRANSACTION，XACT_STATE 為 1，但是交易過程既往不咎，第一筆資料有寫入。
案例 4：XACT_ABORT 為 OFF，沒有 TRANSACTION
沒有 TRANSACTION，發生錯誤時 XACT_STATE 為 0，沒有要求回復整個交易，第一筆資料有寫入。
參考來源：
Continue Reading
預設 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;
IF ( (16384 & @@OPTIONS) = 16384 ) SET @XACT_ABORT = 'ON';
SELECT @XACT_ABORT AS XACT_ABORT;
例如 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 的終止資料庫連接的錯誤，因為在連接終止時，會中止執行動作。
預設 OFF。當 T-SQL 執行同時 SET NOCOUNT 為 ON，不會傳回計數，但仍會更新 @@ROWCOUNT 函式；如果 SET NOCOUNT 為 OFF，會傳回計數(如下圖)。針對執行 T-SQL 迴圈程序，或者預存程序包含多個 T-SQL，將 SET NOCOUNT 設為 ON 可以大幅提升效能，因為網路傳輸量會大幅降低。TRY...CATCH 建構會捕捉嚴重性大於 10 而並未終止資料庫連接的所有執行錯誤。
嚴重性 0-10 的錯誤是資訊訊息，不會使執行動作跳出 TRY...CATCH 建構的 CATCH 區塊。
CATCH 區塊不會處理嚴重性通常是 20-25 的終止資料庫連接的錯誤，因為在連接終止時，會中止執行動作。
檢測目前是否有作用中的交易，以及是否需要進行回復交易，其中 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,
)
[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
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
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
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
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
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，沒有要求回復整個交易，第一筆資料有寫入。
參考來源：
Continue Reading
