Главная страница » Что такое транзакция в sql

Что такое транзакция в sql

  • автор:

Транзакции в T-SQL – основы для новичков с примерами

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

Транзакции в T-SQL – основы для новичков с примерами

Заметка! T-SQL – это расширение языка SQL, реализованное в Microsoft SQL Server. Более подробно об этом можете почитать в статье – Что такое T-SQL. Подробное описание для начинающих.

Транзакции в T-SQL

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

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

По сути каждая отдельная инструкция языка T-SQL является транзакцией, это называется «Автоматическое принятие транзакций» или «Неявные транзакции», но также есть и явные транзакции, это когда мы сами явно начинаем транзакцию и также явно заканчиваем ее, т.е. делаем все это с помощью специальных команд.

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

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

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

Чтобы этого не допустить, все SQL инструкции, которые логически что-то объединяет, в данном случае все операции, связанные с переводом средств, пишут внутри транзакции, и тогда, если наступит подобная ситуация, все изменения будут отменены, т.е. деньги вернутся обратно на счет.

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

Транзакции – это отличный механизм обеспечения целостности данных.

Свойства транзакции

У транзакции есть 4 очень важных свойства:

  • Атомарность – все команды в транзакции либо полностью выполняются, и соответственно, фиксируются все изменения данных, либо ничего не выполняется и ничего не фиксируется;
  • Согласованность – данные, в случае успешного выполнения транзакции, должны соблюдать все установленные правила в части различных ограничений, первичных и внешних ключей, определенных в базе данных;
  • Изоляция – механизм предоставления доступа к данным. Транзакция изолирует данные, с которыми она работает, для того чтобы другие транзакции получали только согласованные данные;
  • Надежность – все внесенные изменения фиксируются в журнале транзакций и данные считаются надежными, если транзакция была успешно завершена. В случае сбоя SQL Server сверяет данные, записанные в базе данных, с журналом транзакций, если есть успешно завершенные транзакции, которые не закончили процесс записи всех изменений в базу данных, они будут выполнены повторно. Все действия, выполненные не подтвержденными транзакциями, отменяются.

Команды управления транзакциями в T-SQL

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

  • BEGIN TRANSACTION (можно использовать сокращённую запись BEGIN TRAN) – команда служит для определения начала транзакции. В качестве параметра этой команде можно передать и название транзакции, полезно, если у Вас есть вложенные транзакции;
  • COMMIT TRANSACTION (можно использовать сокращённую запись COMMIT TRAN) – с помощью данной команды мы сообщаем SQL серверу об успешном завершении транзакции, и о том, что все изменения, которые были выполнены, необходимо сохранить на постоянной основе;
  • ROLLBACK TRANSACTION (можно использовать сокращённую запись ROLLBACK TRAN) – служит для отмены всех изменений, которые были внесены в процессе выполнения транзакции, например, в случае ошибки, мы откатываем все назад;
  • SAVE TRANSACTION (можно использовать сокращённую запись SAVE TRAN) – данная команда устанавливает промежуточную точку сохранения внутри транзакции, к которой можно откатиться, в случае возникновения необходимости.

Примеры транзакций в T-SQL

Давайте рассмотрим примеры транзакций, реализованные на языке T-SQL.

Исходные данные для примеров

Но сначала нам необходимо создать тестовые данные для нашего примера.

Для этого выполните следующую инструкцию.

Скриншот 1

Простой пример транзакции в T-SQL

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

Поэтому мы решили эти инструкции объединить в одну транзакцию.

Сначала мы открываем транзакцию командой BEGIN TRANSACTION, далее пишем все необходимые инструкции, которые мы хотим объединить в транзакцию.

После этого командой COMMIT TRANSACTION мы сохраняем все внесенные изменения.

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

Скриншот 2

Однако, если в любой из инструкций возникнет ошибка, транзакция не завершится, и все изменения не сохранятся.

При этом, стоит помнить о том, что ошибки с определённым уровнем серьезности, например, ошибки, связанные с нарушением ограничений, не влекут за собой автоматический откат всех изменений внесенных текущей транзакцией, поэтому всегда необходимо использовать или инструкцию SET XACT_ABORT ON, или обработку ошибок (допускается и совместное использование).

Например, если во второй инструкции мы попытаемся записать в столбец Price какое-нибудь текстовое значение, то у нас возникнет ошибка, и изменения, внесённые первой инструкцией, не зафиксируются на постоянной основе.

Скриншот 3

Пример транзакции в T-SQL с обработкой ошибок

В языке T-SQL существует механизм перехвата и обработки ошибок – конструкция TRY… CATCH.

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

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

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

Если ошибок нет, то в блок CATCH мы, соответственно, не попадаем и у нас выполнится команда COMMIT TRANSACTION, которая сохранит все изменения.

В этом примере нет ошибок, поэтому транзакция завершена успешно.

Скриншот 4

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

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

Скриншот 5

Уровни изоляции транзакций в T-SQL

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

Давайте подробней рассмотрим уровни изоляции.

READ UNCOMMITTED

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

READ COMMITTED

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

REPEATABLE READ

На данном уровне изоляции запрещается изменение данных между двумя операциями чтения в одной транзакции. Здесь происходит запрет на так называемое «неповторяющееся чтение» или «несогласованный анализ». Другими словами, если в одной транзакции есть несколько операций чтения, данные будут блокированы и их нельзя будет изменить в другой транзакции. Таким образом, Вы избежите ситуации, когда вначале транзакции Вы запросили данные, провели их анализ (некое вычисление), в конце транзакции запросили те же самые данные, а они уже отличаются от первоначальных, так как они были изменены другой транзакцией.

Также уровень REPEATABLE READ, как и остальные, запрещает «Потерянное обновление» – это когда две транзакции сначала считывают одни и те же данные, а затем изменяют их на основе неких вычислений, в результате обе транзакции выполнятся, но данные будут те, которая зафиксировала последняя операция обновления. Это происходит потому, что данные в операциях чтения в начале этих транзакций не были заблокированы.

SERIALIZABLE

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

SNAPSHOT и READ COMMITTED SNAPSHOT

Также существуют уровни изоляции, алгоритм которых основан на версиях строк, это

  • SNAPSHOT
  • READ COMMITTED SNAPSHOT

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

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

READ COMMITTED SNAPSHOT – этот уровень изоляции работает практически так же, как уровень SNAPSHOT, с одним отличием, он хранит снимок строк, которые подтверждены на момент запуска команды, а не транзакции, как в SNAPSHOT.

Побочные эффекты параллелизма

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

  • Потерянное обновление (LostUpdate) – при одновременном изменении данных разными транзакциями одно из изменений будет потеряно;
  • Грязное чтение (DirtyRead) – чтение неподтвержденных данных;
  • Неповторяющееся чтение (Non-Repeatable Read) – чтение измененных данных в рамках одной транзакции;
  • Фантомное чтение (Phantom Reads) – чтение записей, которые появились между началом и завершением транзакции.

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

Побочный эффект / Уровень изоляции Потерянное обновление Грязное чтение Неповторяющееся чтение Фантомные записи
READ UNCOMMITTED Устраняет Не устраняет Не устраняет Не устраняет
READ COMMITTED Устраняет Устраняет Не устраняет Не устраняет
REPEATABLE READ Устраняет Устраняет Устраняет Не устраняет
SERIALIZABLE Устраняет Устраняет Устраняет Устраняет
SNAPSHOT Устраняет Устраняет Устраняет Устраняет
READ COMMITTED SNAPSHOT Устраняет Устраняет Устраняет Устраняет

Включение уровня изоляции в T-SQL

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

Также для уровней SNAPSHOT и READ COMMITTED SNAPSHOT предварительно необходимо включить параметр базы данных ALLOW_SNAPSHOT_ISOLATION для уровня изоляции SNAPSHOT и READ_COMMITTED_SNAPSHOT для уровня READ COMMITTED SNAPSHOT.

Заметка! Если Вас интересует язык SQL, то рекомендую почитать книгу «SQL код» это самоучитель по языку SQL для начинающих программистов. В ней язык SQL рассматривается как стандарт, чтобы после прочтения данной книги можно было работать с языком SQL в любой системе управления базами данных.

2.19. Транзакции

Язык запросов Transact-SQL взял свое название от слова транзакция. Я думаю, что Microsoft не зря сконцентрировало на этом понятии особое внимание, ведь транзакции действительно являются очень мощным средством управления базой данных.

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

Рассмотрим классическую задачу – банковскую проводку. Допустим, что у нас есть таблица из двух полей – номер счета в банке и сумма денег на этом счету. Нам необходимо перевести деньги с одного счета на другой. Для этого нужно выполнить запрос UPDATE, чтобы уменьшить сумму первого счета на нужную сумму. После этого выполняем UPDATE, чтобы увеличить значение второго счета. Все вроде бы нормально. А что, если после уменьшения первого счета выключат свет и сервер не успеет пополнить другой счет? Деньги уже сняты, но никуда не записаны, а значит, они пропали.

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

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

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

Существует две разновидности транзакций в SQL Server:

  • Скрытые транзакции, каждый оператор, такой как INSERT, UPDATE или DELETE выполняется в транзакции. Неявными транзакциями можно управлять и об этом мы поговорим в разделе 4.1.2;
  • Явные транзакции объявленные пользователем – операторы, сгруппированные в BEGIN TRANSACTION и COMMIT TRANSACTION.

Очень важно понимать, что транзакции необходимы только при модификации данных, т.е. использовании операторов INSERT, UPDATE или DELETE. Простая выборка SELECT не изменяет данных, и запоминать или откатывать нечего. Нет, выполнять операции выборки в транзакции можно, но если транзакция не изменяет данные, то незачем ее вообще начинать.

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

  • ALTER DATABASE
  • BUCKUP LOG
  • CREATE DATABASE
  • DROP DATABASE
  • RECONFIGURE
  • RESTORE DATABASE
  • RESTORE LOG
  • UPDATE STATISTICS

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

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

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

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

Начало транзакции в MS SQL Server имеет следующий синтаксис:

Опция Transaction name указывает имя транзакции определенное пользователем. Опция WITH MARK указывает, что транзакция маркирована в журнале транзакций.

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

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

Очень важно понимать, если начата транзакция и изменены какие-то записи, то эти записи блокируются, пока транзакция не будет завершена. Давайте посмотрим это на примере, заодно познакомимся с самой командой. Выполните следующие команды в Query Analyzer:

Теперь откройте еще одну копию программы или установите новое соединение, выбрав меню File/Connect (Файл/Соединиться). В новом окне напишем и выполним следующий запрос:

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

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

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

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

Теперь откатываем транзакцию, выполняя команду ROLLBACK TRANSACTION. Снова выполняем запрос SELECT и видим, что данные вернулись на родину. Транзакция удачно отклонена и физического удаления из базы данных не произошло. Почему мы в этот раз без проблем смогли просмотреть таблицу, а из другой сессии просмотр изменяемой в транзакции таблицы не доступен? Блокировки происходит для всех сессий кроме той, которая выполняет транзакцию. В листинге 2.8 показан весь код эксперимента с подробными комментариями.

Листинг 2.8. Пример эксперимента с удалением данных в транзакции

Все эти команды нужно выполнять в одном и том же окне. К тому же, если в одном окне (сессии) вы начали транзакцию, то именно в этом окне вы должны ее завершить (COMMIT) или откатить (ROLLBACK).

Если в листинге 2.8 заменить вызов команды ROLLBACK TRANSACTION на COMMIT TRANSACTION, то произойдет физическое удаление всех записей из таблицы товаров. Теперь удаленные строки вернуть уже невозможно.

Теперь посмотрим еще один пример в листинге 2.9.

Листинг 2.9. Пример работы с транзакциями

Тут достаточно много действий, поэтому давайте их рассмотрим поэтапно:

  1. Начинаем транзакцию;
  2. Добавляем запись о покупке товара с названием Картофель;
  3. Обновить цену картофеля, увеличив ее до 15 рублей;
  4. Завершаем транзакцию, запоминая изменения;
  5. Обновляем цену до 17 руб.;
  6. Откатываем транзакцию;
  7. Просматриваем содержимое таблицы.

Что произошло с содержимым таблицы? Запись о картофеле добавлена, а значит, все что было до запоминания изменений (шаг 4) выполнено удачно. А вот цена равна 17-ти рублям. Почему? Неужели на шаге 6 мы не откатили изменение цены? Да, отката не произошло, потому что новая транзакция не начиналась. На шаге 1 мы начали транзакцию, а на шаге 4 завершили. Новая транзакция не начиналась, а значит откатывать нечего и шаг 6 завершиться ошибкой:

The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

Запрос ROLLBACK TRANSACTION не имеет соответствующего BEGIN TRANSACTION.

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

Для каждого оператора BEGIN TRANSACTION должен быть только один оператор COMMIT TRANSACTION или ROLLBACK TRANSACTION.

А что если внутри транзакции начать новую транзакцию? Результат неожиданный и давайте его увидим на примере (см. листинг 2.10).

2.10. Вложенные транзакции

Логика запроса следующая:

  • Начать транзакцию;
  • Вставить строку;
  • Начать транзакцию;
  • Обновить таблицу;
  • Откатить транзакцию;
  • Запомнить изменения.

По логике вещей, на шаге 5 мы должны были откатить вторую транзакцию (т.е. изменение таблицы), а на шаге 6 запоминаем транзакцию 1, в которой происходит добавление записи. Посмотрите содержимое таблицы. Ни добавления, ни тем более изменения. Почему? Если посмотреть сообщения, которые выдал сервер, то вы увидите, что на шаге 6 произошла ошибка о том, что нет соответствующего начала транзакции и нечего начинать. Получается, что оператор ROLLBACK TRANSACTION откатывает все начатые транзакции.

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

Посмотрим на листинг 2.11. В нем показан такой же пример, но с именованными транзакциями и без отката.

Листинг 2.11. Использование вложенных транзакций

В данном примере после операторов BEGIN TRANSACTION и COMMIT TRANSACTION указывается имя T1 и T2. Таким образом, мы идентифицируем транзакции и завершаем их в обратном порядке объявлению.

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

Минимум, что необходимо указать – это сам оператор и имя точки сохранения. Например, следующий оператор создает точку сохранения с именем point1:

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

Листинг 2.12. Откат до определенной точки

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

  1. Начинаем транзакцию;
  2. Добавляем строку;
  3. С помощью оператора SAVE TRAN сохраняем состояние таблицы. Точнее сказать, ставим точку в журнале, ведь пока все изменения происходят только в журнале транзакций;
  4. Обновляем цену последней добавленной строки;
  5. Восстанавливаем состояние таблицы на точку сохранения, установленную на третьем шаге. В этот момент из журнала транзакций удаляется запись о необходимости обновить цену, а остается только запись о необходимости добавить строку;
  6. Запоминаем изменения, а в журнале транзакций находиться только добавление строки и именно это сохраняется в таблице товаров.

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

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

Теория транзакций с примерами из Microsoft SQL Server

Думаю, многие из вас работали с транзакциями и представляют, как применить к базе данных консистентную последовательность операций. Сегодня мы узнаем, что происходит с транзакцией, когда мы отправляем ее в СУБД. Мы познакомимся с классической теорией транзакций и тем, какие существуют подходы для формирования корректных расписаний. Кроме того, постараемся связать эту теорию с практикой на примере известной СУБД Microsoft SQL Server. (Сегодня будет много информации, приготовьтесь!)

Сериализуемые расписания

Транзакции

Начнем с определения того, что такое транзакция:

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

  • отсутствуют помехи со стороны других приложений;
  • транзакция выполнена полностью.

В MS SQL Server существует 2 типа транзакций:

  1. Неявные — отдельные операции INSERT, UPDATE или DELETE.
  2. Явные — набор операций языка T-SQL, начинающийся с инструкции BEGIN TRANSACTION и заканчивающийся COMMIT или ROLLBACK.

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

Аномалии транзакций

При параллельном выполнении транзакций возникают различные проблемы, связанные с логикой работы с операциями. Рассмотрим наиболее распространенные из них на примерах из SQL сервера:

1) Потерянное обновление. При обновлении поля двумя транзакциями одно из изменений теряется.

Транзакция 1 Транзакция 2
SELECT x FROM tbl WHERE y=1; SELECT x FROM tbl WHERE y=1;
UPDATE tbl SET x=5 WHERE y=1;
UPDATE tbl SET x=3 WHERE y=1;

2) Грязное чтение. Чтение данных, полученных в результате действия транзакции, которая после этого откатится.

Транзакция 1 Транзакция 2
SELECT x FROM tbl WHERE y=1;
UPDATE tbl SET x=x+1 WHERE y=1;
SELECT x FROM tbl WHERE y=1;
ROLLBACK;

3) Неповторяющееся чтение. Возникает, когда в течение одной транзакции при повторном чтении данные оказываются перезаписанными.

Транзакция 1 Транзакция 2
SELECT x FROM tbl WHERE y=1; SELECT x FROM tbl WHERE y=1;
UPDATE tbl SET x=x+1 WHERE y=1;
COMMIT;
SELECT x FROM tbl WHERE y=1;

4) Фантомное чтение. Отличие от предыдущей аномалии в том, что при повторном чтении одна и та же выборка дает разные множества строк.

Транзакция 1 Транзакция 2
SELECT SUM(x) FROM tbl;
INSERT INTO tbl (x, y) VALUES (5, 3);
SELECT SUM(x) FROM tbl;

Вычислительная модель

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

  1. Элементарные операции, определенные над объектами данных.
  2. Транзакции: последовательности или частично упорядоченные множества элементарных операций.
  3. Расписания (или истории), описывающие конкурентное выполнение транзакций.
  4. Критерии корректности расписаний (историй).
  5. Алгоритмы управления транзакциями, обеспечивающие получение корректных расписаний.

Сегодня мы будем рассматривать транзакции в контексте страничной модели. В этой модели база данных представляется как набор независимых страниц ` (x, y, z) `, над которыми возможны две атомарные операции: read и write с полным или частичным порядком внутри транзакции.

Критерии корректности

Первоначально определим понятие истории. История — упорядоченная совокупность операций нескольких транзакций, включая операции завершения транзакции (commit, abort). Расписанием называется префикс истории. Пример (индексы соответствуют транзакциям): ` r_1(x) r_2(x) w_1(x) w_2(x) c_1 c_2 `

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

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

Семантика Эрбрана

С помощью этого понятия определяются последующие сериализуемости. Семантика Эрбрана основывается на двух предположениях:

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

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

Расписание Семантика Эрбрана
r1(x) r2(y) w1(x) w3(z) r2(x) w2(z) w2(x) f2x(f1x(f0x)), f2y(f0y))

Сериализуемость по конечному состоянию

Расписания эквивалентны по конечному состоянию, если:

  • равны множества операций
  • семантики Эрбрана равны для всех элементов данных

Расписание называется сериализуемым по конечному расписанию (FSR), если оно эквивалентно серийному по конечному состоянию. Пример неэквивалентных расписаний:

Расписание Семантика Эрбрана
r1(x) r2(y) w1(y) w2(y) c1 c2 f2y(f0y())
r1(x) w1(y) r2(y) w2(y) c1 c2 f2y(f1y(f0y()))

Этот тип сериализуемости решает проблему потери обновления, но с другими аномалиями справиться он не в силах.

Сериализуемость по видимому состоянию

В дополнение к условиям предыдущей эквивалентности для эквивалентности по видимому состоянию требуется, чтобы все операции чтения в расписаниях следовали после одинаковых операций записи. Пример расписания, сериализуемого по видимому (VSR), но не по конечному состоянию: ` w_1(x) r_2(x) r_2(y) w_1(y) c_1 c_2 `

VSR по-прежнему не может справиться с аномалиями грязного чтения, так как не учитывает оборванные транзакции. Кроме того, проверка расписания на эту сериализуемость является NP-полной.

Сериализуемость по конфликтам

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

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

  1. Перестановка соседних операций чтения
  2. Перестановка соседних операций над разными элементами

Диспетчеры и протоколы

Функционирование диспетчера

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

В MS SQL Server, так же как и во многих других СУБД есть два вида конкурентного доступа (протоколов):

  1. Пессимистичные. Для предотвращения нарушения сериализуемости двумя транзакциями применяются блокировки.
  2. Оптимистичные. Протокол основывается на предположении маловероятности одновременного изменения данных двумя транзакциями. В случае нарушения сериализуемости транзакция обрывается.

Протокол Two Phase Locking

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

В SQL сервере используется модификация 2PL, именуемая SS2PL. К правилу, использующемуся в базовом протоколе добавляется strict — все полученные замки на запись сохраняются до завершения транзакции и strong — все замки удерживаются до завершения транзакции. При этом, в СУБД есть несколько режимов блокировок. Выбор режима зависит от типа ресурса, который требуется заблокировать. На уровне строки или страницы можно применить блокировки следующих режимов:

  1. Разделяемая. Блокировка занимает ресурс только для чтения. Несколько транзакций могут блокировать этот ресурс таким образом и читать из него данные, но ни один процесс не сможет в него что-либо записать.
  2. Монопольная. Резервирует ресурс для выполнения операций INSERT, UPDATE, DELETE. На ресурс может быть установлена только одна блокировка такого типа, причем блокировки других режимов также не могут быть установлены вместе с ней.
  3. Обновления. Блокировка такого типа может быть установлена на ресурс, только если на нем еще не установлена другая обновляющая или монопольная блокировка. В случае установки ее на ресурс с разделяемой блокировкой, она накладывает на ресурс еще одну разделяемую блокировку. При этом, если модифицирующая объект транзакция подтверждается, то она преобразовывается в монопольную.

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

  1. Разделяемая с намерением. Защищает запрошенные или полученные разделяемые блокировки на некоторых ресурсах на более низком уровне иерархии.
  2. Монопольная с намерением. Защищает запрошенные или полученные монопольные блокировки на некоторых ресурсах на более низком уровне иерархии.
  3. Разделяемая с монопольным намерением. Защищает запрошенные или полученные совмещаемые блокировки на всех ресурсах более низкого уровня иерархии, а также блокировки с намерением на некоторых ресурсах более низкого уровня.

Проблемы 2PL

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

Одна из стратегий борьбы с тупиками, это их распознавание и обрыв. Для распознавания необходимо построить граф ожиданий, в котором вершины — транзакции, а дуги — запросы транзакции на блокировку, конфликтующую с уже установленной. Наличие контура в графе означает наличие тупика. Для разрешение взаимоблокировки нужно оборвать одну из транзакций из образующих контур. Такая стратегия применяется в MS SQL Server.

При этом может возникнуть новая проблема — голодание. Этим термином описывается ситуация, когда одна и та же транзакция становится жертвой разрешения тупика при каждом новом запуске. Для предотвращения этой проблемы можно использовать первоначальные отметки времени поступления транзакции при выборе жертвы. В SQL сервере вы можете присвоить параметру DEADLOCK_PRIORITY одно из 21 значений (от -10 до 10) для выбора разных уровней приоритета взаимоблокировки.

Кроме того, существует еще один способ борьбы с тупиками. Для преждевременного завершения транзакции можно установить ограничение по времени. Если время ожидания блокировки превышает это ограничение, транзакция обрывается. В SQL сервере используется следующий синтаксис: SET LOCK_TIMEOUT 4000

Snapshot Isolation

Snapshot Isolation — один из оптимистичных протоколов. Каждая транзакция читает из снапшота — состояния базы данных, на момент старта транзакции. При этом при выполнении транзакции формируется write set — все операции записи. Перед коммитом транзакции происходит проверка на то, пересекается ли write set с другими write set’ами, параллельно выполнявшихся транзакций. В случае пересечения с уже принятой транзакцией, текущая обрывается. Протокол справляется с наиболее тяжелыми из аномалий, но не обеспечивает сериализуемости даже по конечному состоянию.

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

Уровни изоляции в MS SQL Server

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

Read Uncommitted

Самая простая форма изоляции между транзакциями. Этот уровень не использует никакие блокировки, и, следовательно, совершенно не изолирует операции чтения от других транзакций. Из описанных в начале поста аномалий Read Uncommitted допускает три: грязное чтение, неповторяемое чтение и фантомы.

Read Committed

Существует две формы уровня изоляции Read Committed — для пессимистичной и оптимистичной моделей выполнения. В этом подразделе описывается пессимистичный вариант, оптимистичному соответствует уровень Read Committed Snapshot.

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

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

Repeatable Read

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

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

Serializable

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

Read Committed Snapshot

Последние два уровня используются в оптимистичном контексте. Read Committed Snapshot применяется на уровне инструкции, что означает, что что любая другая транзакция будет читать зафиксированные значения в том виде, в каком они существуют на момент начала этой инструкции. Для выборки строк для обновлений этот уровень изоляции возвращает версии строк в фактические данные и устанавливает на выбранных строках блокировки обновлений. Реальные строки данных, которые требуется изменить, получают монопольные блокировки.

Snapshot

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

Заключение

Сегодня мы познакомились с самыми основами теории транзакций и посмотрели, какие из них нашли свое применение в промышленной СУБД. Пост основывался на материалах лекций Новикова Б. А. и книге Душана Петковича Microsoft SQL Server 2012.

SQL — Транзакции

SQL — Транзакции

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

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

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

Свойства транзакций

Транзакции имеют следующие четыре стандартных свойства, обычно обозначаемых аббревиатурой ACID.

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

Бесплатный курс по PHP программированию

Освойте курс и узнайте, как создать веб-приложение на PHP с полного нуля

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

Изолированность — позволяет транзакциям работать независимо друг от друга и прозрачно.

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

Управление транзакциями

Для управления транзакциями используются следующие команды.

COMMIT — сохранить изменения.

ROLLBACK — отменить изменения.

SAVEPOINT — создает точки сохранения в группах транзакций.

SET TRANSACTION — помещает имя в транзакцию.

Команды управления транзакциями

Команды управления транзакциями используются только с командами DML, такими как — INSERT, UPDATE и DELETE. Они не могут использоваться при создании таблиц или их удалении, поскольку эти операции автоматически фиксируются в базе данных.

Команда COMMIT

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

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *