sql-server - 为什么 SQL 事务会自动回滚？回滚超过预期？

Question

我有一个很长的 SQL 脚本，我通过在 SSMS 中逐语句执行它来测试它。我正在本地机器上的数据库上进行测试。

脚本中没有“外部”事务，也没有嵌套事务。

精简示例：

USE NameOfDatabase

-- rename a table (outside a transaction)
EXEC sp_rename 'Table_XYZ', 'TableZ';

-- add a column to a table (outside a transaction)
ALTER TABLE TableB
ADD NewCol {...}

根据输出选项卡，所有这些语句都成功执行。

脚本中的很多地方，我都使用了BEGIN TRANSACTION/ COMMIT TRANSACTION，中间只有几个语句。所有这些似乎都按预期工作，因为我在Command(s) completed successfully执行COMMIT TRANSACTION语句时收到了消息“”。

例子：

-- create table statement outside a transaction
CREATE TABLE NewTableC {...}

-- inserting data from a table to be dropped, and dropping the table
BEGIN TRANSACTION

    INSERT INTO NewTableC {...}
    SELECT {...} FROM ObsoleteTableC
    
    DROP TABLE ObsoleteTableC

COMMIT TRANSACTION    -- "Command(s) completed successfully"

-- another smaller transaction
BEGIN TRANSACTION

    -- Table HasFK_A contains the foreign key constraint 'FK_A_ToBigTable'
    DROP TABLE HasFK_A

COMMIT TRANSACTION    -- "Command(s) completed successfully"

然后我的脚本中有一个特别大的事务，我在其中创建一个新表、插入数据、删除约束、删除表等。

例子：

BEGIN TRANSACTION

    -- create replacement table with temporary name
    CREATE TABLE Tmp_BigTable {...}

    -- insert data
    INSERT INTO Tmp_BigTable {...}
    SELECT {...} FROM BigTable

    -- drop constraints to old table
    -- Table 'HasFK_A' and its constraint 'FK_A_ToBigTable' should've already been dropped in a previous transaction
    ALTER TABLE HasFK_B DROP CONSTRANT FK_B_ToBigTable
    ALTER TABLE HasFK_C DROP CONSTRANT FK_C_ToBigTable

    -- drop old table
    DROP TABLE BigTable    -- error, there are still foreign keys referencing the table

    -- rename new table
    EXEC sp_rename 'Tmp_BigTable', 'BigTable'

    -- re-add constraints
    ALTER TABLE HasFK_B DROP CONSTRANT FK_B_ToBigTable
    ALTER TABLE HasFK_C DROP CONSTRANT FK_C_ToBigTable

-- commit transaction
COMMIT TRANSACTION

当我在那个更大的事务中执行DROP TABLE BigTable语句时，我收到一个错误，指出仍然有一个外键引用该表。

我打开了一个新的查询窗口并检查sys.foreign_keys了我错过了什么。令人惊讶的是，在我之前删除或重命名的表中列出了一些外键，这些较小的事务似乎已成功提交。ALTER TABLE我很快为所有剩余的外键编写了语句，并执行了这些语句。他们都没有出错，我认为这意味着那些“删除”和“重命名”的表仍然存在于数据库中。

我检查了该DROP TABLE BigTable语句现在是否会运行，因为我删除了额外的外键。它又失败了——剩下的键sys.foreign_keys现在是我最初在脚本中的键（FK_B_ToBigTable, FK_C_ToBigTable）。这让我相信第一个错误是事务回滚时。

我放弃了这些约束，然后BigTable，以确保所有外键都已被捕获。这一次，BigTable成功掉线。我确实尝试通过重命名不存在的新表 ( ) 并提交事务来确认事务已回滚Tmp_BigTable，但由于没有打开的事务而失败。

令人困惑的是，似乎事务被回滚，远远超出了“大”事务的范围。被删除的表似乎仍然存在，重命名的表似乎有它们的旧名称。

我的问题是：

1. 为什么抛出错误时事务会自动回滚？

根据MSDN：

如果批处理中发生运行时语句错误（例如违反约束），则数据库引擎中的默认行为是仅回滚生成错误的语句。SET XACT_ABORT您可以使用该语句更改此行为。

我的脚本中没有任何SET XACT_ABORT语句。

2. COMMIT为什么在执行语句时回滚（或从未提交）说“命令成功完成”的更改？

---

编辑：我已经浏览了脚本以找到语句似乎已回滚到的点。看来我可能不小心忘记了执行其中一项较小的交易COMMIT语句。

所以实际执行的看起来是这样的：

BEGIN TRANSACTION
    UPDATE ...
    EXEC sp_rename ...
    /* missed COMMIT TRANSACTION */
    
    ALTER TABLE ...
    UPDATE ...
    CREATE TABLE ...
    
    BEGIN TRANSACTION
        INSERT ...
        DROP TABLE ...
    COMMIT TRANSACTION
    
    BEGIN TRANSACTION
        ALTER TABLE {NewName} DROP CONSTRANT ...
        DROP TABLE ...
        
-- error on DROP TABLE rolls back both inner and outer nested transactions
ROLLBACK TRANSACTION        

SELECT * FROM sys.foreign_keys    -- returns foreign keys as they looked before "outer" transaction

所以这回答了问题 2，但不是问题 1。

标签： sql-servertransactionsssmssql-server-2014