Нужно ли использовать блок try..catch и явный откат в процедуре SQL Server?

Если я кодирую процедуру SQL Server (2008r2) и переношу ее в транзакцию, мне нужно явно заключить ее в блок try..catch, а затем явно вызвать откат в блоке catch или он выходит и откатывается сам по себе?

то есть:.

Как это сделать:

    begin transaction

    begin try
    delete from....

    insert into...
    end try
    begin catch
    rollback transaction
    return
    end catch

    commit transaction

Сравните с:

    begin transaction
    delete from....

    insert into...
    commit transaction

Спасибо за любую помощь.

Ответы

Ответ 1

Ответ на ваш вопрос зависит от параметра SET XACT_ABORT:

Указывает, будет ли SQL Server автоматически откатывать текущий транзакции, когда оператор Transact-SQL вызывает ошибку времени выполнения.

Когда SET XACT_ABORT включен, если оператор Transact-SQL вызывает ошибка во время выполнения, вся транзакция завершается и откатывается.

Когда SET XACT_ABORT выключен, в некоторых случаях только Transact-SQL оператор, который поднял ошибку, откат и транзакция продолжает обработку. В зависимости от серьезности ошибки, вся транзакция может быть отменена, даже если SET XACT_ABORT выключен. OFF является настройкой по умолчанию.

Ошибки компиляции, такие как синтаксические ошибки, не зависят от SET XACT_ABORT.

Например, попробуйте следующий код. Первое деление на 0 вызывает ошибку, но продолжает выполнение. Второе деление на ноль вызывает ошибку, которая прерывает выполнение:

begin transaction

set xact_abort off

select 1 / 0 -- causes divide by zero error, but continues
select @@trancount -- returns 1

set xact_abort on

select 1 / 0 -- causes divide by zero error and terminates execution
select @@trancount -- we never get here

rollback

Если XACT_ABORT включен, ошибки будут прервать транзакцию, и вам не понадобится TRY/CATCH.

Если XACT_ABORT выключен, вам нужно будет проверить статус каждого оператора, чтобы увидеть, произошла ли ошибка:

begin transaction

delete from...
if @@error <> 0
begin
    if @@trancount > 0
        rollback
    return
end

insert into...
if @@error <> 0
begin
    if @@trancount > 0
        rollback
    return
end

commit

Однако, если вы когда-нибудь найдете случай, когда вам нужно TRY/CATCH, вам может понадобиться сделать что-то особенное при возникновении ошибки. Если это так, не забудьте TRY/CATCH обрабатывать исключения:

begin transaction

set xact_abort on

begin try
    select 1 / 0 -- causes divide by zero error and terminates execution
    select @@trancount -- we never get here
    commit
end try
begin catch
    select xact_state() -- this will be -1 indicating you MUST rollback before doing any other operations
    select @@trancount -- this will probably be one, because we haven't ended the transaction yet
    if xact_state() <> 0
    begin try
        select 'rollback'
        rollback

        -- do something to handle or record the error before leaving the current scope
        select 'exception processing here'
        --insert into...
    end try
    begin catch
        -- ignore rollback errors
    end catch

end catch

Ответ 2

откаты будут выполняться автоматически, если есть ошибка IN MOST CASES, НО НЕ ВСЕ

если вы хотите гарантировать откат для всех ошибок перед началом транзакции с SET XACT_ABORT ON

Лучшая практика заключается в том, чтобы выявить ошибки catch с блоком try-catch и принять меры там, включая, возможно, откат и отчетность/протоколирование ошибки.

Ответ 3

Это зависит от уровня серьезности ошибки. Достаточно высокий - возможно, 16? - процесс может остановиться на линии сбоя, оставив транзакцию открытой и ваши блокировки на месте. Если в транзакции есть какая-либо вероятность ошибки, вы обязательно захотите ее обернуть в блок try-catch, как это было в первом примере.