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

Обновлено: 07.07.2024

Привет, Хабр! Представляю вашему вниманию перевод статьи «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, очень напоминающая подобные конструкции в других языках. Структура такова:

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

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

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

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

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

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

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

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

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

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

2.2 SET XACT_ABORT 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 наиболее важна. Если для кода на промышленной среде только на нее полагаться не стоит, то для быстрых и простых решений она вполне подходит.

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

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

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

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

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

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

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

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

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

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

Встроенная функция 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. Кто-нибудь, читающий процедуру, может не увидеть этот кусок кода.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Триггеры

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

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

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

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

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

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

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

Механизм транзакций используется для поддержания целостности БД. Транзакция – это набор операций с БД, который переводит БД из одного целостного состояния в другое. Чтобы транзакция была успешной, должны выполниться все операции, входящие в ее состав. В случае возникновения ошибки хотя бы одной из операций вся транзакция считается неуспешной, и результаты всех операций отменяются. Транзакция может быть явной и неявной как при работе с локальными, так и при работе с удаленными БД.

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

Неявная транзакция запускается и завершается автоматически при модификации набора данных. Утверждается методом Post, а отменяется Cancel для локальных БД. Для удаленных БД используется PassThrough SQL-запрос, его выполнение приводит к запуску неявной транзакции. Такой SQL-запрос по модификации данных выполняется с помощью метода ExecSQL компонента Query. Способ взаимодействия с сервером на уровне такой транзакции определяет параметр SQLPASSTHRU MODE псевдонима БД или драйвера (в нашем случае InterBase).

Явной транзакцией управляет программист. Для реализации механизма явных транзакций Delphi предоставляет специальные методы компонента DataBase:

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

Commit – подтверждает успешную транзакцию. После него все изменения вступают в силу.

Rollback – отменяет транзакцию и действия всех операций в ее рамках. Вызывается при возникновении исключения.

^ SET TRANSACTION;
COMMIT;
ROLLBACK.

[READ WRITE | READ ONLY]

Все операнды этой инструкции являются необязательными и позволяют управлять режимами транзакции:

READ WRITE – разрешены чтение и модификация записей (по умолчанию);
READ ONLY – разрешено только чтение записей.

WAIT – ожидание завершения другой транзакции (по умолчанию);
NO WAIT – прекращение данной транзакции.

SNAPSHOT – чтение данных в состоянии на момент начала транзакции (по умолчанию); изменения, сделанные другими транзакциями, в данной транзакции не видны;
SNAPSHOT TABLE STABILITY – предоставление транзакции исключительного доступа к таблицам; другие транзакции могут читать записи из таблиц;
READ COMMITED – чтение только подтвержденных изменений в записях; если изменения еще не подтверждены, то читается предыдущая версия записи.

PROTECTED READ – разрешено только чтение записей;
PROTECTED WRITE – для транзакций с уровнем изоляции SNAPSHOT или READ COMMITED разрешена модификация записей;
SHARED READ – разрешены чтение и модификация записей;
SHARED WRITE – разрешено чтение записей, а для транзакций с уровнем изоляции SNAPSHOT или READ COMMITED разрешена модификация записей.

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

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

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

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

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

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

запись кэшированных изменений в основную БД;
подтверждение или отмена сделанных изменений.

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

Метод CancelUpdates отменяет их, возвращая БД в исходное состояние.
^ ПЕРЕХВАТ ИСКЛЮЧИТЕЛЬНЫХ СИТУАЦИЙ И ОБРАБОТКА ОШИБОК

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

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

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

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

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

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

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

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

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

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

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

^ Глобальная обработка

Программист может выполнить более полную обработку, создав свой глобальный обработчик события OnException. Для этого удобно использовать компонент ApplicationEvents.

Procedure TForm1.ApplicationEvents1Exception (Sender: TObject; E: Exception);

MessageDlg (E. Message, mtError, [mbOK], 0);

// Ваши инструкции по обработке исключения

Здесь обработка также состоит в кратком информировании пользователя, возможно, освобождении памяти, закрытии файлов.

^ Локальная обработка

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

//инструкции, которые должны быть выполнены даже в случае ошибки

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

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

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

//инструкции, которые должны быть выполнены в случае ошибки

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

procedure TForm1.btnOpenClick (Sender: TObject);

if OpenDialog1.Execute then begin

MessageDlg('Ошибка открытия таблицы', OpenDialog1.FileName, '!',

При нажатии кнопки btnOpen появляется окно выбора файла таблицы БД для открытия. После выбора файла таблицы набор данных Table1 связывается с этой таблицей и выполняется открытие набора данных. Инструкции, управляющие выбором и открытием файла, включены в блок try, так как. возможна ошибка. При ее возникновении выполняется блок except. В примере обработка заключается только в выдаче предупреждения.

Блок except можно разбить на несколько частей с помощью конструкций on … do, позволяющих анализировать класс исключения для его более удобной и полной обработки.

Если класс возникшего исключения совпадает с указанным после on, то выполняются инструкции после слова do.

Идентификатор произвольно задается пользователем и является необязательным, т.е. может отсутствовать, при этом не ставится и (:). Идентификатор – это локальная переменная, представляющая собой экземпляр класса исключения, который можно использовать для доступа к объекту возникшего исключения. Эта переменная доступна только внутри on … do.

Если в блоке except расположено несколько инструкций on . do, то else, располагающийся в конце блока, относится ко всей совокупности конструкций. Инструкции после do и else могут быть составными.

Если какие-либо действия должны быть выполнены независимо от того, произошла ошибка или нет, то удобно использовать инструкцию try. finally. Однако эта конструкция не обрабатывает исключения, а лишь смягчает их последствия. Для локальной обработки внутрь конструкции try. finally можно включить try … except.

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

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

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

Различия между пессимистичным и оптимистичным подходами

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

При пессимистичном подходе возможные конфликты блокируются прежде, чем они возникнут. Система блокируется при выполнении инструкции и разблокируется, как только транзакция зафиксирована.

В оптимистичном сценарии система обнаруживает конфликты сразу и проводит проверки во время фиксации.

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

Оптимистичный подход требует меньше ресурсов и обычно более эффективен. Отчасти это связано с тем, что в большинстве приложений конфликты транзакций встречаются редко. Основное функциональное различие между пессимистическим и оптимистическим сценариями заключается в том, что если происходит конфликт, в пессимистическом сценарии нужно ждать, а в оптимистическом сценарии клиент должен повторить попытку выполнить транзакцию, которая завершается сбоем. Функциональные различия увеличиваются, если действует уровень изоляции REPEATABLE READ, и достигают максимума на уровне SERIALIZABLE.

Режимы инициации транзакций

SQL Server имеет следующие режимы запуска транзакций.

Автоматическая фиксация . Начало простого запроса или инструкция DML неявно открывает транзакцию, а окончание инструкции неявно фиксирует эту транзакцию. Автофиксация используется по умолчанию.

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

Явный . Transact-SQL содержит код BEGIN TRANSACTION и может содержать код COMMIT TRANSACTION. В одной и той же транзакции могут одновременно выполняться сразу несколько инструкций.

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

Неявный . Активен, пока действует код SET IMPLICIT_TRANSACTION ON. Возможно, лучше подошло бы имя IMPLICIT_BEGIN_TRANSACTION, поскольку единственная задача этого параметра — неявное выполнение действий, аналогичных явной процедуре BEGIN TRANSACTION, перед каждой инструкцией UPDATE, если 0 = @@trancount. Таким образом, в конечном итоге именно ваш код T-SQL выдает явный код COMMIT TRANSACTION.

Блок ATOMIC — все инструкции в блоках ATOMIC (атомарных блоках) всегда выполняются в рамках одной транзакции. При успешном выполнении фиксируются все действия атомарного блока, а при сбое все его действия откатываются. Использовать блок ATOMIC обязательно для всех хранимых процедур, скомпилированных в собственном коде.

Пример кода в режиме Explicit

В следующем интерпретированном сценарии Transact-SQL используется следующее:

Явная транзакция.
Таблица, оптимизированная для памяти, с именем dbo.Order_mo.
Контекст для уровня изоляции транзакции READ COMMITTED.

В связи с этим в таблице, оптимизированной для памяти, должно присутствовать табличное указание. Оно должно вводить уровень изоляции SNAPSHOT или более высокий. В примера кода используется указание WITH (SNAPSHOT). Если оно удаляется, сценарий выдает ошибку 41368, при которой автоматическая повторная попытка недопустима:

Ошибка 41368

Доступ к оптимизированным для памяти таблицам с уровнем изоляции READ COMMITTED поддерживается только для транзакций с автоматической фиксацией. Он не поддерживается для явных или неявных транзакций. Обеспечьте поддерживаемый уровень изоляции для оптимизированной для памяти таблицы с помощью табличного указания, например WITH (SNAPSHOT).

Вы можете обойтись без указания WITH (SNAPSHOT) . Для этого используйте параметр базы данных MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT . Если этот параметр имеет значение ON , уровень изоляции доступа к таблице, оптимизированной для памяти, автоматически повышается до уровня изоляции SNAPSHOT.

Управление версиями строк

В таблицах, оптимизированных для памяти, используется сложная система управления версиями, которая обеспечивает эффективность оптимистичного подхода даже на самом строгом уровне изоляции SERIALIZABLE. Дополнительные сведения см. в разделе Введение в таблицы, оптимизированные для памяти.

Таблицы на жестком диске косвенно используют систему управления версиями строк, если применяется уровень изоляции READ_COMMITTED_SNAPSHOT или SNAPSHOT. Эта система основана на применении tempdb. А в структурах данных, оптимизированных для памяти, для большей эффективности используются встроенные механизмы.

Уровни изоляции

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

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

Фазы и время существования транзакций

При работе с таблицей, оптимизированной для памяти, время существования транзакции можно разделить на несколько этапов, как показано на следующей схеме.

Описания фаз приводятся ниже.

Обычная обработка: этап 1 (из 3)

Этот этап включает выполнение всех запросов и инструкций DML в запросе.
На этом этапе инструкции воспринимают версию таблиц, оптимизированных для памяти, в качестве логического времени начала транзакции.

Проверка: этап 2 (из 3)

Фаза проверки начинается с назначения времени окончания (тем самым транзакция помечается как логически завершенная). При таком завершении все изменения транзакции становятся видимы другим транзакциям, которые от нее зависят. Пока транзакция не зафиксирована, фиксация зависимых транзакций невозможна. Кроме того, транзакции с такими зависимостями не могут возвращать результирующие наборы клиенту. Клиент будет видеть только данные, успешно зафиксированные в базе.
Этот этап включает уровни проверку параметров repeatable read и serializable. С уровнем изоляции repeatable read система проверяет наличие изменений в прочитанных транзакцией строках с момента прочтения. При проверке параметра serializable система проверяет, были ли какие-либо строки вставлены в диапазон данных, сканированный этой транзакцией. Согласно таблице в разделе уровней изоляции и конфликтов, проверки repeatable read и serializable могут происходить при использовании изоляции snapshot, чтобы подтвердить соответствие ограничений для уникальных и внешних ключей.

Обработка фиксации: этап 3 (из 3)

Во время фиксации изменения в надежных таблицах заносятся в журнал, а журнал записывается на диск. Затем управление возвращается клиенту.
По окончании фиксации все зависимые транзакции уведомляются о том, что их можно фиксировать.

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

Обнаружение конфликтов и логика повторных попыток

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

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

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

Нарушение параллельными транзакциями ограничений для внешних ключей происходит редко и, как правило, говорит о проблемах с логикой приложения или с вводом данных. Однако эта ошибка также может произойти, если отсутствует индекс в столбцах, связанных с ограничением FOREIGN KEY. Следовательно, в таблице с оптимизацией для памяти рекомендуется всегда создавать индекс столбцов внешних ключей.

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

Существует ограничение на число транзакций, от которых может зависеть другая транзакция (Tx1). Такие зависимости называются исходящими. Кроме того, существует ограничение на число транзакций, которые могут зависеть от определенной транзакции (Tx1). Это входящие зависимости. Ограничение в обоих случаях равно 8.

Наиболее распространенная причина этого сбоя — ситуация, когда множество транзакций чтения осуществляет доступ к данным, записанным одной транзакцией записи. Вероятность попадания в эту ситуацию увеличивается, если все транзакции чтения выполняют крупные сканирования одних и тех же данных, а проверка или фиксация транзакции записи занимает много времени, например транзакция записи выполняет крупные сканирования в условиях изоляции serializable (увеличивается продолжительность проверки) или журнал транзакций помещается на устройство с медленным вводом/выводом журнала (увеличивается продолжительность фиксации). Если транзакции чтения сканируют большой объем данных, хотя должны получать доступ лишь к нескольким строкам, возможно, отсутствует какой-то индекс. Аналогично, если транзакция записи использует изоляцию serializable и выполняет крупные сканирования, а ожидается, что она будет осуществлять доступ лишь к небольшому количеству строк, возможно, речь идет об отсутствующем индексе.

Логика повторных попыток

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

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

Пример кода повторной попытки T-SQL

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

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

Транзакция между контейнерами

Транзакция называется транзакцией между контейнерами, если она:

обращается к таблице, оптимизированной для памяти, из интерпретированного Transact-SQL; или
выполняет хранимую процедуру, скомпилированную в собственном коде, если транзакция уже открыта (XACT_STATE() = 1).

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

В рамках одной транзакции между контейнерами можно использовать разные уровни изоляции для доступа к дисковым таблицам и таблицам, оптимизированным для памяти. Это различие выражено через явные табличные указания, такие как WITH (SERIALIZABLE) или через параметр базы данных MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT, который неявно повышает уровень изоляции для оптимизированной для памяти таблицы до снимка, если TRANSACTION ISOLATION LEVEL имеет значение READ COMMITTED или READ UNCOMMITTED.

В следующем примере кода Transact-SQL:

Доступ к дисковой таблице Table_D1 осуществляется с использованием уровня изоляции READ COMMITTED.
Доступ к оптимизированной для памяти таблице Table_MO7 осуществляется с использованием уровня изоляции SERIALIZABLE. Table_MO6 не имеет определенного уровня изоляции, поскольку вставляемые элементы отличаются единообразием и выполняются в основном с изоляцией serializable.

Ограничения

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

база данных tempdb;
база данных master, доступная только для чтения.

Распределенные транзакции не поддерживаются. Если используется уровень BEGIN DISTRIBUTED TRANSACTION, транзакция не может получить доступ к таблице, оптимизированной для памяти.

скомпилированные в собственном коде хранимые процедуры

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

. BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, . ) .

В хранимой процедуре, скомпилированной в собственном коде, нельзя использовать явные инструкции управления транзакциями. BEGIN TRANSACTION и ROLLBACK TRANSACTION и т. д. запрещены.

Дополнительные сведения о контроле над транзакциями с блоками ATOMIC см. в разделе Блоки ATOMIC

1. Введение. Архитектуры удаленных баз данных
1.2. Основные технологии доступа к данным и типовые элементы доступа
2. Базовая технология СОМ: понятие и создание объекта, интерфейсы объект, библиотеку СОМ, фабрику класса
3. Основные понятие и место применение технологий ADO, MIDAS, MTS, CORBA.
4. Основные технологии доступа к данным и типовые элементы доступа
1.3 Введение в работу с удаленными базами данных
5. Введение в работу с удаленными базами данных
6. Введение в работу с удаленными базами данных

2.1 Проектирование структуры базы данных с помощью команд
7. Назначение и структура файлов базы данных
8. Команды по созданию и перемещению файла базы данных.
9. Команды по созданию, модификации и удалению таблицы.
10. Команды по созданию, просмотру, перестройке и удалению индексов.
11. Вывод на экран справки о структуре таблицы.
12. Проектирование структуры базы данных. Нормализация таблиц.
13. Создание серверной части приложения: алиас, файл базы данных, таблицы, индексы.

2.2 Визуальные средства проектирования структуры базы данных
14. Приемы графического проектирования структуры базы данных.
15. Просмотр и модификация созданной структуры базы данных.
16. Визуальное проектирование структуры базы данных: таблицы, индексы.
17. Визуальное проектирование структуры базы данных: условия ссылочной целостности, взаимосвязи.
18. Итоговое занятие

3.1 Компоненты доступа к данным. Запросы на выборку данных. Компоненты отображения данных выборки
19.Понятие, назначение и задание различных листов приложения клиента. установление связи между ними.
20.Простые и сложные запросы на выборку.
21.Клиентская часть: размещение не визуальных компонентов, соединённых с БД.
2.2Клиентская часть: размещение визуальных компонентов, отображение таблиц.

23.Форматы операторов управления данными и методы их выполнения.
24.Запросы на добавление данных.
25.Запросы на редактирование и удаление данных.

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

26.Понятие, назначение и команды по созданию хранимой процедуры, триггера и генератора.
27.Понятие и виды каскадных воздействий. Организация выполнения каскадных воздействий
28.Создание генератора и триггеров. Каскадные воздействия.
29.Сохранение хранимых процедур.

3.4 Сортировка, поиск и фильтрация данных в базе данных и в выборках

30.Сортировка, поиск и фильтрация данных в базе данных и в выборках.
31.Сортировка и поиск данных: в базе данных и в выборках.
32.Фильтрация данных: в базе данных и в выборках.

33.Управление транзакциями и кэширование памяти
34.Работа с транзакциями.
35.Кэширование изменений при работе с транзакциями.

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

36. Понятие исключительной ситуации. Мягкий жесткий выход из исключительной ситуации. Место возникновения исключительной ситуации.
37.Определение характера ошибки, вызвавшей исключительную ситуацию. Перехват исключительной ситуации, написание обработчика и организация мягкого выхода из исключительной ситуации
38.Обеспечение достоверности данных и перехват исключительных ситуаций.

39.Назначение и виды отчетов. Мастер отчетов и Конструктор отчетов. Настройка печати и печать отчетов.
40.Работа с отчётами.

3.8 Особенности проектирования клиентской части приложения в различных технологиях доступа к данным

41.Особенности и назначения технологий доступа к данным ADO, MIDAS,MTS и CORBA. Специальные компоненты доступа к данным каждой технологии.
42.Особенности размещения и использование свойств и методов компонентов доступа к данным.
43.Особенности создания интерфейса к данным.

4 раздел. АДМИНИСТРИРОВАНИЕ И ЭКСПЛУАТАЦИЯ БАЗ ДАННЫХ

44.Установление привилегий доступа к данным.
45.Установление привилегий доступа.

4.2 Копирование и перенос данных. Восстановление данных

46.Копирование и перенос данных. Восстановление данных.
47.Копирование и восстановление данных.

4.3 Копирование клиентской части приложения баз данных

48.Копирование клиентской части приложения.
49.Копирование клиентской части.
50.Итоговое занятие

Читайте также: