SQL Server 2005: путаница с использованием XACT_ABORT=ON с TRY...CATCH

Question

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

У меня есть структурированная хранимая процедура, которая мне понравилась (здесь все упрощено):

CREATE PROCEDURE dbo.usp_clean_and_re_Insert
AS
   SET XACT_ABORT ON;

   BEGIN TRY

      BEGIN TRANSACTION

      -- first clear the table
      DELETE FROM dbo.table1

      -- re-populate the table
      INSERT INTO dbo.table1
      (col1, col2, col3)
      SELECT  1
              ,dbo.fn_DoSomething('20150101')
              ,dbo.fn_DoSomething('20150123')

      COMMIT TRANSACTION

   END TRY

BEGIN CATCH
-- Test XACT_STATE for 0, 1, or -1.
-- If 1, the transaction is committable.
-- If -1, the transaction is uncommittable and should 
--     be rolled back.
-- XACT_STATE = 0 means there is no transaction and
--     a commit or rollback operation would generate an error.

-- Test whether the transaction is uncommittable.
IF (XACT_STATE()) = -1
BEGIN
    PRINT 'The transaction is in an uncommittable state.' +
          ' Rolling back transaction.'
    ROLLBACK TRANSACTION;
END;

-- Test whether the transaction is active and valid.
IF (XACT_STATE()) = 1
BEGIN
    PRINT 'The transaction is committable.' + 
          ' Committing transaction.'
    COMMIT TRANSACTION;   
END;
END CATCH;

Таким образом, SP предназначен для работы следующим образом: если транзакция в какой-то момент завершится неудачно, он должен откатиться. Поэтому, когда бит вставки завершается неудачно, бит удаления должен быть откатан, т.е. таблица должна быть в том же состоянии, что и раньше.

Теперь предположим, что во время выполнения функция dbo.fn_DoSomething() недоступна (она была удалена администратором базы данных по ошибке). SP, как написано выше, работает должным образом, то есть транзакция откатывается, таблица остается неповрежденной, а сообщения об ошибках, отображаемые в SSMS, выглядят следующим образом:

"Сообщение 208, уровень 16, состояние 1, процедура usp_clean_and_re_Insert, строка 15 Неверное имя объекта" dbo.fn_DoSOmething "."

Однако по какой-то причине операторы PRINT из блока CATCH, похоже, не выполняются, т.е. я не вижу их в SSMS? В документации Microsoft по TRY...CATCH говорится, что если во время выполнения в блоке TRY возникают ошибки, выполнение передается в блок CATCH ( https://msdn.microsoft.com/en-us/library/ms175976(v=sql.90).aspx).

Однако если я уберу XACT_ABORT ON, все станет еще более странным:

1. Заявления PRINT все еще не появляются в SSMS

2. те же ошибки, что и выше, отображаются правильно, т.е.

"Сообщение 208, уровень 16, состояние 1, процедура usp_clean_and_re_Insert, строка 15 Неверное имя объекта" dbo.fn_DoSOmething "."

3. Есть последняя ошибка, которая говорит:

"Сообщение 266, Уровень 16, Состояние 2, Процедура usp_clean_and_re_Insert, Строка 52 Счетчик транзакций после EXECUTE указывает на отсутствие оператора COMMIT или ROLLBACK TRANSACTION. Предыдущий счетчик = 0, текущий счетчик = 1".

Это приводит к блокировке таблицы до тех пор, пока я не отключу SSMS (окно запросов, в котором запускается SP), после чего таблица снова становится доступной со всеми нетронутыми результатами (поэтому механизм DB должен неявно откатить незафиксированную транзакцию).

Читая другие сообщения об этом сообщении об ошибке (например, это: количество транзакций после EXECUTE указывает на несовпадающее количество операторов BEGIN и COMMIT. Предыдущий счет = 1, текущий счет = 0), я понимаю, что мне нужно проверить XACT_STATE в блоке CATCH и откатите незафиксированные транзакции (это тот же совет от: https://msdn.microsoft.com/en-us/library/ms189797.aspx), но это именно то, что я сделал в вышеупомянутом SP, и все же транзакция не откатывается (без XACT_ABORT ON), пока я не отключу SSMS?

Я сбит с толку! В итоге:

1. Почему я не вижу операторы PRINT в SSMS?

2. Почему ROLLBACK TRANSACTION в блоке CATCH не выполняется при удалении XACT_ABORT ON из хранимой процедуры?

3. Зачем вообще использовать TRY...CACTH, если XACT_ABORT ON, кажется, делает эту работу самостоятельно? Т.е. если я удаляю Try..catch и оставляю XACT_ABORT ON, это откатывает транзакцию, так зачем мне TRY CATCH с неявным ROLLBACK TRANSACTION в блоке catch?

Answer 1

Я думаю, и могу ошибаться, что XACT_ABORT в этом случае не будет работать, потому что вы еще не запускаете транзакцию. Ваша функция не существует, что означает, что SQL Server не выполнит вашу транзакцию, прежде чем вы даже коснетесь БД. Читая справочную страницу для XACT_STATE и предоставленный пример, похоже, что вам действительно не удалось выполнить чтение / запись. Ваш запрос не делает этого так далеко, потому что оптимизатор видит, что у вас есть синтаксическая ошибка (вызывается несуществующая функция, которую вы не создали к моменту ее выполнения).

Если вы прочитали эту страницу (справочную страницу для XACT_STATE) и доступный пример, она настоятельно рекомендовала установить XACT_STATE только при возникновении ошибки, связанной с ограничениями, которая произойдет только после попытки изменения данных:

https://msdn.microsoft.com/en-us/library/ms189797.aspx

В качестве способа тестирования вы можете использовать профилировщик для отслеживания транзакций на dbo.table1 . Держу пари, что при XACT_ABORT ON в запросе удаление не происходит. С его удалением, скорее всего, оптимизатор выбирает другой план выполнения, который позволяет выполнять удаление.