[轉帖]XACT_ABORT 的問題

XACT_ABORT原意是精確終止

其實在SQL執行中很好理解

預設XACT_ABORT=OFF的情況下，語句不管什麼情況，遇到什麼錯誤，反正錯誤了也繼續執行，意思為錯也不回頭。

在XACT_ABORT=ON的情況下，語句遇到一個錯誤就停止執行並回滾錯誤：

關鍵是這個是事務聯絡起來，一個事務就是一個錯誤，所以事務內錯了整個事務就回滾，就把一個事務當成一句語句好了。

這個容易和儲存過程搞，一個儲存過程中可能有好幾個事務，如果遇到錯誤，事務前的還是執行，錯誤事務本身回滾，但是儲存過程不回滾。

如果PB之類的呼叫儲存過程，並且AUTOCOMMIT=TRUE的話，就還後上面分析的一樣，如果AUTOCOMMIT=FALSE的話：

1、儲存過程沒有報錯，COMMIT後還是全部執行

2、儲存過程報錯，ROLLBACK後儲存過程所有的操作都回滾

測試：

建立測試表

IF OBJECT_ID ('dbo.a') IS NOT NULL
    DROP TABLE dbo.a
GO
CREATE TABLE dbo.a
    (
    a INT NOT NULL,
    CONSTRAINT pk_a PRIMARY KEY (a)
    )
GO

1、XACT_ABORT=OFF、無事務

SET XACT_ABORT OFF
GO
INSERT INTO a(a) VALUES(1)
INSERT INTO a(a) VALUES(2)
INSERT INTO a(a) VALUES(3)
INSERT INTO a(a) VALUES(3)
INSERT INTO a(a) VALUES(4)
INSERT INTO a(a) VALUES(5)
GO

執行返回：

(1 行受影響)

(1 行受影響)

(1 行受影響)
訊息 2627，級別 14，狀態 1，第 4 行
違反了 PRIMARY KEY 約束 'pk_a'。不能在物件 'dbo.a' 中插入重複鍵。
語句已終止。

(1 行受影響)

(1 行受影響)

結果：

1
2
3
4
5

很明顯，雖然第四個INSERT處報錯了，但是語句還是繼續往下執行，直至結束。

2、XACT_ABORT=OFF、有事務

SET XACT_ABORT OFF
GO
INSERT INTO a(a) VALUES(1)
BEGIN TRAN
INSERT INTO a(a) VALUES(2)
INSERT INTO a(a) VALUES(3)
INSERT INTO a(a) VALUES(3)
INSERT INTO a(a) VALUES(4)
COMMIT TRAN
INSERT INTO a(a) VALUES(5)
GO

返回和結果與1一樣，說明報錯了以後，只回滾錯誤語句，同時繼續往下執行，直至結束。

XACT_ABORT=OFF沒有事務機制，錯誤就回滾錯誤語句本身，繼續往下一句一句執行，直至結束。

3、XACT_ABORT=ON、無事務

SET XACT_ABORT ON
GO
INSERT INTO a(a) VALUES(1)
INSERT INTO a(a) VALUES(2)
INSERT INTO a(a) VALUES(3)
INSERT INTO a(a) VALUES(3)
INSERT INTO a(a) VALUES(4)
INSERT INTO a(a) VALUES(5)
GO

執行返回：

(1 行受影響)

(1 行受影響)

(1 行受影響)
訊息 2627，級別 14，狀態 1，第 5 行
違反了 PRIMARY KEY 約束 'pk_a'。不能在物件 'dbo.a' 中插入重複鍵。

結果為：

1
2
3

很明顯，雖然也在第四個INSERT出報錯了，但是和1的區別是語句停止了執行，下面插入4和5的語句沒有被執行。

4、XACT_ABORT=ON、有事務

SET XACT_ABORT ON
GO
INSERT INTO a(a) VALUES(1)
BEGIN TRAN
INSERT INTO a(a) VALUES(2)
INSERT INTO a(a) VALUES(3)
INSERT INTO a(a) VALUES(3)
INSERT INTO a(a) VALUES(4)
COMMIT TRAN
INSERT INTO a(a) VALUES(5)
GO

執行返回：

(1 行受影響)

(1 行受影響)

(1 行受影響)
訊息 2627，級別 14，狀態 1，第 5 行
違反了 PRIMARY KEY 約束 'pk_a'。不能在物件 'dbo.a' 中插入重複鍵。

結果為：

1

很明顯，雖然也在第四個INSERT出報錯了，語句停止了執行，但是和3的區別事務內的前兩句插入2和3的語句被回滾了，只有不在事務內的插入1的成功了。

XACT_ABORT=ON有事務機制，錯誤要回滾事務並停止往下執行，但是不在事務內已經執行的語句還是被成功執行。其實很好理解，和其他地方處理事務一樣，把一個事務當成一個語句就好了，錯了就回滾並終止執行，是所謂精準終止。

文章知識點與官方知識檔案匹配，可進一步學習相關知識

MySQL入門技能樹SQL高階技巧CTE和遞迴查詢83479 人正在系統學習中