在SQL Server中正确使用事务

Question

我有两个命令需要正确执行或者全部不执行，因此我认为我需要一个事务(transaction)，但是我不知道如何正确使用它。

以下脚本有什么问题？

BEGIN TRANSACTION [Tran1]

INSERT INTO [Test].[dbo].[T1]
    ([Title], [AVG])
VALUES ('Tidd130', 130), ('Tidd230', 230)

UPDATE [Test].[dbo].[T1]
  SET [Title] = N'az2' ,[AVG] = 1
  WHERE [dbo].[T1].[Title] = N'az'

COMMIT TRANSACTION [Tran1]
GO

执行了INSERT命令，但UPDATE命令存在问题。

如果其中任何一个命令执行时出现错误，如何实现回滚两个命令？

- Saeid

Answer 1

添加try/catch语句块，如果事务成功则提交更改，如果事务失败则回滚事务：

BEGIN TRANSACTION [Tran1]

  BEGIN TRY

      INSERT INTO [Test].[dbo].[T1] ([Title], [AVG])
      VALUES ('Tidd130', 130), ('Tidd230', 230)

      UPDATE [Test].[dbo].[T1]
      SET [Title] = N'az2' ,[AVG] = 1
      WHERE [dbo].[T1].[Title] = N'az'

      COMMIT TRANSACTION [Tran1]

  END TRY

  BEGIN CATCH

      ROLLBACK TRANSACTION [Tran1]

  END CATCH  

- Darren

---

“BEGIN TRANSACTION [Tran1]” 不应该放在 “TRY” 里面吗？不管怎样，这是非常简单优雅的一段代码。 - Piotr Nawrot

@PiotrNawrot 不需要在catch中回滚交易，如果交易创建失败。 - Monsignor

如果想要查看错误信息，请在catch语句中加入以下内容： SELECT ERROR_MESSAGE() AS ErrorMessage; - Kevin LeStarge

如果你正在使用SQL Server >= 2012，可以使用@KevinLeStarge或者简单地使用THROW;，如此处所述：https://dba.stackexchange.com/a/144814/184793。 - Leponzo

如果一个人想要一种"视觉上嵌套的语法"，也就是说，begin transaction被嵌套在begin try之下，那么在rollback transaction之前应该添加一个条件，即：if @@trancount > 0。 - OfirD

如果一个人想要一个所谓的“视觉上嵌套的语法”，也就是说，begin transaction 嵌套在 begin try 下面，那么在 rollback transaction 之前应该添加一个条件，即：if @@trancount > 0。 - undefined

Answer 2

在存储过程的开始部分，应该加上SET XACT_ABORT ON指令，以便指示Sql Server在出现错误时自动回滚事务。如果省略或设置为OFF，那么每个语句之后都需要测试@@ERROR，或使用TRY ... CATCH rollback块。

- Nikola Markovinović

---

换句话说，除非您首先将 SET XACT_ABORT ON 设置为 ON，否则您的事务不是原子性的。 - 4AM

注意 - 对于XACT_ABORT：在T-SQL语句中，OFF是默认设置，而在触发器中，ON是默认设置。 - J. Rockwood

请澄清一下，SET XACT_ABORT ON 只适用于当前存储过程吗？ - U. Busto

@U.Busto 当前交易，是的。 - Nikola Markovinović

Answer 3

简单方法：

CREATE TABLE T
(
    C [nvarchar](100) NOT NULL UNIQUE,
);

SET XACT_ABORT ON -- Turns on rollback if T-SQL statement raises a run-time error.
SELECT * FROM T; -- Check before.
BEGIN TRAN
    INSERT INTO T VALUES ('A');
    INSERT INTO T VALUES ('B');
    INSERT INTO T VALUES ('B');
    INSERT INTO T VALUES ('C');
COMMIT TRAN
SELECT * FROM T; -- Check after.
DELETE T;

- Bohdan