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

у нас есть клиентское приложение, которое работает с некоторыми 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 должен отвечать одинаково для любой язык.

5 155
sql sql-server sql-server-2005 transactions

5 ответов:

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

175

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

Вы можете обернуть это в 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
165

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

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

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

20

вот код с получением сообщения об ошибке работа с 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
16

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

нет, это не так.

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

конечно, вы должны выдать ROLLBACK вместо COMMIT.

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

10