SQL Server何时回滚错误事务？

Question

SQL Server何时回滚错误事务？

sql-servertransactions

3

我一直在研究以下两个问题：

SQL Server - 出错时事务回滚？

SQL 事务不回滚

通过我的研究，我得出结论应该这样编写查询语句：

set XACT_ABORT ON
begin tran FB5773_1
begin try
    -- Do some changes
    commit tran FB5773_1;
end try
begin catch
    IF EXISTS (SELECT [name] FROM sys.dm_tran_active_transactions WHERE name = 'FB5773_1')
        rollback tran FB5773_1;
end catch

然而，我想知道为什么像这样的交易会发生。

begin tran FB5773_1
--do some stuff
commit tran FB5773_1;

或者这个

set XACT_ABORT ON
begin tran FB5773_1
--do some stuff
commit tran FB5773_1;

在某些情况下出现错误时不回滚，什么情况下不会回滚？

- Lajos Arpad

这样的交易使得事务中的所有单个语句成为原子整体。它们全部成功，或者全部失败。不是每个操作都需要显式回滚，但有时您需要在处理更大的整体的单个小块时将所有内容隔离开来。 - Marc B

我本以为如果在提交之前出现错误，事务会被回滚，然而，一位非常了解事务的人告诉我这并不总是如此，并指向一个网址（https://dev59.com/T3I-5IYBdhLWcg3wpaF1），其中一个获得114个赞的答案告诉我们：“您可以在事务之前设置xact_abort，以确保在出现错误时自动回滚sql。”因此，这种行为对我来说是不清楚的。 - Lajos Arpad

有些情况下，您不希望自动回滚，因为您可能依赖于出现错误来决定下一步操作。如果自动回滚，您将无法进行分支决策。 - Marc B

@MarcB，错误需要在try里面才能让我们写一个catch来处理它。如果没有任何东西捕获这个错误，那么它仍然不会回滚。 - Lajos Arpad

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- btberry · Accepted Answer

我认为你正在寻找的答案可以在微软关于XACT_ABORT的文档中找到，我将尝试通过一些示例和其他资源来增强它。在我们开始之前，有几个表格可以用来测试不同场景下会发生什么：

CREATE TABLE _key
    ( id INT PRIMARY KEY CLUSTERED );
GO

INSERT INTO _key VALUES (1), (2), (3);
GO

CREATE TABLE _table1
    ( table1id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, id INT NOT NULL );
GO

ALTER TABLE dbo._table1
    ADD CONSTRAINT FK_table1 FOREIGN KEY (id) REFERENCES dbo._key (id);
GO

CREATE TABLE _table2
    ( table2id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, id INT NOT NULL );
GO

ALTER TABLE dbo._table2
    ADD CONSTRAINT FK_table2 FOREIGN KEY (id) REFERENCES dbo._key (id);
GO

CREATE PROCEDURE _proc
AS
BEGIN
    SET XACT_ABORT ON;
    BEGIN TRANSACTION;
        INSERT INTO dbo._table1 (id) VALUES(1); --valid insert
        DECLARE @query NVARCHAR(MAX) = N'SELECT ** FROM dbo._table1;'
        EXEC (@query); --will result in compile error
    COMMIT TRANSACTION;
    RETURN 0;
END
GO

另外，在每个示例之间，我清空表格以避免有任何疑惑：

TRUNCATE TABLE _table1;
TRUNCATE TABLE _table2;

我认为关于SET XACT_ABORT OFF的结果没有混淆，这是默认状态，但让我们举个例子来更清楚地说明...

SET XACT_ABORT OFF; --this command has no practical effect
BEGIN TRANSACTION;
    INSERT INTO dbo._table1 (id) VALUES(1); --valid insert
    INSERT INTO dbo._table2 (id) VALUES(4); --invalid insert
COMMIT TRANSACTION;

当我们在SSMS中运行此代码时，会出现错误：

插入语句与外键约束“FK_table2”冲突。该冲突发生在数据库“db”中，“dbo._key”表的“id”列中。

当我们查询_table1中的内容时，我们会看到被插入并且已提交的记录，因为XACT_ABORT未开启。这个结果是有道理的，因为我们没有中止，但真正的问题是当该选项开启时，SQL Server的行为如何。现在我们尝试使用不同的中止设置来进行相同的查询：

SET XACT_ABORT ON;
BEGIN TRANSACTION;
    INSERT INTO dbo._table1 (id) VALUES(1); --valid insert
    INSERT INTO dbo._table2 (id) VALUES(4); --invalid insert
COMMIT TRANSACTION;

我们在SSMS中看到了同样的错误，但这次在SQL Server遇到运行时错误时，整个批处理被回滚了，因此没有记录插入到_table1中。而这是来自MS文档的关键短语：

当SET XACT_ABORT为ON时，如果一个Transact-SQL语句引发运行时错误，则整个事务将终止并回滚。

编译错误，如语法错误，不受SET XACT_ABORT的影响。

（另外，我使用多个表来暴露和解决一个谬论，即如果在生成错误的语句中引用的表上进行了插入或更新，则XACT_ABORT不会回滚语句。这种说法是没有根据的和不正确的。无论哪个表中的所有语句都将回滚。）

那么重要的问题是：在什么情况下，即使设置了XACT_ABORT，整个批处理也不会被回滚？Erland Sommarskog已经比我更简明地回答了这个问题（并且更早）。已知的XACT_ABORT ON不会在遇到错误时回滚批处理的条件是：

使用RAISERROR引发的错误。

编译错误（通常终止作用域）不会终止批处理。

错误266，执行后的事务计数表明缺少COMMIT或ROLLBACK TRANSACTION语句。

例如：

SET XACT_ABORT ON;
BEGIN TRANSACTION;
    INSERT INTO dbo._table1 (id) VALUES(1); --valid insert
    RAISERROR (N'omg the sky is falling!!!', 16, 1); --my own error
COMMIT TRANSACTION;

这仍将导致记录被插入并提交，因为我的RAISERROR不会触发回滚。同样，我们可能会遇到语法错误，这不会回滚批处理：

EXEC dbo._proc;

这是一个非常好的问题，突显了在SQL Server中处理错误没有一种适用于所有情况的解决方案。在特定场景下，XACT_ABORT可能非常有用，但开发人员需要在依赖该设置处理所有情况之前，了解其潜在影响和限制。