Автоматический откат транзакций в SQL Server: руководство

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

BEGIN TRANSACTION
-- Расположите ваш SQL код здесь... его забыть недопустимо

IF @@ERROR = 0
    COMMIT TRANSACTION
ELSE
    ROLLBACK TRANSACTION

В SQL Server для объединения нескольких SQL-операций используйте конструкцию транзакции. Проверьте значение @@ERROR: если оно равно нулю, выполните подтверждение транзакции с помощью COMMIT TRANSACTION, в противном случае – отменяем её при помощи ROLLBACK TRANSACTION.

Включите SET XACT_ABORT для гарантированной надежности

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

SET XACT_ABORT ON;
BEGIN TRANSACTION;
-- Развиваем SQL коды здесь 
COMMIT TRANSACTION;

Использование SET XACT_ABORT позволяет обеспечить единообразие транзакций в различных средах программирования, что крайне важно с точки зрения безопасности данных. Это особо актуально для SQL Server 2005 и более новых версий.

Конструкция TRY..CATCH: ваше щит и мачта от аномалий транзакций

Управление транзакциями посредством TRY-CATCH

Воспользуйтесь блоком TRY...CATCH для контроля над выполнением транзакций и удобного резолюции ошибок – он будет эффективно инкапсулировать ваши SQL-запросы.

BEGIN TRY
    BEGIN TRANSACTION;
    -- Здесь лежит ваш SQL код. Никакой магии!
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
    -- Информация об ошибке отсутствует. Найдите её!
END CATCH

Если возникает ошибка, управление автоматически передаётся в блок CATCH, где определите необходимость отката текущей транзакции.

Эффективная обработка ошибок

Если ошибка произошла, используйте @@TRANCOUNT, чтобы проверить статус транзакции, и примените RAISERROR для сигнализации о проблеме:

IF @@TRANCOUNT > 0 BEGIN
    PRINT 'Откатываем транзакцию... почти как путешествие во времени'
    ROLLBACK;
END;

RAISERROR ('Внимание! Произошла ошибка', 16, 1);

Такой подход обеспечивает безукоризненное управление ошибками и предотвращает неполное выполнение транзакций.

Сбор информации об ошибке

В блоке CATCHшироко охватывайте информацию об ошибке в переменные.

DECLARE @ErrorMessage NVARCHAR(4000), @ErrorSeverity INT, @ErrorState INT;

SELECT 
    @ErrorMessage = ERROR_MESSAGE(),
    @ErrorSeverity = ERROR_SEVERITY(),
    @ErrorState = ERROR_STATE();

-- Информация о ошибке готова для логирования

Такой лог ошибок позволит быстро находить и исправлять проблемы, кроме того, определить причины происшедшего сбоя.

Поведение транзакций : выучите неожиданность

Консистентность: стремление к совершенству!

Поведение транзакций SQL Server может иногда казаться сумбурным. Ваша задача – обеспечить консистентное взаимодействие с транзакциями на разных языках программирования.

Ручной откат: точно знаячто вы делаете

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

-- Если выполнение SQL-запроса слишком затягивается, возможно, что-то пошло не так.

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

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

Воспринимайте транзакцию как строительство из кирпичиков (🧱):

🧱 Начало транзакции:
[кирпич] [кирпич] [кирпич] <-- Все кирпичики на месте (операции прошли успешно).
[   ] <-- Кирпичик отсутствует из-за ошибки. Это ошибка!

При ошибке все «кирпичики » 🧱 разбросаны:

О нет! Все кирпичи [🧱] рухнули! Пришло время для отката и начала заново.

Откатив транзакцию, получаем:

Все кирпичики [🧱🧱🧱] на месте,
**ИЛИ**
Чистая площадка! [   ] (Полуобрушившихся башен не бывает)

Таким образом, либо транзакция выполнена полностью (все операции успешны), либо все изменения откатываются, и состояние возвращается к исходному.

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