Sql откат транзакции при ошибке

Привет, Хабр! Представляю вашему вниманию перевод статьи «Error and Transaction Handling in SQL Server. Part One – Jumpstart Error Handling» автора Erland Sommarskog.

1. Введение

Эта статья – первая в серии из трёх статей, посвященных обработке ошибок и транзакций в SQL Server. Её цель – дать вам быстрый старт в теме обработки ошибок, показав базовый пример, который подходит для большей части вашего кода. Эта часть написана в расчете на неопытного читателя, и по этой причине я намеренно умалчиваю о многих деталях. В данный момент задача состоит в том, чтобы рассказать как без упора на почему. Если вы принимаете мои слова на веру, вы можете прочесть только эту часть и отложить остальные две для дальнейших этапов в вашей карьере.

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

Все статьи описывают обработку ошибок и транзакций в SQL Server для версии 2005 и более поздних версий.

1.1 Зачем нужна обработка ошибок?

Почему мы обрабатываем ошибки в нашем коде? На это есть много причин. Например, на формах в приложении мы проверяем введенные данные и информируем пользователей о допущенных при вводе ошибках. Ошибки пользователя – это предвиденные ошибки. Но нам также нужно обрабатывать непредвиденные ошибки. То есть, ошибки могут возникнуть из-за того, что мы что-то упустили при написании кода. Простой подход – это прервать выполнение или хотя бы вернуться на этап, в котором мы имеем полный контроль над происходящим. Недостаточно будет просто подчеркнуть, что совершенно непозволительно игнорировать непредвиденные ошибки. Это недостаток, который может вызвать губительные последствия: например, стать причиной того, что приложение будет предоставлять некорректную информацию пользователю или, что еще хуже, сохранять некорректные данные в базе. Также важно сообщать о возникновении ошибки с той целью, чтобы пользователь не думал о том, что операция прошла успешно, в то время как ваш код на самом деле ничего не выполнил.

Мы часто хотим, чтобы в базе данных изменения были атомарными. Например, задача по переводу денег с одного счета на другой. С этой целью мы должны изменить две записи в таблице CashHoldings и добавить две записи в таблицу Transactions. Абсолютно недопустимо, чтобы ошибки или сбой привели к тому, что деньги будут переведены на счет получателя, а со счета отправителя они не будут списаны. По этой причине обработка ошибок также касается и обработки транзакций. В приведенном примере нам нужно обернуть операцию в BEGIN TRANSACTION и COMMIT TRANSACTION, но не только это: в случае ошибки мы должны убедиться, что транзакция откачена.

2. Основные команды

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

2.1 TRY-CATCH

Основным механизмом обработки ошибок является конструкция TRY-CATCH, очень напоминающая подобные конструкции в других языках. Структура такова:

BEGIN TRY
   <обычный код>
END TRY
BEGIN CATCH
   <обработка ошибок>
END CATCH

Если какая-либо ошибка появится в <обычный код>, выполнение будет переведено в блок CATCH, и будет выполнен код обработки ошибок.

Как правило, в CATCH откатывают любую открытую транзакцию и повторно вызывают ошибку. Таким образом, вызывающая клиентская программа понимает, что что-то пошло не так. Повторный вызов ошибки мы обсудим позже в этой статье.

Вот очень быстрый пример:

BEGIN TRY
   DECLARE @x int
   SELECT @x = 1/0
   PRINT 'Not reached'
END TRY
BEGIN CATCH 
   PRINT 'This is the error: ' + error_message()
END CATCH

Результат выполнения: This is the error: Divide by zero error encountered.

Мы вернемся к функции error_message() позднее. Стоит отметить, что использование PRINT в обработчике CATCH приводится только в рамках экспериментов и не следует делать так в коде реального приложения.

Если <обычный код> вызывает хранимую процедуру или запускает триггеры, то любая ошибка, которая в них возникнет, передаст выполнение в блок CATCH. Если более точно, то, когда возникает ошибка, SQL Server раскручивает стек до тех пор, пока не найдёт обработчик CATCH. И если такого обработчика нет, SQL Server отправляет сообщение об ошибке напрямую клиенту.

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

CREATE PROCEDURE inner_sp AS
   BEGIN TRY
      PRINT 'This prints'
      SELECT * FROM NoSuchTable
      PRINT 'This does not print'
   END TRY
   BEGIN CATCH
      PRINT 'And nor does this print'
   END CATCH
go
EXEC inner_sp

Выходные данные:

This prints
Msg 208, Level 16, State 1, Procedure inner_sp, Line 4
Invalid object name 'NoSuchTable'

Как можно видеть, блок TRY присутствует, но при возникновении ошибки выполнение не передается блоку CATCH, как это ожидалось. Это применимо ко всем ошибкам компиляции, таким как пропуск колонок, некорректные псевдонимы и тому подобное, которые возникают во время выполнения. (Ошибки компиляции могут возникнуть в SQL Server во время выполнения из-за отложенного разрешения имен – особенность, благодаря которой SQL Server позволяет создать процедуру, которая обращается к несуществующим таблицам.)

Эти ошибки не являются полностью неуловимыми; вы не можете поймать их в области, в которой они возникают, но вы можете поймать их во внешней области. Добавим такой код к предыдущему примеру:

CREATE PROCEDURE outer_sp AS
   BEGIN TRY
      EXEC inner_sp
   END TRY
   BEGIN CATCH
      PRINT 'The error message is: ' + error_message()
   END CATCH
go
EXEC outer_sp

Теперь мы получим на выходе это:

This prints
The error message is: Invalid object name 'NoSuchTable'.

На этот раз ошибка была перехвачена, потому что сработал внешний обработчик CATCH.

2.2 SET XACT_ABORT ON

В начало ваших хранимых процедур следует всегда добавлять это выражение:

SET XACT_ABORT, NOCOUNT ON

Оно активирует два параметра сессии, которые выключены по умолчанию в целях совместимости с предыдущими версиями, но опыт доказывает, что лучший подход – это иметь эти параметры всегда включенными. Поведение SQL Server по умолчанию в той ситуации, когда не используется TRY-CATCH, заключается в том, что некоторые ошибки прерывают выполнение и откатывают любые открытые транзакции, в то время как с другими ошибками выполнение последующих инструкций продолжается. Когда вы включаете XACT_ABORT ON, почти все ошибки начинают вызывать одинаковый эффект: любая открытая транзакция откатывается, и выполнение кода прерывается. Есть несколько исключений, среди которых наиболее заметным является выражение RAISERROR.

Параметр XACT_ABORT необходим для более надежной обработки ошибок и транзакций. В частности, при настройках по умолчанию есть несколько ситуаций, когда выполнение может быть прервано без какого-либо отката транзакции, даже если у вас есть TRY-CATCH. Мы видели такой пример в предыдущем разделе, где мы выяснили, что TRY-CATCH не перехватывает ошибки компиляции, возникшие в той же области. Открытая транзакция, которая не была откачена из-за ошибки, может вызвать серьезные проблемы, если приложение работает дальше без завершения транзакции или ее отката.

Для надежной обработки ошибок в SQL Server вам необходимы как TRY-CATCH, так и SET XACT_ABORT ON. Среди них инструкция SET XACT_ABORT ON наиболее важна. Если для кода на промышленной среде только на нее полагаться не стоит, то для быстрых и простых решений она вполне подходит.

Параметр NOCOUNT не имеет к обработке ошибок никакого отношения, но включение его в код является хорошей практикой. NOCOUNT подавляет сообщения вида (1 row(s) affected), которые вы можете видеть в панели Message в SQL Server Management Studio. В то время как эти сообщения могут быть полезны при работе c SSMS, они могут негативно повлиять на производительность в приложении, так как увеличивают сетевой трафик. Сообщение о количестве строк также может привести к ошибке в плохо написанных клиентских приложениях, которые могут подумать, что это данные, которые вернул запрос.

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

SET NOCOUNT ON
SET XACT_ABORT ON

Между ними нет никакого отличия. Я предпочитаю версию с SET и запятой, т.к. это снижает уровень шума в коде. Поскольку эти выражения должны появляться во всех ваших хранимых процедурах, они должны занимать как можно меньше места.

3. Основной пример обработки ошибок

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

Для примера я буду использовать эту простую таблицу.

CREATE TABLE sometable(a int NOT NULL,
                       b int NOT NULL,
                       CONSTRAINT pk_sometable PRIMARY KEY(a, b))

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

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

Первая строка в процедуре включает XACT_ABORT и NOCOUNT в одном выражении, как я показывал выше. Эта строка – единственная перед BEGIN TRY. Все остальное в процедуре должно располагаться после BEGIN TRY: объявление переменных, создание временных таблиц, табличных переменных, всё. Даже если у вас есть другие SET-команды в процедуре (хотя причины для этого встречаются редко), они должны идти после BEGIN TRY.

Причина, по которой я предпочитаю указывать SET XACT_ABORT и NOCOUNT перед BEGIN TRY, заключается в том, что я рассматриваю это как одну строку шума: она всегда должна быть там, но я не хочу, чтобы это мешало взгляду. Конечно же, это дело вкуса, и если вы предпочитаете ставить SET-команды после BEGIN TRY, ничего страшного. Важно то, что вам не следует ставить что-либо другое перед BEGIN TRY.

Часть между BEGIN TRY и END TRY является основной составляющей процедуры. Поскольку я хотел использовать транзакцию, определенную пользователем, я ввел довольно надуманное бизнес-правило, в котором говорится, что если вы вставляете пару, то обратная пара также должна быть вставлена. Два выражения INSERT находятся внутри BEGIN и COMMIT TRANSACTION. Во многих случаях у вас будет много строк кода между BEGIN TRY и BEGIN TRANSACTION. Иногда у вас также будет код между COMMIT TRANSACTION и END TRY, хотя обычно это только финальный SELECT, возвращающий данные или присваивающий значения выходным параметрам. Если ваша процедура не выполняет каких-либо изменений или имеет только одно выражение INSERT/UPDATE/DELETE/MERGE, то обычно вам вообще не нужно явно указывать транзакцию.

В то время как блок TRY будет выглядеть по-разному от процедуры к процедуре, блок CATCH должен быть более или менее результатом копирования и вставки. То есть вы делаете что-то короткое и простое и затем используете повсюду, не особо задумываясь. Обработчик CATCH, приведенный выше, выполняет три действия:

1. Откатывает любые открытые транзакции.
2. Повторно вызывает ошибку.
3. Убеждается, что возвращаемое процедурой значение отлично от нуля.

Эти три действия должны всегда быть там. Мы можете возразить, что строка

IF @@trancount > 0 ROLLBACK TRANSACTION

не нужна, если нет явной транзакции в процедуре, но это абсолютно неверно. Возможно, вы вызываете хранимую процедуру, которая открывает транзакцию, но которая не может ее откатить из-за ограничений TRY-CATCH. Возможно, вы или кто-то другой добавите явную транзакцию через два года. Вспомните ли вы тогда о том, что нужно добавить строку с откатом? Не рассчитывайте на это. Я также слышу читателей, которые возражают, что если тот, кто вызывает процедуру, открыл транзакцию, мы не должны ее откатывать… Нет, мы должны, и если вы хотите знать почему, вам нужно прочитать вторую и третью части. Откат транзакции в обработчике CATCH – это категорический императив, у которого нет исключений.

Код повторной генерации ошибки включает такую строку:

DECLARE @msg nvarchar(2048) = error_message()

Встроенная функция error_message() возвращает текст возникшей ошибки. В следующей строке ошибка повторно вызывается с помощью выражения RAISERROR. Это не самый простой способ вызова ошибки, но он работает. Другие способы мы рассмотрим в следующей главе.

Замечание: синтаксис для присвоения начального значения переменной в DECLARE был внедрен в SQL Server 2008. Если у вас SQL Server 2005, вам нужно разбить строку на DECLARE и выражение SELECT.

Финальное выражение RETURN – это страховка. RAISERROR никогда не прерывает выполнение, поэтому выполнение следующего выражения будет продолжено. Пока все процедуры используют TRY-CATCH, а также весь клиентский код обрабатывает исключения, нет повода для беспокойства. Но ваша процедура может быть вызвана из старого кода, написанного до SQL Server 2005 и до внедрения TRY-CATCH. В те времена лучшее, что мы могли делать, это смотреть на возвращаемые значения. То, что вы возвращаете с помощью RETURN, не имеет особого значения, если это не нулевое значение (ноль обычно обозначает успешное завершение работы).

Последнее выражение в процедуре – это END CATCH. Никогда не следует помещать какой-либо код после END CATCH. Кто-нибудь, читающий процедуру, может не увидеть этот кусок кода.

После прочтения теории давайте попробуем тестовый пример:

EXEC insert_data 9, NULL

Результат выполнения:

Msg 50000, Level 16, State 1, Procedure insert_data, Line 12
Cannot insert the value NULL into column 'b', table 'tempdb.dbo.sometable'; column does not allow nulls. INSERT fails.

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

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

Результат работы:

Msg 50000, Level 16, State 1, Procedure outer_sp, Line 9
Violation of PRIMARY KEY constraint 'pk_sometable'. Cannot insert duplicate key in object 'dbo.sometable'. The duplicate key value is (8, 8).

Мы получили корректное сообщение об ошибке, но если вы посмотрите на заголовки этого сообщения и на предыдущее поближе, то можете заметить проблему:

Msg 50000, Level 16, State 1, Procedure insert_data, Line 12
Msg 50000, Level 16, State 1, Procedure outer_sp, Line 9

Сообщение об ошибке выводит информацию о расположении конечного выражения RAISERROR. В первом случае некорректен только номер строки. Во втором случае некорректно также имя процедуры. Для простых процедур, таких как наш тестовый пример, это не является большой проблемой. Но если у вас есть несколько уровней вложенных сложных процедур, то наличие сообщения об ошибке с отсутствием указания на место её возникновения сделает поиск и устранение ошибки намного более сложным делом. По этой причине желательно генерировать ошибку таким образом, чтобы можно было определить нахождение ошибочного фрагмента кода быстро, и это то, что мы рассмотрим в следующей главе.

4. Три способа генерации ошибки

4.1 Использование error_handler_sp

Мы рассмотрели функцию error_message(), которая возвращает текст сообщения об ошибке. Сообщение об ошибке состоит из нескольких компонентов, и существует своя функция error_xxx() для каждого из них. Мы можем использовать их для повторной генерации полного сообщения, которое содержит оригинальную информацию, хотя и в другом формате. Если делать это в каждом обработчике CATCH, это будет большой недостаток — дублирование кода. Вам не обязательно находиться в блоке CATCH для вызова error_message() и других подобных функций, и они вернут ту же самую информацию, если будут вызваны из хранимой процедуры, которую выполнит блок CATCH.

Позвольте представить вам error_handler_sp:

CREATE PROCEDURE error_handler_sp AS
 
   DECLARE @errmsg   nvarchar(2048),
           @severity tinyint,
           @state    tinyint,
           @errno    int,
           @proc     sysname,
           @lineno   int
           
   SELECT @errmsg = error_message(), @severity = error_severity(),
          @state  = error_state(), @errno = error_number(),
          @proc   = error_procedure(), @lineno = error_line()
       
   IF @errmsg NOT LIKE '***%'
   BEGIN
      SELECT @errmsg = '*** ' + coalesce(quotename(@proc), '<dynamic SQL>') + 
                       ', Line ' + ltrim(str(@lineno)) + '. Errno ' + 
                       ltrim(str(@errno)) + ': ' + @errmsg
   END
   RAISERROR('%s', @severity, @state, @errmsg)

Первое из того, что делает error_handler_sp – это сохраняет значение всех error_xxx() функций в локальные переменные. Я вернусь к выражению IF через секунду. Вместо него давайте посмотрим на выражение SELECT внутри IF:

SELECT @errmsg = '*** ' + coalesce(quotename(@proc), '<dynamic SQL>') + 
                 ', Line ' + ltrim(str(@lineno)) + '. Errno ' + 
                 ltrim(str(@errno)) + ': ' + @errmsg

Цель этого SELECT заключается в форматировании сообщения об ошибке, которое передается в RAISERROR. Оно включает в себя всю информацию из оригинального сообщения об ошибке, которое мы не можем вставить напрямую в RAISERROR. Мы должны обработать имя процедуры, которое может быть NULL для ошибок в обычных скриптах или в динамическом SQL. Поэтому используется функция COALESCE. (Если вы не понимаете форму выражения RAISERROR, я рассказываю о нем более детально во второй части.)

Отформатированное сообщение об ошибке начинается с трех звездочек. Этим достигаются две цели: 1) Мы можем сразу видеть, что это сообщение вызвано из обработчика CATCH. 2) Это дает возможность для error_handler_sp отфильтровать ошибки, которые уже были сгенерированы один или более раз, с помощью условия NOT LIKE ‘***%’ для того, чтобы избежать изменения сообщения во второй раз.

Вот как обработчик CATCH должен выглядеть, когда вы используете error_handler_sp:

BEGIN CATCH
   IF @@trancount > 0 ROLLBACK TRANSACTION
   EXEC error_handler_sp
   RETURN 55555
END CATCH

Давайте попробуем несколько тестовых сценариев.

EXEC insert_data 8, NULL
EXEC outer_sp 8, 8

Результат выполнения:

Msg 50000, Level 16, State 2, Procedure error_handler_sp, Line 20
*** [insert_data], Line 5. Errno 515: Cannot insert the value NULL into column 'b', table 'tempdb.dbo.sometable'; column does not allow nulls. INSERT fails.
Msg 50000, Level 14, State 1, Procedure error_handler_sp, Line 20
*** [insert_data], Line 6. Errno 2627: Violation of PRIMARY KEY constraint 'pk_sometable'. Cannot insert duplicate key in object 'dbo.sometable'. The duplicate key value is (8, 8).

Заголовки сообщений говорят о том, что ошибка возникла в процедуре error_handler_sp, но текст сообщений об ошибках дает нам настоящее местонахождение ошибки – как название процедуры, так и номер строки.

Я покажу еще два метода вызова ошибок. Однако error_handler_sp является моей главной рекомендацией для читателей, которые читают эту часть. Это — простой вариант, который работает на всех версиях SQL Server начиная с 2005. Существует только один недостаток: в некоторых случаях SQL Server генерирует два сообщения об ошибках, но функции error_xxx() возвращают только одну из них, и поэтому одно из сообщений теряется. Это может быть неудобно при работе с административными командами наподобие BACKUP\RESTORE, но проблема редко возникает в коде, предназначенном чисто для приложений.

4.2. Использование ;THROW

В SQL Server 2012 Microsoft представил выражение ;THROW для более легкой обработки ошибок. К сожалению, Microsoft сделал серьезную ошибку при проектировании этой команды и создал опасную ловушку.

С выражением ;THROW вам не нужно никаких хранимых процедур. Ваш обработчик CATCH становится таким же простым, как этот:

BEGIN CATCH
   IF @@trancount > 0 ROLLBACK TRANSACTION
   ;THROW
   RETURN 55555
END CATCH

Достоинство ;THROW в том, что сообщение об ошибке генерируется точно таким же, как и оригинальное сообщение. Если изначально было два сообщения об ошибках, оба сообщения воспроизводятся, что делает это выражение еще привлекательнее. Как и со всеми другими сообщениями об ошибках, ошибки, сгенерированные ;THROW, могут быть перехвачены внешним обработчиком CATCH и воспроизведены. Если обработчика CATCH нет, выполнение прерывается, поэтому оператор RETURN в данном случае оказывается не нужным. (Я все еще рекомендую оставлять его, на случай, если вы измените свое отношение к ;THROW позже).

Если у вас SQL Server 2012 или более поздняя версия, измените определение insert_data и outer_sp и попробуйте выполнить тесты еще раз. Результат в этот раз будет такой:

Msg 515, Level 16, State 2, Procedure insert_data, Line 5
Cannot insert the value NULL into column 'b', table 'tempdb.dbo.sometable'; column does not allow nulls. INSERT fails.
Msg 2627, Level 14, State 1, Procedure insert_data, Line 6
Violation of PRIMARY KEY constraint 'pk_sometable'. Cannot insert duplicate key in object 'dbo.sometable'. The duplicate key value is (8, 8).

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

В этом месте вы можете сказать себе: действительно ли Microsoft назвал команду ;THROW? Разве это не просто THROW? На самом деле, если вы посмотрите в Books Online, там не будет точки с запятой. Но точка с запятой должны быть. Официально они отделяют предыдущее выражение, но это опционально, и далеко не все используют точку с запятой в выражениях T-SQL. Более важно, что если вы пропустите точку с запятой перед THROW, то не будет никакой синтаксической ошибки. Но это повлияет на поведение при выполнении выражения, и это поведение будет непостижимым для непосвященных. При наличии активной транзакции вы получите сообщение об ошибке, которое будет полностью отличаться от оригинального. И еще хуже, что при отсутствии активной транзакции ошибка будет тихо выведена без обработки. Такая вещь, как пропуск точки с запятой, не должно иметь таких абсурдных последствий. Для уменьшения риска такого поведения, всегда думайте о команде как о ;THROW (с точкой с запятой).

Нельзя отрицать того, что ;THROW имеет свои преимущества, но точка с запятой не единственная ловушка этой команды. Если вы хотите использовать ее, я призываю вас прочитать по крайней мере вторую часть этой серии, где я раскрываю больше деталей о команде ;THROW. До этого момента, используйте error_handler_sp.

4.3. Использование SqlEventLog

Третий способ обработки ошибок – это использование SqlEventLog, который я описываю очень детально в третьей части. Здесь я лишь сделаю короткий обзор.

SqlEventLog предоставляет хранимую процедуру slog.catchhandler_sp, которая работает так же, как и error_handler_sp: она использует функции error_xxx() для сбора информации и выводит сообщение об ошибке, сохраняя всю информацию о ней. Вдобавок к этому, она логирует ошибку в таблицу splog.sqleventlog. В зависимости от типа приложения, которое у вас есть, эта таблица может быть очень ценным объектом.

Для использования SqlEventLog, ваш обработчик CATCH должен быть таким:

BEGIN CATCH
   IF @@trancount > 0 ROLLBACK TRANSACTION
   EXEC slog.catchhandler_sp @@procid
   RETURN 55555
END CATCH

@@procid возвращает идентификатор объекта текущей хранимой процедуры. Это то, что SqlEventLog использует для логирования информации в таблицу. Используя те же тестовые сценарии, получим результат их работы с использованием catchhandler_sp:

Msg 50000, Level 16, State 2, Procedure catchhandler_sp, Line 125
{515} Procedure insert_data, Line 5
Cannot insert the value NULL into column 'b', table 'tempdb.dbo.sometable'; column does not allow nulls. INSERT fails.
Msg 50000, Level 14, State 1, Procedure catchhandler_sp, Line 125
{2627} Procedure insert_data, Line 6
Violation of PRIMARY KEY constraint 'pk_sometable'. Cannot insert duplicate key in object 'dbo.sometable'. The duplicate key value is (8, 8).

Как вы видите, сообщение об ошибке отформатировано немного не так, как это делает error_handler_sp, но основная идея такая же. Вот образец того, что было записано в таблицу slog.sqleventlog:

Если вы хотите попробовать SqlEventLog, вы можете загрузить файл sqleventlog.zip. Инструкция по установке находится в третьей части, раздел Установка SqlEventLog.

5. Финальные замечания

Вы изучили основной образец для обработки ошибок и транзакций в хранимых процедурах. Он не идеален, но он должен работать в 90-95% вашего кода. Есть несколько ограничений, на которые стоит обратить внимание:

1. Как мы видели, ошибки компиляции не могут быть перехвачены в той же процедуре, в которой они возникли, а только во внешней процедуре.
2. Пример не работает с пользовательскими функциями, так как ни TRY-CATCH, ни RAISERROR нельзя в них использовать.
3. Когда хранимая процедура на Linked Server вызывает ошибку, эта ошибка может миновать обработчик в хранимой процедуре на локальном сервере и отправиться напрямую клиенту.
4. Когда процедура вызвана как INSERT-EXEC, вы получите неприятную ошибку, потому что ROLLBACK TRANSACTION не допускается в данном случае.
5. Как упомянуто выше, если вы используете error_handler_sp или SqlEventLog, мы потеряете одно сообщение, когда SQL Server выдаст два сообщения для одной ошибки. При использовании ;THROW такой проблемы нет.

Я рассказываю об этих ситуациях более подробно в других статьях этой серии.

Перед тем как закончить, я хочу кратко коснуться триггеров и клиентского кода.

Триггеры

Пример для обработки ошибок в триггерах не сильно отличается от того, что используется в хранимых процедурах, за исключением одной маленькой детали: вы не должны использовать выражение RETURN (потому что RETURN не допускается использовать в триггерах).

С триггерами важно понимать, что они являются частью команды, которая запустила триггер, и в триггере вы находитесь внутри транзакции, даже если не используете BEGIN TRANSACTION.
Иногда я вижу на форумах людей, которые спрашивают, могут ли они написать триггер, который не откатывает в случае падения запустившую его команду. Ответ таков: нет способа сделать это надежно, поэтому не стоит даже пытаться. Если в этом есть необходимость, по возможности не следует использовать триггер вообще, а найти другое решение. Во второй и третьей частях я рассматриваю обработку ошибок в триггерах более подробно.

Клиентский код

У вас должна быть обработка ошибок в коде клиента, если он имеет доступ к базе. То есть вы должны всегда предполагать, что при любом вызове что-то может пойти не так. Как именно внедрить обработку ошибок, зависит от конкретной среды.

Здесь я только обращу внимание на важную вещь: реакцией на ошибку, возвращенную SQL Server, должно быть завершение запроса во избежание открытых бесхозных транзакций:

IF @@trancount > 0 ROLLBACK TRANSACTION

Это также применимо к знаменитому сообщению Timeout expired (которое является не сообщением от SQL Server, а от API).

6. Конец первой части

Это конец первой из трех частей серии. Если вы хотели изучить вопрос обработки ошибок быстро, вы можете закончить чтение здесь. Если вы настроены идти дальше, вам следует прочитать вторую часть, где наше путешествие по запутанным джунглям обработки ошибок и транзакций в SQL Server начинается по-настоящему.

… и не забывайте добавлять эту строку в начало ваших хранимых процедур:

SET XACT_ABORT, NOCOUNT ON

Reading Time: 9 minutes

In SQL Server, there are plenty of instances where you might want to undo a statement that was just ran.

Maybe you wrote an UPDATE statement that modified too many rows, or maybe an INSERT statement wasn’t quite right.

In these instances, it is great when the work is performed within a transaction and can therefore be undone.

Do you need to know how the ROLLBACK statement works? Do you need to learn a thing or two about error handling? You’ve come to the right place!

In this tutorial, we’ll teach you everything you need to know about the ROLLBACK statement. We’ll learn a few tricks and tools we can use to help identity problems in a transaction that would trigger a ROLLBACK to occur, so that our data remains consistent.

If you need a rundown on what a transaction is in the first place, check out my introduction to transactions here:

SQL Server Transactions: An Introduction for Beginners

Once you get the hang of transactions, you should also make sure you understand these 6 rules about transactions.

Here are the topics we will cover in this tutorial:

1. How to issue a ROLLBACK in an explicit transaction
2. Understanding @@ROWCOUNT and @@ERROR
3. Can I ROLLBACK a transaction that was already committed?
4. Tips and tricks

Everything in this tutorial is also discussed in the following FREE Ebook:

FREE Ebook on SQL Server Transactions!

This FREE Ebook discusses everything you need to know about creating and using transactions within SQL Server. It will definitely be a great resource for you to keep and reference throughout your career as a data professional. Make sure you download it today!

Let’s roll it over:

1. How to issue a ROLLBACK in an explicit transaction

In the introduction to transactions, we learned how to write an explicit transaction that can be either committed or rolled back. We’ll touch on that topic here, too.

Let’s go ahead and create a table and populate it with data for us to do some ROLLBACK tests with:

CREATE TABLE Books
(
BookID INT PRIMARY KEY IDENTITY,
Title VARCHAR(50),
Author VARCHAR(20),
Pages SMALLINT
)

INSERT INTO Books(Title, Author, Pages)
VALUES 
('As a man thinketh','Allen',45),
('From poverty to power','Allen',56),
('Eat that frog','Tracy',108),
('The war of art','Pressfield',165)

Here’s the data in our table:

So far, so good.

Let’s think about how to wrap a simple UPDATE statement around an explicit transaction. Let’s say I messed up the Pages value for the book “From Poverty to Power“, which is BookID # 2. I meant to give it a Pages value of 156.

If we want to perform an UPDATE statement within a transaction, it’s very easy. First, we put the words BEGIN TRANSACTION before the UPDATE statement, like this:

BEGIN TRANSACTION
UPDATE Books SET Pages = 156 where BookID = 2

You could abbreviate the word “TRANSACTION” and just say “BEGIN TRAN” if you want.

If we wanted to go ahead and COMMIT this transaction, we would just put the word COMMIT after the UPDATE statement, like this:

BEGIN TRANSACTION
UPDATE Books SET Pages = 156 where BookID = 2
COMMIT

You could also be very explicit and say “COMMIT TRANSACTION“, or “COMMIT TRAN“, or even “COMMIT WORK” if you wanted.

But what if you wanted to ROLLBACK?

Instead of committing the transaction, we can undo the UPDATE statement performed in the transaction by using the ROLLBACK keyword, like this:

BEGIN TRANSACTION
UPDATE Books SET Pages = 156 where BookID = 2
ROLLBACK

Similarly, you could be explicit and say “ROLLBACK TRANSACTION“, or just “ROLLBACK TRAN“, or even “ROLLBACK WORK“.

Either way, the effect is the same. The work done inside the transaction will be undone.

Let’s test it out. We’ll run that transaction with the ROLLBACK, then run a quick SELECT statement to see that the data is unchanged:

We see the change was basically undone and we’re back to where we started.

Folks, that’s basically all there is to it. When it comes to an explicit transaction (I.E. a transaction you create using the BEGIN TRANSACTION statement), the way you would undo any work performed inside that transaction is by issuing a ROLLBACK statement after the work.

2. Understanding @@ROWCOUNT and @@ERROR

The example in that last section was a bit silly because we knew we wanted to undo the work.

What if we want to make sure a ROLLBACK occurs if some unknown or unexpected error occurs in our transaction?

This is known as error handling. We want to protect our data in the event that some error occurs that we weren’t aware of.

We can use the extremely handy @@ROWCOUNT or @@ERROR system functions to help us incorporate error handling within our transactions.

Understanding @@ROWCOUNT

The @@ROWCOUNT system function simply tells us the number of rows effected by the last statement.

This can be extremely useful for writing bulletproof transactions. For example, if we want to run an UPDATE statement, and we want to make sure only one row gets updated, we can use the @@ROWCOUNT function to make sure only one row was updated.

Here’s the code, then we’ll discuss it:

BEGIN TRANSACTION
UPDATE Books SET Pages = 156 WHERE BookID = 2
IF (@@ROWCOUNT = 1)
COMMIT
ELSE
ROLLBACK

Remember, @@ROWCOUNT get’s the number of rows affected by the previous statement. The previous statement in our example is, of course, the UPDATE statement.

We want and expect our UPDATE statement to update only one row. But before we issue a COMMIT, we want to make sure only one row was updated.

We use the handy IF…ELSE block to see how many rows were affected by the UPDATE statement. If in fact we only updated one row, we know all is well and we issue the COMMIT.

Otherwise, if @@ROWCOUNT returns a number larger than 1, we know something went wrong and therefore issue a ROLLBACK.

Here’s our code executing the UPDATE statement and the COMMIT successfully:

Let’s see if we can force the ROLLBACK to run. We’ll do another UPDATE statement, but this time leave off the WHERE clause (which you should NEVER do):

Evidently our UPDATE statement modified more than one row, meaning @@ROWCOUNT was greater than 1.  Because we have the error handling in place, the accidental work was successfully rolled back.

All is well.

Understanding @@ERROR

The @@ERROR system function is another great function to use for error handling. We can use this function to see if an error occurred during the last execution of the most recent SQL statement.

This function will return the error number of the error if one occurred. Otherwise, if no error occurred in the previous statement, it returns 0.

The @@ERROR function, combined with an IF…ELSE block, is a great way to roll back the transaction if any bad thing happened.

Let’s take a look at an example. Here’s an example of an UPDATE statement that has a deliberate error in place:

BEGIN TRANSACTION
UPDATE Books SET Pages = 200 WHERE BookID = 4/0
IF (@@ERROR = 0)
COMMIT
ELSE
ROLLBACK

The error is in the WHERE clause, where I’m trying to divide an integer by 0. This is a mathematic violation and will give us an error.

In the IF block, if the value of @@ERROR is 0, we know there was no error generated by the UPDATE statement and it’s safe to COMMIT the transaction.

However, we would enter the ELSE block if an error did occur. In that case, @@ERROR would hold a value greater than 0. In that case, we ROLLBACK the transaction because something went wrong.

Let’s test it out:

We see that the error occurs. Now let’s check the data to see if anything changed:

Nope, looks good. But the most important thing to check is that we’re not in an open transaction. We use the handy @@TRANCOUNT system function for that. This function tells us how many open transactions exist in the current connection. Let’s make sure it’s zero:

Nice.

3. Can I ROLLBACK a transaction that was already committed?

I have a notion that some of you are reading this tutorial in the hope you can learn how to undo something you didn’t mean to do in SQL Server.

Maybe you ran and UPDATE statement and forgot to add a WHERE clause, or maybe the WHERE clause existed but it pulled more rows than you expected.

Or worse, you ran a DELETE statement without specifying a WHERE clause. Yikes!

Ok, here’s how we can check if your work can be undone:

1. Is the connection still open? It would be open if you didn’t close the query window or close SQL Server completely. If you’re still in the same connection, we can proceed to step 2.
2. Check the value of @@TRANCOUNT by running the following query: SELECT @@TRANCOUNT

If @@TRANCOUNT is greater than 0. We’re still in an open transaction. You should be able to just execute a ROLLBACK statement. After running that, if you check @@TRANCOUNT again, it will be 0 and the changes made in error should be undone.

But if @@TRANCOUNT is already 0, I’m afraid to say the changes are already committed to the database.

But fear not. If your database team is worth their sand, they will have prepared for something like this. They ought to have a backup plan for scenarios where bad SQL was executed. They would need to put the database back in a state close to what it was before the bad SQL was executed.

Things happen. It will be okay!

4. Tips and tricks

Here is a list of tips and tricks you should know when working with the ROLLBACK command:

Tip # 1: ROLLBACK will set @@TRANCOUNT to zero, regardless of what it was before.

If you have 35 open transactions, then you issue a single ROLLBACK, guess what, all the work done in all those transactions was just undone.

This is different from COMMIT which will just reduce @@TRANCOUNT by 1 each time it’s ran. So if you wanted to commit all 35 transactions instead, you would need to run COMMIT 35 times.

(By the way, none of the data is actually committed to the database until @@TRANCOUNT is reduced from 1 to 0. Running COMMIT once when there are 35 open transaction literally does nothing except reduce @@TRANCOUNT to 34)

Learn more about this factoid and others in this tutorial:

SQL Server Transactions: Do you understand these 6 rules?

Tip # 2: Before running an UPDATE or DELETE statement, run it as a SELECT statement first

If I’m updating data in a particularly important database, a trick I like to do is simply write my statement as a SELECT statement first.

That SELECT statement will likely have a WHERE clause. I’ll run that SELECT statement and verify the returned rows are the rows I expect. These are, of course, rows I intend to update or delete.

If everything checks out, I’ll use that same WHERE clause in my UPDATE or DELETE statement. That way, I can be confident the rows getting updated or deleted are those same rows that were returned in my SELECT statement.

This tip doesn’t exactly deal with performing a ROLLBACK, but it does deal with writing accurate DML (Data Manipulation Language) statements that hopefully won’t need to be rolled back.

Next Steps:

Leave a comment if you found this tutorial helpful!

Everything discussed in this tutorial can be found in the following FREE Ebook:

FREE Ebook on SQL Server Transactions!

This FREE Ebook discusses everything you need to know about creating and using transactions within SQL Server. It will definitely be a great resource for you to keep and reference throughout your career as a data professional. Make sure you download it today!

If you missed the introduction tutorial on SQL Server transactions, check it out here!

We discuss everything you need to know about transactions, including using ROLLBACK to undo work performed within a transaction.

Also, there are many important factoids about transactions many people don’t know. If you want to be a transactional master, you should read this tutorial too:

SQL Server Transactions: Do you understand these 6 rules?

Thank you very much for reading!

Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!

If you have any questions, please leave a comment. Or better yet, send me an email!

В случае конструкции

BEGIN TRAN
UPDATE 1
UPDATE 2 --error
UPDATE 3
COMMIT TRAN

(при выполнении команд не в блоке TRY ... CATCH ...) если на втором UPDATE возникнет ошибка, исполнение команд может продолжиться и дойти до COMMIT.

Использование варианта

BEGIN TRAN
UPDATE 1
UPDATE 2
UPDATE 3
IF @@ERROR <> 0
    ROLLBACK TRAN
ELSE
    COMMIT TRAN

не будет правильным, т.к. глобальная переменная @@ERROR содержит номер ошибки для последней исполненной команды. Это означает следующее: если UPDATE 1 или UPDATE 2 завершится с обшибкой, а UPDATE 3 — без ошибки, то после UPDATE 3 значение переменной @@ERROR станет равно 0, что сделает ложным вывод об успешности всей транзакции.

Если нужно при ошибке делать откат, то тут могут быть два варианта.

Первый — это исполнение команд в блоке TRY ... CATCH ...

BEGIN TRY
    BEGIN TRAN
    UPDATE 1
    UPDATE 2 --error
    UPDATE 3
    COMMIT TRAN
END TRY
BEGIN CATCH
    ROLLBACK TRAN
END CATCH

В этом случае, при возникновении ошибки на 2м шаге, исполнение не продолжится, а по-возможности перейдёт в блок CATCH, где можно принудительно вызвать ROLLBACK.

Второй вариант — включение опции XACT_ABORT перед входом в транзакцию.

SET XACT_ABORT ON
BEGIN TRAN
UPDATE 1
UPDATE 2 --error
UPDATE 3
COMMIT TRAN

В этом случае при возникновении ошибок (определённого рода, не любых) на 2-м UPDATE выполнение команд будет прервано и произойдёт автоматический откат изменений. (Может быть даже не стоит считать этот вариант самостоятельным, на мой взгляд включение XACT_ABORT — это некое дополнительное средство; не припомню случая, когда бы я для отката транзакции пользовался этой опцией обособленно).

В некоторых случаях используется и то и другое.

Ниже чуть подробнее об автоматическом и управляемом откате.

Автоматический ROLLBACK

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

create table test (id int primary key);

Открываем соединение и в нём выполняем

begin tran;
insert into test (id) values (1);
select * from test;

закрываем соединение, не завершив транзакцию (ни COMMIT, ни ROLLBACK не сделали). SqlServer сделает откат такой транзакции, при разрыве соединения. Запросив затем данные из таблицы в новом соединении, мы увидим, что она пустая.

Также автоматический ROLLBACK может происходить при возникновении ошибок (таких как, например, нарушение PK, FK ограничений при вставке или удалении данных), если включена опция XACT_ABORT (по умолчанию OFF). Например:

set xact_abort on;
begin tran;
insert into test (id) values (2);
select * from test;
insert into test (id) values (2); --error: Violation of PK ...
select * from test;
commit tran;

в этом случае до второго select и до commit дело не дойдёт, и откат произойдёт автоматически. Теперь при выключенном xact_abort (то что по умолчанию):

set xact_abort off;
begin tran;
insert into test (id) values (3);
insert into test (id) values (3); -- error
insert into test (id) values (4);
commit tran;
select * from test;

Несмотря на ошибку дело дойдёт и до commit (соответственно отката не будет) и до select после него.

К сожалению опция set xact_abort on полезна далеко не всегда. В частности она не откатывает транзакцию при генерации пользовательских исключений (в том числе сгенерированных в DML-триггерах). Например:

set xact_abort on;
begin tran;
insert into test (id) values (5);
if not exists (select 1 from test where id = 0)
    raiserror('Bad data', 16, 1);
commit tran;
select * from test;

Несмотря на set xact_abort on и сгенерированное исключение дело дойдёт и до commit и до select после него. Поэтому полезнее может быть целенаправленный вызов rollback.

-- вернули опцию в состояние по-умолчанию, если она была оставлена в состоянии ON
set xact_abort off; 

Управляемый ROLLBACK

Часто применяется в catch блоке, при оборачивании транзакции в try ... catch ... конструкцию:

begin try
    begin tran;
    -- тут делаем что-то
    commit tran;
end try
begin catch
    rollback tran;
end catch

При xact_abort off (т.е. по умолчанию) ROLLBACK не происходит автоматически, если транзакция была открыта, но из-за ошибки не достигла COMMIT. В этом случае SqlServer позволяет программисту самому решить будет ли откат полезен при той или иной ошибке, или нет. Далее пара примеров, когда откат может быть полезен в catch и когда вреден.

Пример 1: Изменение данных в транзакции.

Пусть есть процедура, которая в транзакции делает вставку данных в две связанных таблицы:

create procedure dbo.SetUserInfo
(
    @uid uniqueidentifier = NULL,
    @info xml
)
as
begin try
    set nocount, xact_abort on;

    if @info is NULL or @info.exist('/User') = 0
    begin
        raiserror('No or bad data provided.', 16, 1);
        return;
    end;

    begin transaction;

    declare @inserted table (ID int not NULL);
    declare @id int;

    merge into dbo.Users t
    using(
        select
            @uid,
            @info.value('(/User/@FirstName)[1]', 'nvarchar(50)'),
            @info.value('(/User/@LastName)[1]', 'nvarchar(50)')
        ) s(UID, FirstName, LastName)
    on t.UID = s.UID
    when matched then
        update
        set t.FirstName = s.FirstName, t.LastName = s.LastName
    when not matched then
        insert (UID, FirstName, LastName)
        values (s.UID, s.FirstName, s.LastName)
    output inserted.ID into @inserted (ID)
        ;

    select @id = ID from @inserted;

    merge into dbo.UserContacts t
    using (
        select @id, ct.ID, x.c.value('@Value', 'nvarchar(400)')
        from @info.nodes('/User[1]/Contacts[1]/Contact') x(c)
            join dbo.UserContactTypes ct on ct.Type = x.c.value('@Type', 'nvarchar(400)')
    ) s (UserID, ContactTypeID, ContactInfo)
    on t.UserID = s.UserID and t.ContactTypeID = s.ContactTypeID
    when not matched by source then
        delete
    when matched then
        update
        set t.ContactInfo = s.ContactInfo
    when not matched then
        insert (UserID, ContactTypeID, ContactInfo)
        values (s.UserID, s.ContactTypeID, s.ContactInfo)
        ;

    commit transaction;

end try
begin catch
    declare
        @errMsg nvarchar(4000) = error_message(),
        @errLine int = error_line(),
        @procName sysname = quotename(object_schema_name(@@procid)) + '.' + quotename(object_name(@@procid))
        ;

    if @@trancount > 0
        rollback transaction;

    raiserror('%s in %s at %d', 16, 1, @errMsg, @procName, @errLine);
end catch
GO

Допустим теперь, что произошёл вызов процедуры и началась вставка данных. Предположим, что вставка в Users прошла успешно, а при вставке в UserContacts произошел конфликт с уникальным индексом (UserID, ContactTypeID) (из-за того, например, что в @info один и тот же <Contact Type="Phone" Value="0(000)000-00-00" /> затесался дважды).

Если логикой приложения продиктовано, что либо сущность вставляется целиком, либо вообще не вставляется — тогда в catch делается rollback (как в данном примере).

Но возможны ситуации, когда ошибки, возникшие в результате выполнения каких-то отдельных запросов, не являются серьёзным основанием для отката всех совершенных действий. Например, если у нас не две связанных таблицы, а импорт данных в несколько независимых таблиц, и мы не хотим откатывать ту часть данных, что была уже успешно внесена. Тогда в catch можно попытаться сделать commit (не любая ошибка сделает это возможным, о том как это сделать корректно — в следующем примере).

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

Пример 2: Чтение данных в транзакции.

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

Пусть есть процедура, которая в repeatable read или snapshot транзакции читает данные:

create procedure dbo.GetSalesData
(
    @dateFrom datetime,
    @dateTo datetime
)
as
begin try
    set nocount on;

    declare @userID int;
    select @userID = UserID from #Session;

    if @userID is NULL
    begin
        raiserror('Access denied.', 16, 1);
        return;
    end;

    create table #Orders (OrderID int not NULL);
    alter table #Orders add primary key (OrderID);

    set transaction isolation level snapshot;
    begin transaction;

    insert into #Orders (OrderID)
    select op.OrderID
    from dbo.OrderPermissions(@userID) op
        join dbo.Orders ord on ord.ID = op.OrderID
    where op.[Permissions] > 0
        and ord.[Date] >= @dateFrom and ord.[Date] < @dateTo

    -- some check based on #Order and other data
    if exists (select 1 from #Orders o join ... where ...)
    begin
        raiserror('Check fail.', 16, 1);
        return;
    end;

    select ...
    from dbo.Orders ord
        join #Orders o on o.OrderID = ord.ID

    select ...
    from dbo.Invoices inv
        join #Orders o on o.OrderID = inv.OrderID

    select ...
    from dbo.Shipment sh
        join #Orders o on o.OrderID = sh.OrderID

    commit transaction;

end try
begin catch
    declare
        @errMsg nvarchar(4000) = error_message(),
        @errLine int = error_line(),
        @procName sysname = quotename(object_schema_name(@@procid)) + '.' + quotename(object_name(@@procid))
        ;

    if xact_state() = 1
        commit transaction;
    else if xact_state() = -1
        rollback transaction;

    raiserror('%s in %s at %d', 16, 1, @errMsg, @procName, @errLine);
end catch
GO

В процедуре происходит следующее. Открывается транзакция. В ней заполняется фильтрующая таблица #Orders (чтобы потом дать пользователю только то, что ему разрешено видеть). Затем некоторая проверка на основании #Orders и других данных. Если проверка проходит, то данные отдаются, если нет — генерируется ошибка.

Предположим, что эта проверка не была успешной. Открыта транзакция и произошла ошибка raiserror('Check fail.', 16, 1), вследствие чего управление передаётся в catch. Должен ли в catch произойти rollback? Нет. Ведь мы только читаем данные и ничего не изменяем (кроме временной таблицы #Orders). Более того, таблица #Orders создана до входа в транзакцию, а заполнялась в транзакции. Вследствие чего, если бы мы стали делать rollback начал бы происходить откат вставленных в неё данных, а это дольше, чем commit и простое уничтожение #Orders при выходе из процедуры. Т.е. в данном случае в catch лучше попытаться сделать commit, возможность или невозможность которого определяется функцией XACT_STATE().

Откаты транзакций

Откаты могут происходить в двух формах: автоматический откат, выполняемый 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)» в диалоговом окне Topics Found.