sql-server - 为什么下面的 t-sql 事务没有按预期工作？

Question

为了测试事务如何工作，我编写了以下 t-sql。由于第一个属性是主键，因此不应提交任何插入。但是，第一个插入被提交了吗？为什么？

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 row affected) Msg 2627, Level 14, State 1, Line 100 Violation of PRIMARY KEY constraint 'PK__instruct__3214EC278C8DA99F'. Cannot insert duplicate key in object 'dbo.instructor'. The duplicate key value is (99999). The statement has been terminated.

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

Answer 1

因为，当 XACT_ABORT 关闭时(这是默认设置):

When SET XACT_ABORT is OFF, in some cases only the Transact-SQL statement that raised the error is rolled back and the transaction continues processing.

当它是ON时:

.. if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.

这就是我们需要打开它的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];

另外，请注意:

Compile errors, such as syntax errors, are not affected by 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 block ，就像下一个一样，第一个语句将再次提交:

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];