Почему следующая транзакция t-sql не работает должным образом?

Чтобы проверить, как работает транзакция, я написал следующий 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 строка затронута) Сообщение 2627, уровень 14, состояние 1, строка 100 Нарушение ограничения PRIMARY KEY 'PK__instruct__3214EC278C8DA99F'. Не удается вставить повторяющийся ключ в объект «dbo.insstructor». Повторяющееся значение ключа (99999). Заявление было прекращено.

Время завершения: 2019-11-06T14:02:27.3436411+02:00


sql-server transactions
person Shahin Mehdipour Ataee    schedule 06.11.2019    source источник
comment
Вы можете получить этот результат, если XACT_ABORT равно OFF, потому что нарушение повторяющегося ключа прерывает только вторую вставку, а не транзакцию в целом, поэтому COMMIT все еще происходит. Если вы сначала сделаете SET XACT_ABORT ON, фиксация не произойдет. Точные правила того, что прерывает и не прерывает транзакцию, действительно сложны, даже если XACT_ABORT может значительно упростить ситуацию.   -  person Jeroen Mostert    schedule 06.11.2019

Ответы (2)


arrow_upward
0
arrow_downward

Потому что, когда XACT_ABORT ВЫКЛЮЧЕН (по умолчанию):

Когда SET XACT_ABORT имеет значение OFF, в некоторых случаях выполняется откат только инструкции 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];
person gotqn    schedule 06.11.2019
comment
Ошибки компиляции не могут быть обнаружены, поскольку они препятствуют запуску всего пакета, включая TRY/CATCH, поэтому обертывание BEGIN TRY вокруг всей транзакции не дает дополнительного эффекта. Единственный способ CATCH таких ошибок состоит в том, чтобы поместить весь запрос в динамический EXEC, как показывает последний пример (у которого есть свои применения, но это не то, что вы обычно хотите делать; вам лучше управлять транзакцией из клиентского кода). ). - person Jeroen Mostert; 06.11.2019
comment
@JeroenMostert Правда, я имел в виду, что если у вас есть, например, динамический оператор T-SQL, и вы делаете что-то не так (например, забыли место и объединили несколько предложений во время построения), использования XACT, установленного на ON, недостаточно. - person gotqn; 06.11.2019
comment
Большое спасибо, @gotqn! - person Shahin Mehdipour Ataee; 06.11.2019

arrow_upward
0
arrow_downward

Вам нужно добавить 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, я все же рекомендую его во всем коде T-SQL с явными транзакциями. Это обеспечит немедленный откат транзакции, даже если пакет отменен вручную или из-за тайм-аута запроса, когда код блока CATCH не выполняется. В противном случае незафиксированная транзакция останется открытой до тех пор, пока соединение не будет закрыто или повторно не будет использовано с пулом соединений.

person Dan Guzman    schedule 06.11.2019
comment
Пожалуйста, не могли бы вы сообщить, почему вы упомянули и/или используете TRY/CATCH? В примере с try/catch вы не использовали xact_abort, поэтому является ли он необязательным, когда мы используем try/catch? - person variable; 20.02.2021
comment
@variable, я добавил пояснение к своему ответу. - person Dan Guzman; 20.02.2021