SQL Server 的 SET XACT_ABORT ON | OFF;

最新推荐文章于 2021-12-20 08:34:25 发布

土豆131421

最新推荐文章于 2021-12-20 08:34:25 发布

阅读量1.2k

点赞数

CC 4.0 BY-SA版权

分类专栏： SQL Server

本文链接：https://blog.youkuaiyun.com/tuzhen007/article/details/10235391

SQL Server 专栏收录该内容

100 篇文章

订阅专栏

本文详细介绍了SQL Server中SET XACT_ABORT ON和OFF的使用。当SET XACT_ABORT ON时，若事务内发生错误，整个事务会被自动回滚；而根据XACT_STATE()函数，可以进一步控制事务的回滚策略。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一、当 SET XACT_ABORT ON; 时，一个事务（BEGIN TRANSACTION; ......(SQL语句)......COMMIT TRANSACTION;）中，如果有报错，就回滚整个事务。

USE AdventureWorks2008R2;
GO
IF OBJECT_ID(N't2', N'U') IS NOT NULL
    DROP TABLE t2;
GO
IF OBJECT_ID(N't1', N'U') IS NOT NULL
    DROP TABLE t1;
GO
CREATE TABLE t1
    (a INT NOT NULL PRIMARY KEY);
CREATE TABLE t2
    (a INT NOT NULL REFERENCES t1(a));
GO
INSERT INTO t1 VALUES (1);
INSERT INTO t1 VALUES (3);
INSERT INTO t1 VALUES (4);
INSERT INTO t1 VALUES (6);
GO
SET XACT_ABORT OFF;
GO
BEGIN TRANSACTION;
INSERT INTO t2 VALUES (1);
INSERT INTO t2 VALUES (2); -- Foreign key error.
INSERT INTO t2 VALUES (3);
COMMIT TRANSACTION;
GO
SET XACT_ABORT ON;
GO
BEGIN TRANSACTION;
INSERT INTO t2 VALUES (4);
INSERT INTO t2 VALUES (5); -- Foreign key error.
INSERT INTO t2 VALUES (6);
COMMIT TRANSACTION;
GO
-- SELECT shows only keys 1 and 3 added. 
-- Key 2 insert failed and was rolled back, but
-- XACT_ABORT was OFF and rest of transaction
-- succeeded.
-- Key 5 insert error with XACT_ABORT ON caused
-- all of the second transaction to roll back.
SELECT *
    FROM t2;
GO

二、当 SET XACT_ABORT ON; 的根据XACT_STATE()的状态判断是否回滚事务。

USE AdventureWorks2008R2;
GO

-- SET XACT_ABORT ON will render the transaction uncommittable
-- when the constraint violation occurs.
SET XACT_ABORT ON;

BEGIN TRY
    BEGIN TRANSACTION;
        -- A FOREIGN KEY constraint exists on this table. This 
        -- statement will generate a constraint violation error.
        DELETE FROM Production.Product
            WHERE ProductID = 980;

    -- If the delete operation succeeds, commit the transaction. The CATCH
    -- block will not execute.
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    -- Test XACT_STATE for 0, 1, or -1.
    -- If 1, the transaction is committable.
    -- If -1, the transaction is uncommittable and should 
    --     be rolled back.
    -- XACT_STATE = 0 means there is no transaction and
    --     a commit or rollback operation would generate an error.

    -- Test whether the transaction is uncommittable.
    IF (XACT_STATE()) = -1
    BEGIN
        PRINT 'The transaction is in an uncommittable state.' +
              ' Rolling back transaction.'
        ROLLBACK TRANSACTION;
    END;

    -- Test whether the transaction is active and valid.
    IF (XACT_STATE()) = 1
    BEGIN
        PRINT 'The transaction is committable.' + 
              ' Committing transaction.'
        COMMIT TRANSACTION;   
    END;
END CATCH;
GO