SQL Server - транзакции откатываются при ошибке?

Question

У нас есть клиентское приложение, которое выполняет SQL на SQL Server 2005, например:

BEGIN TRAN;
INSERT INTO myTable (myColumns ...) VALUES (myValues ...);
INSERT INTO myTable (myColumns ...) VALUES (myValues ...);
INSERT INTO myTable (myColumns ...) VALUES (myValues ...);
COMMIT TRAN;

Он отправляется одной длинной строковой командой.

В случае сбоя одной из вставок или сбоя какой-либо части команды SQL Server откатывает транзакцию? Если он не откатывается, нужно ли отправлять вторую команду для его отката?

Я могу дать конкретную информацию о API и языке, который я использую, но я думаю, что SQL Server должен отвечать одинаково для любого языка.

Answer 1

Вы можете положить set xact_abort on перед вашей транзакцией, чтобы убедиться, что sql автоматически откатывается в случае ошибки.

Answer 2

Вы правы в том, что вся транзакция будет откатана. Вы должны выполнить команду, чтобы откатить его.

Вы можете обернуть это в TRY CATCH блок следующим образом

BEGIN TRY
    BEGIN TRANSACTION

        INSERT INTO myTable (myColumns ...) VALUES (myValues ...);
        INSERT INTO myTable (myColumns ...) VALUES (myValues ...);
        INSERT INTO myTable (myColumns ...) VALUES (myValues ...);

    COMMIT TRAN -- Transaction Success!
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRAN --RollBack in case of Error

    -- you can Raise ERROR with RAISEERROR() Statement including the details of the exception
    RAISERROR(ERROR_MESSAGE(), ERROR_SEVERITY(), 1)
END CATCH

Answer 3

Вот код с получением сообщения об ошибке при работе с MSSQL Server 2016:

BEGIN TRY
    BEGIN TRANSACTION 
        -- Do your stuff that might fail here
    COMMIT
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRAN

        DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE()
        DECLARE @ErrorSeverity INT = ERROR_SEVERITY()
        DECLARE @ErrorState INT = ERROR_STATE()

    -- Use RAISERROR inside the CATCH block to return error  
    -- information about the original error that caused  
    -- execution to jump to the CATCH block.  
    RAISERROR (@ErrorMessage, -- Message text.  
               @ErrorSeverity, -- Severity.  
               @ErrorState -- State.  
               );
END CATCH

Answer 4

Из статьи MDSN " Управление транзакциями ( компонент Database Engine)".

Если в пакете возникает ошибка оператора времени выполнения (например, нарушение ограничения), по умолчанию в компоненте Database Engine выполняется откат только оператора, который сгенерировал ошибку. Вы можете изменить это поведение, используя инструкцию SET XACT_ABORT. После выполнения команды SET XACT_ABORT ON любая ошибка оператора во время выполнения вызывает автоматический откат текущей транзакции. SET XACT_ABORT не влияет на ошибки компиляции, такие как синтаксические ошибки. Для получения дополнительной информации см. SET XACT_ABORT (Transact-SQL).

В вашем случае он откатит всю транзакцию, если какая-либо из вставок не удалась

Answer 5

В случае сбоя одной из вставок или сбоя какой-либо части команды сервер SQL откатывает транзакцию?

Нет.

Если он не откатывается, нужно ли отправлять вторую команду для его отката?

Конечно, вы должны выпустить ROLLBACK вместо COMMIT,

Если вы хотите решить, следует ли совершать или откатывать транзакцию, вы должны удалить COMMIT предложение из утверждения, проверьте результаты вставок, а затем выдать либо COMMIT или же ROLLBACK в зависимости от результатов проверки.

Answer 6

Используя этот метод в качестве альтернативы, вы также можете записать номер ошибки после каждого оператора, а затем использовать оператор if, чтобы определить, следует ли фиксировать или откатывать. Принятый ответ — лучший однострочный ответ, но если вы хотите узнать больше о возникшей проблеме, а не просто откатывать ее, вы можете использовать приведенный ниже пример и добавить дополнительную информацию, чтобы увидеть проблему. Конечно, вы также можете выполнить блок Try-Catch с помощьюRAISERROR.

Вот пример того, что у меня есть на скорую руку:

      DECLARE @errorNumber int;

BEGIN TRANSACTION;
    INSERT INTO [table2] ([field1], [field2])
    SELECT [fieldA], [fieldB]
    FROM [table1];
        
    SET @errorNumber = @@ERROR;

    UPDATE [table3]
    SET [field1] =
        (SELECT COUNT(ID)
        FROM [table2]
        WHERE [table2].[fieldA] = [table3].[field2])
    WHERE [field1] IS NULL;
        
    SET @errorNumber = @@ERROR;

IF @errorNumber = 0
    COMMIT TRANSACTION;
ELSE
    BEGIN
        ROLLBACK TRANSACTION;
        PRINT CONCAT('Transaction rolled back with error number: ',@errorNumber);
    END