使用 GO 命令时 Transact-SQL 事务回滚无法正常工作 [英] Transact-SQL transaction rollback not working properly when using GO commands

问题描述

我有一个用 Transact-SQL 编写的迁移脚本，它使用事务以便在执行过程中出现问题时进行适当的回滚.不幸的是，当我在脚本中使用一些 GO 实用程序语句时，这种回滚行为没有按预期工作.

I have a migration script written in Transact-SQL which is using transactions in order to have a proper rollback if something goes wrong during the execution. Unfortunately, this rollback behaviour is not working as expected when I'm using some GO utility statements in my script.

这个问题可以用一个简单的脚本重现:

The issue can be reproduced with a simple script:

BEGIN TRANSACTION

-- Create a table with two nullable columns
CREATE TABLE [dbo].[t1](
    [id] [nvarchar](36) NULL,
    [name] [nvarchar](36) NULL
)

-- add one row having one NULL column
INSERT INTO [dbo].[t1] VALUES(NEWID(), NULL)

-- set one column as NOT NULLABLE
-- this fails because of the previous insert
ALTER TABLE [dbo].[t1] ALTER COLUMN [name] [nvarchar](36) NOT NULL
GO

-- create a table as next action, so that we can test whether the rollback happened properly
CREATE TABLE [dbo].[t2](
    [id] [nvarchar](36) NOT NULL
)
GO

COMMIT TRANSACTION

当我执行这个脚本时，我得到以下输出:

When I execute this script, I get the following output:

(1 row affected)
Msg 515, Level 16, State 2, Line 23
Cannot insert the value NULL into column 'name', table 'test-transaction.dbo.t1'; column does not allow nulls. UPDATE fails.
The statement has been terminated.
Msg 3902, Level 16, State 1, Line 31
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

正如预期的那样，它抱怨列 'name' 包含一个 NULL 值，但只有相应的 GO 批处理失败.执行下一批，成功创建表t2.

As expected, it is complaining that the column 'name' contains a NULL value but only the corresponding GO batch fails. The next batch is executed and the table t2 is successfully created.

我对GO 文档 是它不应该影响 T-SQL 事务，但在我的示例中并非如此.如果任何 GO 批处理失败，如何使整个事务回滚?

My understanding of the GO documentation is that it should not impact the T-SQL transactions but this is not the case in my example. How can I make the whole transaction be rolled back if any of the GO batch fails?

ps:如果我删除 GO 语句，事务回滚将按预期工作.但我确实需要那些 GO 语句，以确保脚本的某些部分先于其他部分执行.

ps: if I remove the GO statements, the transaction rollback is working as expected. But I do need those GO statements, in order to ensure that some parts of the script are executed before others.

推荐答案

一些错误回滚事务.不要费心去弄清楚哪些是，因为没有简单的规则.

Some errors roll back the transaction. Don't bother figuring out which ones, because there's no simple rule.

一个多批处理脚本应该有一个错误处理程序范围，它在错误时回滚事务，并在最后提交.在 TSQL 中，您可以使用动态 sql 执行此操作，例如

A multi-batch script should have a single error handler scope that rolls back the transaction on error, and commits at the end. In TSQL you can do this with dynamic sql, eg

BEGIN TRANSACTION
BEGIN TRY

    EXEC('
    -- Create a table with two nullable columns
    CREATE TABLE [dbo].[t1](
        [id] [nvarchar](36) NULL,
        [name] [nvarchar](36) NULL
    )
    ')

    EXEC('
    -- add one row having one NULL column
    INSERT INTO [dbo].[t1] VALUES(NEWID(), NULL)
    ')
    -- set one column as NOT NULLABLE
    -- this fails because of the previous insert
    EXEC('
    ALTER TABLE [dbo].[t1] ALTER COLUMN [name] [nvarchar](36) NOT NULL
    ')

    EXEC('
    -- create a table as next action, so that we can test whether the rollback happened properly
    CREATE TABLE [dbo].[t2](
        [id] [nvarchar](36) NOT NULL
    )
    ')

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
  IF @@TRANCOUNT > 0 ROLLBACK;
  THROW;
END CATCH

使用 SQLCMD，您可以使用 -b 选项在出错时中止脚本.

With SQLCMD you can use the -b option to abort the script on error.

这篇关于使用 GO 命令时 Transact-SQL 事务回滚无法正常工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！