Транзакции и блокировка транзакций
Откаты транзакций
Откаты могут происходить в двух формах: автоматический откат, выполняемый SQL Server, или программируемый вручную откат. В определенных случаях SQL Server выполнит для вас откат. Но для обеспечения логической согласованности в ваших программах вы должны при необходимости явным образом обращаться к оператору ROLLBACK. Рассмотрим более подробно эти два метода.
Автоматические откаты
Как уже говорилось ранее в этой лекции, при сбое транзакции вследствие серьезной ошибки, такой как потеря сетевого соединения при выполнении данной транзакции или отказ клиентского приложения или компьютера, SQL Server выполняет автоматический откат транзакции. Во время отката происходит отмена всех модификаций, выполненных в данной транзакции, и освобождение всех ресурсов, использовавшихся этой транзакцией.
Если при исполнении какого-либо оператора возникает ошибка, такая как нарушение ограничения или правила, то по умолчанию SQL Server автоматически выполняет откат только этого определенного оператора, в котором возникла ошибка. Чтобы изменить это поведение, вы можете использовать оператор SET XACT_ABORT. Если задать для XACT_ABORT значение ON, то в случае ошибки исполнения SQL Server автоматически выполнит откат транзакции. Этот метод полезно использовать, например, когда один оператор вашей транзакции не выполняется из-за того, что он нарушает ограничение по внешнему ключу, и поэтому вы не хотите, чтобы выполнялись последующие операторы. По умолчанию для XACT_ABORT задано значение OFF.
SQL Server использует также автоматический откат при восстановлении сервера. Например, если у вас отключился источник питания во время выполнения транзакций и произошла перезагрузка системы, то при перезапуске SQL Server выполнит автоматическое восстановление. При автоматическом восстановлении происходит чтение информации из журнала транзакций для воспроизведения фиксированных транзакций, которые не были записаны на диск, и отката транзакций, которые находились в процессе выполнения (еще не были фиксированы) в момент отказа источника питания.
Программируемые откаты
С помощью оператора ROLLBACK вы можете указать точку в транзакции, где будет выполнен откат. Оператор ROLLBACK прекращает данную транзакцию и выполняет откат (отмену) всех выполненных изменений. Если вы запускаете откат в середине какой-либо транзакции, то остальная часть этой транзакции игнорируется. Если, например, эта транзакция является хранимой процедурой и оператор ROLLBACK выполняется в этой процедуре, то происходит откат всей процедуры и переход к обработке оператора, следующего после вызова хранимой процедуры.
Если вам нужно выполнить откат, исходя из количества строк, возвращенных оператором SELECT, используйте системную переменную @@ROWCOUNT. Эта переменная содержит количество строк, которое возвращается в результате запроса или на которое влияет модификация или удаление. Если конкретное количество строк не имеет значения и вам просто нужно определить наличие строки или строк для определенного условия, то вы можете использовать совместно с оператором SELECT оператор IF EXISTS. Этот оператор не возвращает количества строк данных, а только значение TRUE или FALSE. Если результат равен TRUE, то выполняется следующий оператор; если результат равен FALSE, то следующий оператор не выполняется. В операторе IF EXISTS может также использоваться предложение ELSE. Рассмотрим пример использования предложения IF EXISTS...ELSE. В следующей транзакции происходит модификация значений ставки арендной платы (royalty) в таблице roysched для двух ставок арендной платы (royalty rates) (16 процентов и 15 процентов), но если ни одна из этих ставок не существует, то ни одна из команд UPDATE выполняться не будет. Для обеспечения такого результата в этой транзакции используется оператор ROLLBACK.
BEGIN TRAN update_royalty --Начать транзакцию. USE pubs IF EXISTS (SELECT titles.title, roysched.royalty FROM titles, roysched WHERE titles.title_id = roysched.title_id AND roysched.royalty = 16) UPDATE roysched SET royalty = 17 WHERE royalty = 16 --Имеется 13 строк. ELSE ROLLBACK TRAN update_royalty --ROLLBACK не выполняется. IF EXISTS (SELECT titles.title, roysched.royalty FROM titles, roysched WHERE titles.title_id = roysched.title_id AND roysched.royalty = 15) --Нет ни одной строки. BEGIN UPDATE roysched SET royalty = 20 WHERE royalty = 15 COMMIT TRAN update_royalty END ELSE --Выполняется ROLLBACK. ROLLBACK TRAN update_royalty GO
В этой транзакции первый оператор IF EXISTS (SELECT...) определяет, что существует несколько строк, и поэтому оператор UPDATE выполняется (показывая, что модификация касается 13 строк). Второй оператор SELECT возвращает 0 строк, и поэтому второй оператор UPDATE не выполняется, но выполняется оператор ROLLBACK TRAN update_royalty. Поскольку ROLLBACK выполняет откат всех модификаций до самого начала данной транзакции, то происходит откат первой модификации. Если снова выполнить первый оператор SELECT, то вы по-прежнему увидите 13 строк со значением royalty, равным 16, как и было в исходном состоянии базы данных, когда мы запускали данную транзакцию. И снова изменение royalty на значение 17 будет отменено (будет выполнен откат) из-за оператора ROLLBACK.
Примечание.В этой транзакции было использовано несколько новых ключевых слов: IF, ELSE, BEGIN и END. Эти ключевые слова будут подробно описаны в
"Расширенное описание
T-SQL"
.Откат транзакции нельзя выполнить после ее фиксирования. (Напомним, что внутренняя транзакция на самом деле не фиксируется, пока не будет фиксирована внешняя транзакция.) Чтобы можно было выполнить явный откат отдельной транзакции, оператор ROLLBACK должен предшествовать оператору COMMIT. В случае вложенных транзакций после фиксирования внешней транзакции (и, тем самым, внутренних транзакций) уже нельзя выполнить откат ни одной из транзакций. Как уже отмечалось, вы не можете выполнить откат только внутренних транзакций; должен быть выполнен откат всей транзакции (всех внутренних транзакций и внешней транзакции). Поэтому, включив имя транзакции в оператор ROLLBACK, проследите за тем, чтобы было указано имя внешней транзакции (во избежание путаницы и сообщения об ошибке от SQL Server). Однако существует обходной путь, позволяющий избежать отката всей транзакции и сохранить часть модификаций: вы можете использовать точки сохранения.
Точки сохранения
Вы можете избежать отката всей транзакции; для этого нужно использовать точку сохранения, которая позволяет выполнять откат только до определенной точки в транзакции, а не до самого начала транзакции. Все модификации, выполненные до точки сохранения, остаются в силе и не подвергаются откату; но для всех операторов, выполняемых после точки сохранения (которую вы должны указать в транзакции) вплоть до оператора ROLLBACK, будет выполнен откат. Затем начнут выполняться операторы, следующие за оператором ROLLBACK. Если вы затем зададите откат транзакции без указания точки сохранения, то, как обычно, будет выполнен откат всех модификаций вплоть до начала данной транзакции, т.е. будет отменена вся транзакция. Отметим, что при откате транзакции до точки сохранения SQL Server не освобождает блокированные ресурсы. Они будут освобождены после фиксирования транзакции или после отката всей транзакции.
Чтобы указать точку сохранения в транзакции, используйте следующий оператор:
SAVE TRAN[SACTION] {имя_точки_сохранения | @переменная_с_именем_точки_сохранения}Поместите точку сохранения в том месте транзакции, до которого вы хотите выполнять откат. Чтобы выполнить откат до точки сохранения, используйте ROLLBACK TRAN вместе с именем точки сохранения, как это показано ниже:
ROLLBACK TRAN имя_точки_сохранения
Вы можете использовать другие операторы T-SQL, чтобы продолжить транзакцию. Не забудьте включить оператор COMMIT или другой оператор ROLLBACK после первого оператора ROLLBACK, чтобы завершить всю транзакцию.
Дополнительная информация. Для получения более полной информации о точках сохранения и примера их использования найдите "Save Transaction" (Сохранение транзакций) в Books Online и выберите "Save Transaction
(T-SQL)" в диалоговом окне