Каковы ограничения блока try-catch, препятствующие откату в коде в блоке TRY и в хранимой процедуре при включенном xact abort?

Вопрос или проблема

Каковы ограничения блока try-catch, препятствующие откату в коде в блоке TRY и в хранимой процедуре при включенном xact abort?

CREATE PROCEDURE insert_data @a int, @b int AS 
   SET XACT_ABORT, NOCOUNT ON
   BEGIN TRY
      BEGIN TRANSACTION
      INSERT sometable(a, b) VALUES (@a, @b)
      INSERT sometable(a, b) VALUES (@b, @a)
      COMMIT TRANSACTION
   END TRY
   BEGIN CATCH
      IF @@trancount > 0 ROLLBACK TRANSACTION
      DECLARE @msg nvarchar(2048) = error_message()  
      RAISERROR (@msg, 16, 1)
      RETURN 55555
   END CATCH

IF @@trancount > 0 ROLLBACK TRANSACTION

Ответ или решение

Ответ на ваш вопрос требует рассмотрения особенностей обработки ошибок в T-SQL, особенно в контексте использования TRY-CATCH и параметра SET XACT_ABORT ON. Ниже приведены ключевые моменты, которые помогут прояснить ограничения и поведение, связанное с этой темой.

1. Ограничения TRY-CATCH, мешающие откату в вложенных процедурах

Ограничения обработки ошибок в TRY-CATCH:

• В случае, если происходит ошибка в коде внутри блока TRY, управление передается в блок CATCH. Однако, если ошибка вызвана выполнением другой хранимой процедуры, которая приводит к неявному ROLLBACK, блок CATCH не может адекватно обработать транзакцию, начатую вне него.
• Если хранимая процедура, выполняемая внутри блока TRY, инициирует собственную транзакцию и выдает ошибку, то это может привести к ошибке "Transaction count after ROLLBACK is inconsistent". Это означает, что не удастся осуществить ROLLBACK для текущей транзакции в блоке CATCH.
• Если ошибка, вызванная другой хранимой процедурой, не инициирует экстраординарное состояние, то она также не будет поймана в блоке CATCH и транзакция может остаться открытой.

2. Причины, по которым откат не выполняется при ошибке в другой хранимой процедуре

Причины неподходящего поведения:

• Когда SET XACT_ABORT ON, это гарантирует, что если SQL Server встречает ошибку, транзакция автоматически откатится, но это лишь касается ошибок, обнаруженных непосредственно в текущем контексте выполнения. Если ошибка возникает в результате вызова вложенной процедуры, состояние ошибки может быть «споткнутым» до уровня блока CATCH.
• При выполнении хранимой процедуры, если внутри нее была инициирована новая транзакция, то даже при установке SET XACT_ABORT ON текущая транзакция не будет откатена, поскольку управление передается другой транзакции, что придает ей автономность.
• Чтобы обработать ошибки, возникающие в хранимых процедурах, необходимо явно контролировать транзакции, особенно применять IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION, чтобы гарантировать стабильность состояния базы данных.

Рекомендации

1. Контроль за транзакциями:
   Всегда используйте точное управление транзакциями (BEGIN TRANSACTION, COMMIT, ROLLBACK), даже если вы вызываете другие хранимые процедуры, чтобы соблюсти согласованность и целостность данных.

2. Дополнительная обработка ошибок:
   Включите детальную обработку ошибок в вызываемых хранимых процедурах, например через механизмы возврата кодов ошибок, чтобы условно управлять при возникновении проблем.

3. Документация и тестирование:
   Обязательно документируйте все возможные ошибки и сценарии. Регулярное тестирование разных случаев с использованием утилит для мониторинга и логирования также поможет избежать неожиданностей.

Заключение: используя TRY-CATCH и SET XACT_ABORT ON, важно понимать взаимодействие между разными уровнями транзакций и ошибок, возникающими как на уровне текущей процедуры, так и на уровне вложенных. Тщательное управление транзакциями поможет минимизировать проблемы с откатами и целостностью данных внутри вашей базы данных.