Откат и фиксация транзакций в хранимых процедурах и триггерах
Выполнять инструкции ROLLBACK TRANSACTION и COMMIT TRANSACTION языка Transact-SQL в хранимых процедурах и триггерах можно, но это может привести к ошибкам.
В хранимых процедурах
Если за время выполнения хранимой процедуры значение @@TRANCOUNT изменяется, происходит информационная ошибка (266). Это может случиться в двух следующих сценариях.
- Хранимая процедура вызывается со значением @@TRANCOUNT, равным 1 или более, и выполняет инструкцию ROLLBACK TRANSACTION. Значение @@TRANCOUNT уменьшается до 0, что приводит к ошибке 266 при завершении хранимой процедуры.
- Хранимая процедура вызывается со значением @@TRANCOUNT, равным 1 или более, и выполняет инструкцию COMMIT TRANSACTION. Значение @@TRANCOUNT уменьшается на 1, что приводит к ошибке 266 при завершении хранимой процедуры. Однако если после инструкции COMMIT TRANSACTION будет выполнена инструкция BEGIN TRANSACTION, ошибка не возникнет.
В триггерах
Триггер работает так, как если бы при его выполнении имелась необработанная транзакция. Это верно независимо от того, относится ли инструкция, приведшая к срабатыванию триггера, к неявной или явной транзакции.
Когда начинается выполнение инструкции в режиме с автоматической фиксацией транзакций, неявно выполняется команда BEGIN TRANSACTION, позволяющая отменить все изменения, если при обработке инструкции возникнет ошибка. Эта неявная транзакция никак не влияет на другие инструкции пакета, потому что при завершении обработки инструкции она или фиксируется, или откатывается. Тем не менее, при вызове триггера эта неявная транзакция активна.
При выполнении триггера инициируется неявная транзакция. Если выполнение триггера завершается со значением @@TRANCOUNT, равным 0, происходит ошибка 3609, а обработка пакета инструкций отменяется. Поэтому рекомендуется не использовать в триггерах инструкцию ROLLBACK TRANSACTION, которая сбрасывает значение @@TRANCOUNT до 0, и инструкцию COMMIT TRANSACTION, которая может привести к уменьшению значения @@TRANCOUNT до 0. Выполнение инструкции BEGIN TRANSACTION после отката транзакции предотвратит формирование ошибки, но это может ухудшить логику приложения.
Важно понимать, что при выполнении в триггере инструкции BEGIN TRANSACTION на самом деле инициируется вложенная транзакция. В этой ситуации инструкция COMMIT TRANSACTION зафиксирует только вложенную транзакцию. Так как при откате вложенных транзакций вложенная инструкция BEGIN TRANSACTION игнорируется, инструкция ROLLBACK TRANSACTION, выполняемая в триггере, приводит к откату до состояния перед обработкой инструкций BEGIN TRANSACTION, инициированных самим триггером. Инструкция ROLLBACK откатывает самую внешнюю транзакцию и сбрасывает значение @@TRANCOUNT до 0.
Используя инструкцию ROLLBACK TRANSACTION в триггере, имейте в виду следующее.
- При этом будут отменены все изменения данных, уже выполненные в текущей транзакции, в том числе изменения, выполненные триггером.
- Триггер продолжает выполнять все оставшиеся инструкции после инструкции ROLLBACK. Если какая-нибудь из инструкций изменит данные, откат этих изменений выполнен не будет.
- При выполнении инструкции ROLLBACK в триггере она закрывает и освобождает все курсоры, которые были объявлены и открыты в пакете, содержащем инструкцию, приведшую к срабатыванию триггера. В их число входят курсоры, объявленные и открытые в хранимых процедурах, вызванных пакетом, который привел к срабатыванию триггера. Курсоры, объявленные в пакете до пакета, который привел к срабатыванию триггера, только закрываются. Однако курсоры STATIC и INSENSITIVE остаются открытыми, если справедливо следующее:
- параметр CURSOR_CLOSE_ON_COMMIT имеет значение OFF;
- статический курсор является или синхронным, или полностью заполненным асинхронным курсором.
Чтобы выполнить частичный откат транзакций в триггере, вместо инструкции ROLLBACK TRANSACTION можно использовать инструкцию SAVE TRANSACTION.
См. также
Основные понятия
Другие ресурсы
@@TRANCOUNT (Transact-SQL)
ROLLBACK TRANSACTION (Transact-SQL)
ROLLBACK WORK (Transact-SQL)
SAVE TRANSACTION (Transact-SQL)