为什么下面的the事务不能按预期工作？

Question

为了测试事务是如何工作的，我编写了以下test。因为第一个属性是主键，所以不应该提交任何插入。但是，第一个插入被提交了吗？为什么？

begin transaction
   insert into instructor
   values ('99999', 'Yellow', 'Biology', 700000)

   insert into instructor
   values ('99999', 'Blue', 'Statistics', 85000)
commit;

select * from instructor where ID = '99999'

--delete from instructor where ID = '99999'

(1行受影响) Msg 2627，级别14，状态1，行100违反主键约束‘PK_instruct_3214EC278C8DA99F’。无法在对象‘dbo.辅导员’中插入重复键。重复键值为(99999)。声明已被终止。

完工时间: 2019-11-06T14:02:27.3436411+02:00

Answer 1

因为，当XACT_ABORT为OFF时(这是默认的)：

在SET XACT_ABORT关闭时，在某些情况下只有引发错误的Transact-SQL语句回滚，事务继续处理。

当它是ON时

..如果Transact-SQL语句引发运行时错误，则整个事务将终止并回滚。

这就是我们需要将其转换为ON的地方，如果您尝试下面的代码，您可以检查以下内容：

DROP TABLE IF EXISTS [dbo].[StackOverflow];

CREATE TABLE [dbo].[StackOverflow]
(
    [StackID] TINYINT PRIMARY KEY
);

SET XACT_ABORT ON;

BEGIN TRANSACTION;

INSERT INTO [dbo].[StackOverflow] ([StackID])
VALUES (105);

INSERT INTO [dbo].[StackOverflow] ([StackID])
VALUES (105);

COMMIT TRANSACTION;

SET XACT_ABORT OFF;

SELECT [StackID]
FROM [dbo].[StackOverflow];

此外，请注意：

编译错误(例如语法错误)不受SET XACT_ABORT的影响。

上面的意思是，如果您想要真正拥有自动事务，您需要使用以下代码块：

SET NOCOUNT, XACT_ABORT ON;

BEGIN TRY

    BEGIN TRANSACTION;
    -- CODE BLOCK GOES HERE
    COMMIT TRANSACTION;

END TRY
BEGIN CATCH 

   IF @@TRANCOUNT > 0
   BEGIN
      ROLLBACK TRANSACTION
   END;

   -- GET ERRORS DETAILS OR THROW ERROR

END CATCH;

SET NOCOUNT, XACT_ABORT OFF;

如果在特定情况下(如下一种情况)不使用TRY-CATCH块，则将再次提交第一条语句：

DROP TABLE IF EXISTS [dbo].[StackOverflow];

CREATE TABLE [dbo].[StackOverflow]
(
    [StackID] TINYINT PRIMARY KEY
);

SET XACT_ABORT ON;

BEGIN TRANSACTION;

INSERT INTO [dbo].[StackOverflow] ([StackID])
VALUES (105);

EXEC 
(
    'INSERrrrrrT INTO [dbo].[StackOverflow] ([StackID]) VALUES (106);'
)

COMMIT TRANSACTION;

SET XACT_ABORT OFF;

SELECT [StackID]
FROM [dbo].[StackOverflow];

Answer 2

您需要添加SET XACT_ABORT ON;和/或使用TRY/CATCH，这样脚本就不会在出错后继续运行到COMMIT。

SET XACT_ABORT ON示例：

SET XACT_ABORT ON;
begin transaction
   insert into instructor
   values ('99999', 'Yellow', 'Biology', 700000)

   insert into instructor
   values ('99999', 'Blue', 'Statistics', 85000)
commit;

尝试/捕捉示例：

BEGIN TRY
    begin transaction
       insert into instructor
       values ('99999', 'Yellow', 'Biology', 700000)
    
       insert into instructor
       values ('99999', 'Blue', 'Statistics', 85000)
    commit;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK;
    THROW;
END CATCH;

尽管SET XACT_ABORT ON在此TRY/CATCH模式下是可选的，但我仍然推荐在所有带有说明事务的still代码中使用它。这将确保事务立即回滚，即使批处理被手动取消，或者由于查询超时而没有执行CATCH块代码。否则，未提交的事务将保持打开状态，直到连接关闭或与连接池一起重用为止。