Работа с транзакциями SQL: commit после rollback в try/catch

Быстрый ответ

Чтобы избежать выполнения commit после rollback, можно использовать конструкцию TRY для осуществления SQL-операций, а в блоке CATCH управлять ошибками и установить флаг, который будет указывать на возможность commit. Взглянем на пример кода:

DECLARE @CommitFlag BIT = 1;  -- По умолчанию мы готовы к commit (если не возникнет ошибок 😉)

BEGIN TRANSACTION;
BEGIN TRY
    -- Ваши SQL-операции
    
    -- Если возникает ошибка, флаг не изменится
END TRY
BEGIN CATCH
    SET @CommitFlag = 0;   -- Произошла ошибка! Операция должна быть прервана 🚨
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;  -- Начинаем процесс отката...
    -- Обработка исключения производится здесь
END CATCH

IF @CommitFlag = 1 AND @@TRANCOUNT > 0
    COMMIT TRANSACTION;  -- Если ошибок нет, утверждаем транзакцию.

В данном случае @CommitFlag определяет судьбу транзакции: если его значение равно 0, будет инициирован откат в блоке CATCH. Commit же применяется только в том случае, если флаг остаётся положительным.

Необходимость поддержания целостности транзакции: @@TRANCOUNT

В случаях работы с вложенными транзакциями или транзакциями в хранимых процедурах очень важно сохранять целостность всех элементов транзакционной цепочки. Здесь @@TRANCOUNT играет ключевую роль.

Защищаем данные от несоответствий

Используйте @@TRANCOUNT для предотвращения некорректного commit в случае, если транзакция была уже откачена. Всегда проверяйте значение @@TRANCOUNT перед применением commit или rollback:

IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;

Подробнее об этом расскажет наш спикер на видео

Вложенные транзакции: уровень за уровнем

Если вы работаете с вложенными транзакциями, отслеживайте количество транзакций на каждом уровне и выполните откат только на самом первом уровне. Это поможет не нарушить целостность цепочки транзакций:

IF @@TRANCOUNT = 1
    ROLLBACK TRANSACTION;
ELSE
    SAVE TRANSACTION MySavePoint;

Эффективное использование инструментов: THROW и RAISERROR

Надлежащая обработка ошибок обеспечивает прозрачность и корректное информирование всех заинтересованных сторон. С помощью команды THROW, представленной в SQL Server 2012 и более поздних версиях, можно повторно вызывать выявленную ошибку:

THROW;  -- Повторный выброс ошибки

Для того чтобы сообщить об ошибке пользователю или в целом по причине совместимости со старыми версиями SQL сервера, можно использовать RAISERROR:

RAISERROR ('Пользовательское сообщение об ошибке', 16, 1);

1. Персонализированные сообщения: Больше контекста, больше информации для разработчиков.
2. Логи ошибок: Используйте вместе с RAISERROR процедуры логирования ошибок.
3. Уровни серьёзности: Ставьте уровни серьёзности с учетом критичности ошибки.

Диагностика ошибок

Чтобы лучше понимать причины отказов, важно диагностировать ошибки. Для это можно использовать переменные, такие как @ErrMsg, в которой сохраняется информация об ошибках:

DECLARE @ErrMsg NVARCHAR(2048);
SET @ErrMsg = ERROR_MESSAGE();
-- @ErrMsg готова к логированию или отображению

Лучшие практики обработки ошибок

1. Фиксация подробностей: Записывайте сообщения об ошибках, коды ошибок, информацию о процедурах и номерах строк.
2. Централизированный сбор логов: Собирайте всю информацию об ошибках в одном логе.
3. Система оповещений: Создайте систему оповещения, основанную на информации об ошибках.

Визуализация

Схема управления транзакциями с использованием SQL TRY/CATCH, ROLLBACK и COMMIT:

Ход Транзакционного Поезда 🚂:

🔄 Начало транзакции
  |
  🛑 Блок TRY обнаруживает ошибку
  |   ↳ 🚧 Блок CATCH начинает действовать
  |         ↳ 🔄 ОТКАТ транзакции
  |             ↳ 💥 Конец цепочки, commit не происходит.
  |
  ✅ Ошибок нет
      ↳ 💾 COMMIT транзакции

• Символ 🚧 указывает на решающий блок CATCH, обеспечивающий сохранность нашего "транзакционного поезда" и не допускающий совершение нежелательного COMMIT после обязательного ROLLBACK.

Обращайтесь к NOLOCK осторожно

Применение подсказки NOLOCK с операторами SELECT позволяет избежать блокировок данных, однако это может привести к грязному чтению. Используйте эту опцию осознанно, всегда помните о возможных рисках:

SELECT * FROM MyTable WITH (NOLOCK);

Размышления о NOLOCK

1. Согласованность данных: Данные могут оказаться неточными.
2. Параллелизм: Вашему случаю важнее скорость чтения или точность данных?
3. Тестирование: Тщательно протестируйте эту функцию перед внедрением ее в продакшен.

Тупиковые ситуации: это не проблема!

Тупиковые ситуации или deadlocks иногда становятся проблемой при работе с транзакциями. В таких случаях установите приоритет тупиковой ситуации и при необходимости измените уровень изоляции:

SET DEADLOCK_PRIORITY LOW;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

Стратегии противодействия тупиковым ситуациям

1. Индексирование: Правильное и точное индексирование помогает сократить время блокировки ресурсов.
2. Последовательность доступа к данным: Всегда обращайтесь к ресурсам в одной и той же последовательности.
3. Короткие транзакции: Чем быстрее завершаются транзакции, тем лучше.

Полезные материалы

1. TRY...CATCH (Transact-SQL) – SQL Server | Microsoft Docs — Официальная документация Microsoft по конструкции TRY...CATCH в SQL Server.
2. SQL Server Error Handling Workbench – Simple Talk — Детальное руководство по обработке ошибок.
3. Handling Errors in SQL Server 2012 – Simple Talk — Больше информации об обработке исключений.
4. Exception Handling in SQL Server – CodeProject — Практическое руководство по использованию TRY и CATCH.
5. Use Caution with SQL Server's MERGE Statement — Рекомендации по использованию оператора MERGE в SQL Server.