Перехват исключительных ситуаций и обработка ошибок 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:

logid	logdate	errno	severity	logproc	linenum	msgtext
1	2015-01-25 22:40:24.393	515	16	insert_data	5	Cannot insert …
2	2015-01-25 22:40:24.395	2627	14	insert_data	6	Violation of …

Если вы хотите попробовать 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

Механизм кэшированных изменений

КЭШем
называется специально выделенная
область оперативной памяти (буфер).

Кэшированные
изменения БД заключаются в том, что на
компьютере клиента в КЭШе создается
локальная копия данных, и все изменения
в данных выполняются в этой копии.
Сделанные изменения можно подтвердить,
перенеся их в основную БД, или отказаться
от них. Этот механизм напоминает
транзакции, но, в отличие от них,
существенно снижает нагрузку на сеть,
т.к. все изменения в основную БД передаются
одним пакетом. Однако для всех записей
локальной копии отсутствуют блокировки
на изменение их значений, в то время
как для основной БД, находящейся на
сервере, они могут быть в этот момент
установлены другим приложением.

Механизм
кэшированных изменений реализуется в
приложении через компоненты DataBase,
Table, Query (при доступе с помощью BDE), а также
через специально предназначенный для
этого компонент UpdateSQL. Компонент
UpdateSQL используется для подтверждения
кэшированных изменений и обеспечивает
модификацию наборов данных, доступных
только для чтения.

Для
наборов данных DataBase, Table, Query включением
режима кэшированных изменений управляет
свойство CachedUpdates, принимающее два
значения:

True –
активизирует режим;

False –
выключает его (по умолчанию).

После
включения режима кэширование производится
автоматически. Подтверждение кэшированных
изменений выполняют в два этапа:

• запись
  кэшированных изменений в основную БД;

• подтверждение
  или отмена сделанных изменений.

Метод
ApplyUpdates набора данных записывает в БД
изменения в кэшированных данных. Данные
пересылаются одним пакетом, что снижает
нагрузку на сеть. Однако в основной БД
в текущий момент могут быть блокированные
записи, поэтому потребуется обработка
исключений (удобно использовать
конструкцию TRY … EXCEPT).

Метод
CommitUpdates набора данных подтверждает
изменение.

Метод
CancelUpdates отменяет их, возвращая БД в
исходное состояние.

Перехват исключительных ситуаций и обработка ошибок

Структурная
схема терминов

Понятие исключительных ситуаций

Исключительная
ситуация – это динамическая ошибка,
представляющая собой нарушение условий
выполнения программы, вызывающее
прерывание или полное прекращение ее
работы.

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

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

В Delphi
механизмы обработки ошибок инкапсулированы
в класс Exception, описываемый в модуле
SysUtils. Все классы исключений являются
его потомками.

Возникающие
при выполнении программы динамические
ошибки автоматически преобразовываются
средствами Delphi в соответствующие
объекты-исключения. Объект-исключение
содержит информацию о типе ошибки и
при возникновении исключения заставляет
программу или ее поток (составляющую
процесса) временно приостановиться.
После обработки исключения
объекты-исключения автоматически
удаляются.

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

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

EDatabaseError
– предназначен для обработки ошибок
при работе с набором данных (БД – TTable,
TQuery), имеет двух потомков:

• EDBEngineError
  – ошибка BDE (для локальных и файл-серверных
  БД);

• EDBClient
  – ошибка в приложении клиента (для
  клиент-серверных БД).

EDBEditError
– значение, введенное в поле, не
соответствует типу данных поля или
вводимые в поле данные несовместимы с
маской ввода, заданной в свойстве
EditMask.

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

Класс
TDBError содержит информацию об исключительной
ситуации в свойствах:

• Message
  типа String – текст сообщения, характеризующий
  возникшую ошибку;

• ErrorCode
  типа DBIResult – код
  ошибки;

• Category
  типа Byte – категория исключительной
  ситуации;

• SubCode
  типа Byte – группа (подкод) исключительной
  ситуации;

• NativeError
  типа Longint – код ошибки, возвращаемой
  сервером. Если код равен 0, то ошибка
  произошла не на сервере.

Соседние файлы в предмете [НЕСОРТИРОВАННОЕ]

• #
• #
• #
• #
• #
• #
• #
• #
• #
• #
• #

6.6. Обработка исключительных ситуаций При выполнении программных объектов пользователь должен иметь возможность оперативно реагировать на возникающие отклонения от нормального процесса их выполнения. Для решения этой задачи разработчики SQL Anywhere применили механизм обработки исключительных ситуаций. Данный механизм представляет собой прием, обеспечивающий перехват и обработку ошибок и предупреждений. Исключительные ситуации в SQL Anywhere возникают при выполнении SQL-операторов. Исключительные ситуации представляются в виде кодов возврата, которые формируются после завершения выполнения операторов. Коды возврата, как и в большинстве баз данных SQL, одновременно записываются в системные переменные SQLSTATE и SQLCODE. В этих переменных возникновение исключительных ситуаций кодируются по разному. Так в переменной SQLCODE они представляются в виде десятичных чисел. При этом отрицательные значения соответствуют ошибкам, положительные — предупреждениям, а ноль — успешному завершению оператора. Кодирование исключительных ситуаций в переменной SQLSTATE соответствует стандарту SQL/92. При этом коды возврата представляются в виде строк из пяти символов. В переменной SQLSTATE первые два символа составляют код класса ошибок, следующие три символа — код внутри класса. Код «00000» соответствует успешному выполнению оператора. Перечень кодов для переменных SQLSTATE и SQLCODE вы можете найти в документации SQL Anywhere или ее справочной системе. В зависимости от значений кодов возврата может быть принято решение о повторении оператора, прерывания функционирования приложения и т.д. ПРИМЕЧАНИЕ. После считывания значения одной из переменных SQLSTATE или SQLCODE, обе они приводятся в состояние отсутствия ошибок. Рассмотрим сначала, что происходит с работой программных объектов при возникновении исключительных ситуаций в виде ошибок, когда обработка этих ошибок не предусмотрена. Обработка ошибок и поведение программных объектов при возникновении предупреждений рассмотрена п.п. 6.6.2, 6.6.3. 6.6.1. Работа программных объектов при отсутствии реакции на ошибки Если при функционировании программного объекта возникает исключительная ситуаций в виде ошибки и их обработка нем не определена, то происходит следующее. Программный объект сразу прекращает свое функционирование и происходит передача управления вызвавшему его программному объекту. Туда же через переменные SQLSTATE и SQLCODE передается код возврата ошибки. Если и там не определена обработка возникшей ошибки, то происходит передача управления и кода возврата ошибки еще выше по иерархии вызовов. Такая передача происходит до тех пор пока не встретится программный объект, в котором предусмотрена обработка возникшей ошибки, или пока будет достигнут самый верхний уровень иерархии вызовов. В последнем случае произойдет аварийное завершение функционирования приложения. Допустим, что ни в одном из программных объектов A, B и C не предусмотрена обработка ошибок. Допустим также, что для них имеется следующая динамика вызовов: A -> B —>C. Тогда при возникновении ошибки в C произойдет последовательное аварийное завершение функционирования программных объектов C,B и A , т.е. всего приложения в целом. Ниже приводится пример, демонстрирующий поведение программных объектов при отсутствии обработки ошибок. В данном примере процедура Proc_First вызывает Proc_Second , в которой возникает ошибка. CREATE PROCEDURE Proc_First() BEGIN MESSAGE '===Работает Proc_First .'; CALL Proc_Second(); MESSAGE '=== SQLSTATE= ', SQLSTATE,' в Proc_First.' END //___конец___процедуры CREATE PROCEDURE Proc_Second() BEGIN /* Объявление исключительной ситуации colum_not_found -ошибка "поле не найдено" */ DECLARE colum_not_found EXCEPTION FOR SQLSTATE '52003'; MESSAGE '***Работает Proc_Second.'; /* Искусственно генерируем возникновение исключительной ситуации colum_not_found */ SIGNAL colum_not_found; MESSAGE '******* SQLSTATE= ', SQLSTATE,' в Proc_Second .' ; END //___конец___процедуры Сделаем некоторые комментарии к процедурам Proc_Firsrt и Proc_Second. Оператор MESSAGE осуществляет выдачу в окно локального сервера (см. рис. 2) или окно Messages удаленного сервера (см. рис. 4) строку, формируемую из параметров оператора. Оператор DECLARE..EXCEPTION объявляет псевдоним для исключительной ситуации colum_not_found. Эта исключительная ситуация в процедуре Proc_Second вызывается искусственно. Для этой цели используется оператор SIGNAL. Для выполнения данного примера применим следующий оператор: //Вызов процедуры CALL Proc_First(). Тогда в окне сервера получим следующие сообщения: ===Работает Proc_First ***Работает Proc_Second Кроме того, на клиентской ЭВМ пользователю выдается сообщение colum not found. В процедуре Proc_Second после выполнения оператора SIGNAL эмулируется исключительная ситуация. В связи с этим второе сообщение о значении переменной SQLSTATE из данной процедуры формироваться не будет. Дело в том, в что в этой процедуре не определена обработка ошибки colum not found. Поэтому после ее возникновения процедура сразу прекращает свое функционирование. Следовательно все операторы, следующие за оператором SIGNAL , в том числе и второй оператор MESSAGE, выполняться не будут. После аварийного завершения процедуры Proc_Second управление передается процедуре Proc_First c выдачей кода возврата о ошибке colum_not_found. В этой процедуре также не предусмотрена обработка данной ошибки. По этой причине процедура Proc_First тоже аварийно завершает свое функционирование. При этом на экран клиентской ЭВМ, с которой была запущена процедура Proc_First, будет выдано сообщение о возникшей ошибке colum not found. ПРИМЕЧАНИЕ Определить оператор, при выполнении которого возникла ошибка, позволяет вызов в утилите ISQL функции Traceback(*). 6.6.2. Обработка ошибок в программных объектах Рассмотрим как происходит обработка ошибок в программных объектах. Их обработка производится механизмом обработки исключительных ситуаций. Использование этого механизма состоит: в определении псевдонимов для исключительных ситуаций соответствующие ожидаемым ошибкам, посредством оператора DECLARE..EXCEPTION; в определении реакции на описанные исключительные ситуации в программном блоке, начинающемся служебным словом EXCEPTION. Покажем как применяются исключительные ситуации для обработки ошибок на примере процедур Proc_First_Ex и Proc_Second|_Ex. Эти процедуры представляют собой незначительные модификации процедур Proc_Firsrt и Proc_Second. CREATE PROCEDURE Proc_First_Ex() BEGIN MESSAGE '===Работает Proc_First_Ex'; CALL Proc_Second_Ex(); MESSAGE '=== SQLSTATE= ', SQLSTATE, ' в Proc_First_Ex.' END //___конец___процедуры CREATE PROCEDURE Proc_Second_Ex() BEGIN /* Объявление исключительной ситуации colum_not_found - ошибка "поле не найдено)" */ DECLARE colum_not_found EXCEPTION FOR SQLSTATE '52003'; MESSAGE '***Работает Proc_Second_Ex .'; //искусственно генерируем возникновение // исключительной ситуации colum_not_found SIGNAL colum_not_found; MESSAGE 'Выполнен оператор SIGNAL'; EXCEPTION WHEN colum_not_found THEN MESSAGE '***Сolum_not_found', ' SQLSTATE= ', SQLSTATE; MESSAGE '***' SQLSTATE= ', SQLSTATE, ' в Proc_Second_Ex'; WHEN OTHERS THEN RESIGNAL; END //___конец___процедуры После выполнения данного примера посредством оператора //Вызов процедуры CALL Proc_First_Ex() в окне сообщений сервера появится следующая информация: ===Работает Proc_First_Ex . ***Работает Proc_Second_Ex . ***Colum_not_found SQLSTATE= 52003 *** SQLSTATE= 00000 в Proc_Second_Ex === SQLSTATE= 00000 в Proc_First_Ex. При использовании исключительных ситуации для обработки ошибок после возникновении последних происходит переход к блоку EXCEPTION. При этом операторы, следующие за ошибочным оператором не выполняются. В блоке EXCEPTION выполняется реакция на возникшую ошибку, если такая реакция определена. Если ошибка не определена, то в пределах этого блока осуществляется переход на метку OTHERS. В рассматриваемом примере в последнем случае должен выполняться оператор RESIGNAL. Он позволяет аварийно завершить процедуру, несмотря на наличие операторов обработки ошибок. Кроме того, данный оператор отменяет все действия совершенные в процедуре. После выполнения соответствующих операторов в блоке EXCEPTION происходит завершение процедуры. При этот, если не используется оператор RESIGNAL, то в вызвавший программный объект передается код возврата об отсутствии ошибки (SQLSTATE=’00000′, SQLCODE=0). Использование механизма исключительных ситуаций является удобным средством контроля за возникающими ошибками. При его применении от пользователя не требуется проверять коды возврата после каждого SQL-оператора. Эту функции берет на себя СУБД. Код программного объекта становится короче, нагляднее и, как правило, в нем уменьшается вероятность наличия ошибок. 6.6.3. Обработка предупреждений в программных объектах Обработка предупреждений отличается от обработки ошибок. При формировании предупреждения в переменные SQLSTATE и SQLCODE также записывается код возврата. Но прерывания вычислительного процесса при этом не происходит и программный объект продолжает свое функционирование. Если возникает необходимость отреагировать на предупреждение, то достаточно проверить содержимое переменной SQLSTATE или SQLCODE. Рассмотрим функционирование программных объектов в условиях формирования предупреждений на примере процедур Proc_Firsrt_W и Proc_Second_W. Они разработаны на базе процедур Proc_Firsrt и Proc_Second. CREATE PROCEDURE Proc_First_W() BEGIN MESSAGE '===Работает Proc_First_w.'; CALL Proc_Second_W(); MESSAGE '=== SQLSTATE= ', SQLSTATE,' в Proc_First_W.' END //___конец___процедуры CREATE PROCEDURE Proc_Second_W() BEGIN /* Объявление исключительной ситуации row_not_found - предупреждение запись не найдена */ DECLARE row_not_found EXCEPTION FOR SQLSTATE '02000'; MESSAGE '***Работает Proc_Second_W .'; /* искусственно генерируем исключи- тельную ситуацию row_not_found */ SIGNAL row_not_found; IF sqlstate='02000' THEN MESSAGE '***Warning row_not_found', ' SQLSTATE= ', SQLSTATE; END IF ; MESSAGE '***SQLSTATE= ', SQLSTATE,' в Proc_Second_W .' ; END //___конец___процедуры Запустив пример на исполнение оператором //Вызов процедуры CALL Proc_First_W() в окне сообщений сервера появится следующая информация: ===Работает Proc_First_w . ***Работает Proc_Second_W . ***row_not_found SQLSTATE= 02000 ***SQLSTATE= 00000 в Proc_Second_W . === SQLSTATE= 00000 в Proc_First_W. Данный пример показывает, что управлять обработкой предупреждений казалось бы значительно проще, чем обработкой ошибок. Однако использование проверок после каждого SQL-оператора вызывает нагромождение вспомогательных конструкций в тексте программного объекта. Это никак не способствует снижению программных ошибок. Назад | Содержание | Вперед

• Другие части статьи:
• 1
• 2
• 3
• вперед »

Конечно сейчас уже сложно найти хоть одного человека работающего с SQL сервером достаточно плотно и при этом не знающего (или хотя бы не слышавшего) «кодовую фразу»: «блок TRY/CATCH». Ведь с момента первого появления означенного блока как такового в синтаксисе языка T-SQL прошло, круглым счетом, лет семь — срок более чем достаточный, что бы о «новинке» узнал любой желающий изучить хоть что-то новое. При этом нельзя сказать, что в вопросе правильного перехвата ошибок для кода написанного на языке T-SQL поставлена последняя точка, отнюдь. Во-первых самому блоку как синтаксическому элементу есть куда расти, и вышедший на днях сервер 2012-й версии тому подтверждение. Во-вторых, даже реализация того же блока в серверах версий 2005-2008-R2 все еще остается непонятой до конца многими SQL-специалистами. Из опыта проведений курсов (как официальных, от Microsoft, так и курсов по программе заказчика), из общений на форумах, из встреч в обстановке формальной и не очень, автор данного блога, как ему кажется, обнаружил «корень» такого непонимания. Дело в том, что 95% литературы освещающих данную тему (BOL так же входит в число указанных процентов, и даже лучше сказать возглавляет их) впадают в одну и ту же… ошибку? Нет, это, пожалуй, слишком жесткая оценка. В один и тот же неверный «посыл» они впадают — вот так вернее будет. Так вот этот самый ошибочный посыл сводится к следующему: раз общая идея и концепция перехвата исключений (а наш блок именно этим и занимается) появилась в языках высокого уровня пару десятков лет тому назад, то тратить слова и печатное пространство на описание этой самой идеи/концепции резона нет — все и так все знают. Просто переходим к описанию сути происходящего внутри блока, и все дела.

Почему такой посыл кажется автору ошибочным? Потому что не все IT-профессионалы пишущие T-SQL код имели счастье (или несчастье, тут уж у каждого свой опыт и впечатления) попробовать реализовать свои алгоритмы на языках C++, C# и иже с ними. Разумеется, профессиональный программист на языке T-SQL знающий, до некоторой степени подробности или очень хорошо, тот же C# исключением не будет. Но и не станет исключением другой программист, пишущий всю жизнь на T-SQL, а C# знающий ну очень поверхностно, или не знающий его вовсе. Кроме того, для контекста нашей беседы, знание синтаксиса конкретного высокоуровневого языка не столь и значимо, гораздо важнее та самая «идея», а точнее ее полное понимание, а как раз с последним у большинства SQL-профессионалов — проблема.

В силу всего изложенного, автор решился взяться за эту уже порядком «заезженную» тему обработки ошибок, но построить подачу материала на несколько иной основе. «Что если», подумалось автору, «программист знает T-SQL и только его»? А тогда простая логика подсказывает, что объяснение блока TRY/CATCH нужно, для таких программистов, начинать с вещей куда как более фундаментальных, чем это делают иные источники информации. И уж потом, на этом фундаменте, излагать факты с которых традиционно принято начинать рассмотрение данного вопроса. Кроме того, с учетом того, что 100% материалов данного блога пишутся по методологии «основательно — масштабно — достоверно» автор не мог закончить данный труд простым перечислением тех самых фактов. Нами непременно будут так же рассмотрены:

• как происходит переключение потока исполнения между блоками TRY и CATCH;
• зачем, уже перехватив ошибку и обработав ее, мы можем захотеть вновь ее «поднять» или «бросить» (throw);
• как блок TRY/CATCH работает с ошибками разных уровней серьезности (severity);
• как обрабатываются ошибки периода исполнения и как — периода компиляции кода;
• как взаимодействуют вложенные блоки TRY/CATCH и как взаимодействуют блоки из разных программных модулей вызывающих друг друга;
• допустимо ли вкладывать новый блок TRY/CATCH в блок CATCH вышестоящего уровня;
• как блок TRY/CATCH влияет на работу транзакций и как они влияют на него;
• и, наконец, как же в общем, с позиций «большой IT-науки», правильно использовать этот инструмент — обработку и перехват ошибок.

Кроме того, в завершение материала вас ждет «контрольная работа» которая поможет вам провести самооценку усвоения излагаемых далее фактов и правил. Вопросов, как видите, немало (да еще и экзамен ), в пару страниц нам точно не уложиться, а поэтому — устраивайтесь поудобнее. И, как для любого другого материала данного блога, позаботьтесь что бы ваша любимая Sql Server Management Studio была от вас не дальше чем на расстоянии вытянутой руки, она вам потребуется уже буквально через несколько экранов текста.

Обработка исключений, базовая идея.

Итак, начнем мы, как договорились, с самого фундамента, с идеи и концепции исключений их обработки. Если абстрагироваться от любого конкретного языка программирования (и даже от T-SQL), то выяснится, что:

• при реализации любого программного алгоритма уровня сложности выше чем элементарный, на любом языке программирования, мы никак не можем на 100% гарантировать что программа, даже написанная идеально, даже абсолютно точно не содержащая «багов» в своем коде, при выполнении отработает успешно и произведет планируемые расчеты/действия/модификацию данных/и т.д. Есть факторы не подвластные ни нам, ни нашему коду. Если временно «выключить» нашу абстракцию от конкретных языков программирования, и представить что мы на T-SQL написали простейший INSERT, то можем ли мы гарантировать успешность исполнения этой единственной команды, даже если мы при указании значений для этого простейшего INSERT-а учли все ключи существующие в целевой таблице, все ограничения в ней же, аккуратно проверили типы значений на соответствие типам целевых колонок и сделали еще 50 подобных телодвижений? Не можем мы этого гарантировать! Почему? Да хотя бы потому, что INSERT безусловно требует известного числа байт (килобайт?) свободного пространства в журнале транзакций, а при его (свободного места) отсутствии и если у нас к тому же отключено автоприращение LDF-файла, мы будем иметь гарантированную ошибку 9002. А однозначно утверждать, что это самое место в журнале будет всегда, сейчас и в будущем, невозможно;
• если программист знает что он написал реально качественный код, что «багов» в нем точно нет, то он очень надеется, что в большинстве случаев выполнение программы будет все же успешным. Ровно как и мы с нашим INSERT-ом надеемся что в 99.99% случаев он просто, тихо и мирно вставит строку в таблицу и передаст управление следующему за ним оператору;
• тем не менее, пункт последний не отменяет пункт предпоследний, 99.99% <> 100%. И это еще без учета того фактора, что в более-менее сложном коде гарантия отсутствия «багов» будет заявлением, ну… несколько самоуверенным, назовем это так. Запросто можно не учесть все то разнообразие значений и их типов, что получает на вход наша хранимая процедура и из которых и формируется код все того же INSERT-а. И это только первый пункт из того длинного списка чего еще «можно не учесть»;
• итого, любой код во время своего исполнения подвержен риску «нарваться» на проблему, будь то проблема от автора кода вообще никак не зависящая (та же ошибка 9002), а равно и проблема им же самим порожденная, вроде попытки вставить дублирующее значение в колонку первичного ключа;
• проблемы предыдущего пункта принято называть термином более техническим — ошибки (errors), или даже термином еще более техническим — исключения (exceptions). Название термина как бы предполагает, что при возникновении такой ошибке на системе сданной в промышленную эксплуатацию программист разводит руками и как бы говорит нам: «ну, я надеялся, что раз оно две недели отработало, то и дальше все OK будет, а оно вон как… Исключение из правила, понимаешь!»;

Ну а если исключительных (сбойных, ошибочных, нестандартных… синонимов у этого определения много) ситуаций избежать нельзя, можно ли с этими ситуациями сделать хоть что-то толковое? Можно! Их можно обработать. Такая обработка говорит нашему коду: «брось тратить время на основной алгоритм — его уже не завершить корректно. Переключись на другой (под-)алгоритм в котором попробуй, во-первых, выяснить суть случившейся проблемы (т.е. была ли это ошибка 9002, или это все же был дубликат ключа, или еще что-то третье), а, во-вторых, попробуй изящно выйти из сложившейся ситуации». «Изящество» выхода из сложившейся ситуации полностью определяется снова программистом, который должен предвидеть и предвосхищать возможные проблемы с INSERT-ом и еще с миллионом вещей ему подобным. И вот тогда программист может:

• вывести очень любезное сообщение клиенту вместо по-спартански лаконичного «transaction log full» от сервера;
• вывести тоже самое любезное сообщение и дополнительно зафиксировать технические детали проблемы в некотором локальном (для сервера) txt-файле для дальнейшего анализа ситуации;
• или нечто совсем отдельное, например можно принудительно добавить свободного места в LDF-файл и повторить операцию вставки. Правда вот тут нам не обойтись без «многоэтажных», вложенных TRY/CATCH, которые вполне реальны и разговор о которых у нас впереди.

И так далее, и тому подобное — вас ограничивает только ваша фантазия. А самая главная прелесть обработки исключений в том, что она совершенно четко делит код на два ясно выраженных блока. Например, если переключится уже на конкретную реализацию обсуждаемого механизма в SQL Server версии 2005-й (и более поздних, разумеется), то такими «четко различимыми» блоками будут:

• блок TRY — блок основного алгоритма. Тут мы пишем наш INSERT и тут мы надеемся что все будет хорошо. Иными словами тут находится бизнес-логика нашего решения;
• блок CATCH — блок под-алгоритма. Тут мы реализуем те самые фантазии на тему «изящного» выхода из ситуации и уже ни на что хорошее не надеемся. Раз мы тут очутились — наши надежны точно не оправдались.

Такой подход к структуре программы гораздо яснее и четче того единственно возможного варианта, что был доступен до версии сервера 2000-го включительно: после каждого оператора имеющего потенциал ошибочного завершения оценивать этот самый оператор на результат его исполнения и если результат именно ошибочный — писать код «выхода из ситуации». Т.е. в целом T-SQL код был явно «спагетный», поскольку функция сообщающая нам была ли ошибка или нет (а таковой функцией являлась @@ERROR) требовала своего вызова сразу же (и только сразу!) после любого «опасного» оператора. И эти самые операторы (а они и есть бизнес-логика, основной алгоритм) щедро перемежались вставками вызова @@ERROR и набором инструкций по теме «если что пошло не так» (а это явно алгоритм вспомогательный):

А вот тоже самое в стилистике TRY/CATCH:

Полагаю никто не будет спорить — второе приятнее даже для глаза, не говоря уж про чтение чужих кодов с целью их поддержания и развития. Все очень четко: пока мы в блоке TRY — мы вообще ни о чем не беспокоимся, а просто реализуем только основную бизнес-логику нашего решения и надеемся на лучшее. Ну а в блоке CATCH мы реализуем только выход из ситуации, полностью игнорируя главный алгоритм — ведь он к этому моменту (имеется в виду к моменту «сваливания» в этот блок в исполняющейся программе) уже прекратил свою работу.

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

Итак, формально говоря, программа на любом языке (но и на T-SQL в том числе) использующая обработку исключений будет, или, как минимум, может, в ходе своего исполнения проходить через такие стадии и ветвления:

• вход в блок TRY и исполнение всех операторов данного блока последовательно, в порядке определенным программистом;
• если все операторы блока исполнены (т.е. достигнута метка END TRY) и никаких ошибок и проблем зафиксировано не было — блок CATCH полностью игнорируется (нам не нужен под-алгоритм выхода из «кризисных» ситуаций) и очередным оператором отправляющимся на исполнение будет первый оператор после метки END CATCH;
• если же очередной оператор блока TRY вызывает ту самую ошибку которую мы условились называть исключением, то:
  • исполнение блока TRY заканчивается этим самым проблемным оператором (мы не можем продолжать основной алгоритм, у нас проблемы);
  • очередным оператором отправляющимся на исполнение будет первый оператор после метки BEGIN CATCH;
  • затем исполняются все операторы этого блока (CATCH) в порядке определенным программистом, вплоть до метки END CATCH;
  • после чего возврат в блок TRY не происходит. Вообще идея обработки исключений как таковая предполагает два возможных продолжения в этой точке: подход «обработка с возвратом» вернет нас нас обратно, в блок TRY, в точку сбоя, откуда и продолжится исполнение главного алгоритма. Однако подавляющее большинство современных языков (и T-SQL в том числе) пропагандируют подход встречный, «обработка без возврата», который как раз предписывает остаток основного алгоритма игнорировать, а продолжить исполнение оператором следующим за обработчиком. Так что у нас после достижения метки END CATCH очередным оператором отправляющимся на исполнение будет оператор непосредственно за ней следующий в тексте T-SQL скрипта.

Обычно понимание описанной методики выбора пути исполнения программы проблем не вызывает, алгоритм ясен и четок, всегда можно предсказать что будет дальше. А вот тонким моментом, ускользающим от понимания SQL-программистов, является тот факт, что ошибка «прогнанная» через блок CATCH, по сути, перестает быть ошибкой! То есть, у нас была проблема в блоке TRY и нас перекинули в CATCH. Мы что-то сделали в последнем блоке, и теперь у нас все в порядке, можно продолжать далее. Да, у нас отработал не весь основной алгоритм, а лишь его часть, но программист, применяя блок CATCH, фактически заявляет «я знаю как из этого выпутаться без того, что бы заставлять клиента повторить всю операцию с самого начала». А если программист такого обходного пути не знает? Или знает как выпутаться если нет места в LDF-файле, но не знает как, если INSERT пытается задублировать значение ключа? Ну тогда он может:

• вообще не применять блоки TRY/CATCH. И позволить клиенту получить стандартное серверное сообщение об ошибке, причем о любой ошибке. И, как следствие, заставить клиента повторить операцию. Этот подход даже еще хуже, чем тот что был в сервере 2000-м и тот который интенсивно использовал функцию @@ERROR — тот хоть какую-то обработку ошибок предполагал…
• применить блоки TRY/CATCH и во втором из них извлечь информацию о данной конкретной ошибке, проанализировать ее, и если ошибка окажется «класса» «я знаю как» — реализовать это «как» в коде. А если ошибка окажется «класса» противоположного — повторно ее генерировать, тем самым честно сообщая о своей неготовности к полной ее обработке. Теоретические основы и правильный дизайн кода для этого варианта более подробно обсуждается в заключительной части статьи.

Сразу определимся, что проблему извлечения информации в блоке CATCH, т.е. пути узнавания что же конкретно привело к переходу из блока TRY в блок CATCH, мы в нашей статье рассматривать не будем. Во-первых, это одна из самых простых задач и совершенно прямолинейная — нужно просто в блоке CATCH «дернуть» соответствующую системную функцию. Во-вторых, статья в BOL Получение сведений об ошибках в языке Transact-SQL описывает общее назначение этих функций, а в конце ее есть набор ссылок на персональную страницу каждой из упомянутых функций. Мы, в наших практических экспериментах, из всего этого богатого набора будем использовать единственную функцию ERROR_MESSAGE, возвращающую текст сообщения об ошибке. Однако совершенно все функции из указанного набора должны быть в арсенале SQL-разработчика готовыми к применению. Тем более функций не так и много (всего шесть), а их вызов и интерпретация результатов ими возвращаемых просто элементарны. Не будем мы говорить и о вопросе «я знаю как из этого выпутаться» — этот момент не поддается обобщению, каждый отдельный код уникален и знать (либо не знать) обходной маневр может и должен только программист его создающий. Но зато мы подробно, и даже весьма, поговорим о третьем компоненте решения — повторной генерации («броске», throw) ошибки с целью информирования клиента что на этот раз случилось нечто серьезное и мы бессильны. Только разговор этот будет не прямо сейчас, а в следующем разделе — продолжайте чтение статьи.

Так вот, самое важное для нас на текущий момент, что если ошибка была проведена через блок CATCH и не была в нем повторно сгенерирована (или, выражаясь более технически, не была «брошена» (throw) повторно), то такая ошибка считается обработанной. Клиент спокойно продолжает свою работу с оператора следующим за меткой END CATCH. Повторю, что для изрядного числа специалистов создающих модули на языке T-SQL, такое «исчезновение» ошибки становится большой неожиданностью, однако все обстоит именно так: блок CATCH «подавляет» ошибку, не дает ей «прорваться» к клиенту. Ну и обратное тоже верно: если блок CATCH не использовать (а это автоматически означает отказ и от блока TRY), или использовать, но произвести в нем повторный бросок ошибки — клиент получит соответствующее уведомление. Мы, кстати, не обязаны сообщать последнему ошибку истинную, или сообщать все ее подробности. Вполне возможно использовать «подмену», когда в блоке CATCH мы видим, что истинной ошибкой приведшей к прерыванию работы является исключение A, но повторно генерируем не его, а исключение B. Мотивы для такой подмены могут быть самыми разными, начиная от желания предоставить клиенту больше информации, чем та что стандартно предоставляется сервером в исключении A, и заканчивая желанием ровно противоположным — скрыть подробности случившегося, например для затруднения потенциального взлома нашего решения. Технически, любое (правда тут, как обычно в SQL Server, тоже есть свои тонкости, разберем их далее; пока остановимся на термине «любое» как наиболее близком к истинному положению вещей) исключение «вброс» которого произведен в границах блока CATCH отменяет обсуждавшееся только что «подавление» и ведет к невозможности продолжения работы с точки зрения клиента.

Повторная генерации исключения (throw) и инструкция RAISERROR.

Итак, как неопровержимо доказал нам предшествующий раздел, нам совершенно необходимо уметь не только ошибки обрабатывать, но и, как ни парадоксально это прозвучит, создавать их. Первая причина такой парадоксальности уже была приведена — отмена нежелательного подавления случившегося исключения. Вторая причина может и не столь насущна, но не менее востребована — изучение и тесты блоков TRY/CATCH. Дело в том, что полностью понять работу разбираемого нами блока можно лишь одним путем — брать его и пробовать писать T-SQL код с его участием. То есть писать короткие скрипты, запускать их и анализировать результат. Собственно, это, наверно, вообще единственно действующая метода изучения любого синтаксического элемента любого языка программирования. И вот при написании таких тестовых скриптов оператор гарантирующий возникновение ошибки нам совершенно необходим — иначе как нам «свалиться» в блок CATCH? Разумеется, банальный SELECT 1/0 поможет нам в нашей «беде», но:

• иногда нам, что бы исключить все вопросы о ходе исполнения тестового скрипта, требуется ошибка сопровождающаяся нашим собственным текстовым сообщением, а не стандартизированным сообщением от сервера;
• что гораздо важнее, ошибки в SQL Server бывают множества уровней серьезности (severity level) и значение этого уровня категорически влияет на то, как именно такая ошибка будет обработана блоками TRY/CATCH. «Подгонять» же под каждый из возможных уровней оператор «реальный», вроде показанного чуть выше SELECT-а, занятие не очень практичное, нам нужен способ более удобный и универсальный.

И такое универсальное решение — есть! Инструкция RAISERROR дает нам требуемое и позволяет сделать изучение обработки ошибок в языке T-SQL делом значительно более простым и приятным.

В отличии от предполагаемого, синтаксис инструкции RAISERROR оказывается неожиданно весьма «развесист», хотя самых главных ее аргументов всего три:

• msg_str — текст сопровождающий ошибку;
• severity — тот самый уровень серьезности, о конкретных значениях которого речь впереди;
• state — состояние, целое число от 0 до 255. Если мы в нескольких точках своего кода «бросаем» совершенно идентичные ошибки, у которых совпадает и msg_str, и severity, то этот параметр позволит отделить одну такую точку кода от другой. Это, конечно, если таковое разделение нам вообще требуется. А необходимость в этом случается не часто. Например, во всех скриптах данной статьи автор указывает данному параметру значение 1.

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

По крайней мере такой подход рекомендован BOL. И в принципе показанный код вполне рабочий, только автор заменил бы переменную @ErrorState константой, той же единицей, к примеру. Случаев когда значение данного параметра имеет хоть какой-то смысл очень немного, так что константа почти никогда ничего не испортит. Если же вы уверены, что вам нужно именно состояние, то уж, по крайней мере, проверяйте значение того же параметра на то, что оно не меньше нуля и не больше 255-ти, перед тем как вызвать RAISERROR. А иначе клиент получит сообщение не об ошибке приведшей код в блок CATCH, а сообщение о неверных параметрах самой инструкции RAISERROR. Программисты-перфекционисты могут еще и значение параметра @ErrorSeverity проверять на вхождение в диапазон 0…25, но это замечание гораздо менее актуальное чем предыдущее. Дело в том, что если значение @ErrorSeverity будет меньше нуля, движок «засчитает» его за 0. А если больше 25-ти — то за 25. И клиент все-равно получит сообщение о нужной ошибке, а не сообщение об ошибочности синтаксиса. Но вот с @ErrorState я б подстраховался.

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

• Другие части статьи:
• 1
• 2
• 3
• вперед »