2 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

На пути к правильным SQL транзакциям

Содержание

Транзакции

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

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

В этой статье показано, как проблемы, связанные с одновременным конкурентным доступом, можно решить посредством транзакций. Здесь дается вводное представление о свойствах транзакций, называемых свойствами ACID (Atomicity, Consistency, Isolation, Durability — атомарность, согласованность, изолированность, долговечность), обзор инструкций языка Transact-SQL, применяемых для работы с транзакциями, и введение в журналы транзакций.

Модели одновременного конкурентного доступа

Компонент Database Engine поддерживает две разные модели одновременного конкурентного доступа:

пессимистический одновременный конкурентный доступ;

оптимистический одновременный конкурентный доступ.

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

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

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

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

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

Неявная транзакция — задает любую отдельную инструкцию INSERT, UPDATE или DELETE как единицу транзакции.

Явная транзакция — обычно это группа инструкций языка Transact-SQL, начало и конец которой обозначаются такими инструкциями, как BEGIN TRANSACTION, COMMIT и ROLLBACK.

Понятие транзакции лучше всего объяснить на примере. Допустим, в базе данных SampleDb сотруднику «Василий Фролов» требуется присвоить новый табельный номер. Этот номер нужно одновременно изменить в двух разных таблицах. В частности, требуется одновременно изменить строку в таблице Employee и соответствующие строки в таблице Works_on. Если обновить данные только в одной из этих таблиц, данные базы данных SampleDb будут несогласованны, поскольку значения первичного ключа в таблице Employee и соответствующие значения внешнего ключа в таблице Works_on не будут совпадать. Реализация этой транзакции посредством инструкций языка Transact-SQL показана в примере ниже:

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

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

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

Транзакции обладают следующими свойствами, которые все вместе обозначаются сокращением ACID (Atomicity, Consistency, Isolation, Durability):

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

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

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

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

Инструкции Transact-SQL и транзакции

Для работы с транзакциями язык Transact-SQL предоставляет некоторые инструкции. Инструкция BEGIN TRANSACTION запускает транзакцию. Синтаксис этой инструкции выглядит следующим образом:

В параметре transaction_name указывается имя транзакции, которое можно использовать только в самой внешней паре вложенных инструкций BEGIN TRANSACTION/COMMIT или BEGIN TRANSACTION/ROLLBACK. В параметре @trans_var указывается имя определяемой пользователем переменной, содержащей действительное имя транзакции. Параметр WITH MARK указывает, что транзакция должна быть отмечена в журнале. Аргумент description — это строка, описывающая эту отметку. В случае использования параметра WITH MARK требуется указать имя транзакции.

Инструкция BEGIN DISTRIBUTED TRANSACTION запускает распределенную транзакцию, которая управляется Microsoft Distributed Transaction Coordinator (MS DTC — координатором распределенных транзакций Microsoft). Распределенная транзакция — это транзакция, которая используется на нескольких базах данных и на нескольких серверах. Поэтому для таких транзакций требуется координатор для согласования выполнения инструкций на всех вовлеченных серверах. Координатором распределенной транзакции является сервер, запустивший инструкцию BEGIN DISTRIBUTED TRANSACTION, и поэтому он и управляет выполнением распределенной транзакции.

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

Язык Transact-SQL также поддерживает инструкцию COMMIT TRANSACTION, которая функционально равнозначна инструкции COMMIT WORK, с той разницей, что она принимает определяемое пользователем имя транзакции. Инструкция COMMIT TRANSACTION является расширением языка Transact-SQL, соответствующим стандарту SQL.

В противоположность инструкции COMMIT WORK, инструкция ROOLBACK WORK сообщает о неуспешном выполнении транзакции. Программисты используют эту инструкцию, когда они полагают, что база данных может оказаться в несогласованном состоянии. В таком случае выполняется откат всех произведенных инструкциями транзакции изменений. Инструкция ROOLBACK WORK является стандартной формой этой инструкции. Использовать предложение WORK не обязательно. Язык Transact-SQL также поддерживает инструкцию ROLLBACK TRANSACTION, которая функционально равнозначна инструкции ROOLBACK WORK, с той разницей, что она принимает определяемое пользователем имя транзакции.

Инструкция SAVE TRANSACTION устанавливает точку сохранения внутри транзакции. Точка сохранения (savepoint) определяет заданную точку в транзакции, так что все последующие изменения данных могут быть отменены без отмены всей транзакции. (Для отмены всей транзакции применяется инструкция ROLLBACK.) Инструкция SAVE TRANSACTION в действительности не фиксирует никаких выполненных изменений данных. Она только создает метку для последующей инструкции ROLLBACK, имеющей такую же метку, как и данная инструкция SAVE TRANSACTION.

Использование инструкции SAVE TRANSACTION показано в примере ниже:

Единственной инструкцией, которая выполняется в этом примере, является первая инструкция INSERT. Для третьей инструкции INSERT выполняется откат с помощью инструкции ROLLBACK TRANSACTION b, а для двух других инструкций INSERT будет выполнен откат инструкцией ROLLBACK TRANSACTION a.

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

Как вы уже знаете, каждая инструкция Transact-SQL всегда явно или неявно принадлежит к транзакции. Для удовлетворения требований стандарта SQL компонент Database Engine предоставляет поддержку неявных транзакций. Когда сеанс работает в режиме неявных транзакций, выполняемые инструкции неявно выдают инструкции BEGIN TRANSACTION. Это означает, что для того чтобы начать неявную транзакцию, пользователю или разработчику не требуется ничего делать. Но каждую неявную транзакцию нужно или явно зафиксировать или явно отменить, используя инструкции COMMIT или ROLLBACK соответственно. Если транзакцию явно не зафиксировать, то все изменения, выполненные в ней, откатываются при отключении пользователя.

Читать еще:  Как сделать нагрузку для проверки бп?

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

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

Начало явной транзакции помечается инструкцией BEGIN TRANSACTION, а окончание — инструкцией COMMIT или ROLLBACK. Явные транзакции можно вкладывать друг в друга. В таком случае, каждая пара инструкций BEGIN TRANSACTION/COMMIT или BEGIN TRANSACTION/ROLLBACK используется внутри каждой такой пары или большего количества вложенных транзакций. (Вложенные транзакции обычно используются в хранимых процедурах, которые сами содержат транзакции и вызываются внутри другой транзакции.) Глобальная переменная @@trancount содержит число активных транзакций для текущего пользователя.

Инструкции BEGIN TRANSACTION, COMMIT и ROLLBACK могут использоваться с именем заданной транзакции. (Именованная инструкция ROLLBACK соответствует или именованной транзакции, или инструкции SAVE TRANSACTION с таким же именем.) Именованную транзакцию можно применять только в самой внешней паре вложенных инструкций BEGIN TRANSACTON/COMMIT или BEGIN TRANSACTION/ROLLBACK.

Журнал транзакций

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

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

Журнал транзакций применяется для отката или восстановления транзакции. Если в процессе выполнения транзакции еще до ее завершения возникает ошибка, то система использует все существующие в журнале транзакций исходные значения записей (которые называются исходными образами записей (before image)), чтобы выполнить откат всех изменений, выполненных после начала транзакции. Процесс, в котором исходные образы записей из журнала транзакций используются для отката всех изменений, называется операцией отмены записей (undo activity).

В журналах транзакций также сохраняются преобразованные образы записей (after image). Преобразованные образы — это модифицированные значения, которые применяются для отмены отката всех изменений, выполненных после старта транзакции. Этот процесс называется операцией повторного выполнения действий (redo activity) и применяется при восстановлении базы данных.

Каждой записи в журнале транзакций присваивается однозначный идентификатор, называемый порядковым номером журнала транзакции (log sequence number — LSN). Все записи журнала, являющиеся частью определенной транзакции, связаны друг с другом, чтобы можно было найти все части этой транзакции для операции отмены или повтора.

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

Дата публикации: 2017-12-11

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

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

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

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

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

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

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

Освойте курс и узнайте, как создать динамичный сайт на PHP и MySQL с полного нуля, используя модель MVC

В курсе 39 уроков | 15 часов видео | исходники для каждого урока

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

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

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

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

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

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

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

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

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

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

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

Команда COMMIT

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

На пути к правильным SQL транзакциям (Часть 2)

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

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

  • Основанный на блокировке ресурсов
  • Основанный на создании версионной копии ресурсов.

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

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

Организация памяти в MS SQL

Все данные в базе разбиты на страницы, которые в свою очередь формируют экстенты.

Страница

Страница – занимает 8Kb, первые 96 байт из которых являются заголовком и содержат описание страницы.
Типы страниц (отличаются по типу хранимой информации):

  • Data — данные таблицы (за исключением колонок переменного и очень большого размера);
  • Index – индексы;
  • Text/Image – колонки переменного и очень большого размера
  • Другие типы, содержащие вспомогательную информацию

Экстент

Экстент – основная единица управления пространством (64Kb), содержащая последовательные 8-м страниц (8*8Kb). Бывают однородные (Uniform) и смешанные (Mixed) экстенты. В однородных содержаться страницы одного типа, а в смешанных – разного типа. По мере появления новых страниц одного типа в составе смешанных экстентов, сервер старается создавать однородные экстенты, перемещая страницы между смешанными экстентами.
Теперь, зная, как организована память в сервере, можно ввести первую классификацию блокировок, а именно по типу блокируемого ресурса.

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

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

Эскалация блокировок

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

Разновидности блокировок по режиму блокирования

Совмещаемая (Shared) блокировка

Используются для операций считывания (SELECT) и предотвращают изменение (UPDATE, DELETE) заблокированного ресурса. Как следует из названия, данная блокировка может быть совмещена с другими блокировками (совмещаемыми или блокировками обновления, описаны ниже). Это значит, что если транзакция T1 считывает данные и устанавливает совмещаемые блокировки на считываемые строки, то другая транзакция T2 может так же установить блокировки на те же строки, не дожидаясь снятия блокировок транзакцией T1.
В зависимости от уровня изоляции транзакции, блокировка может быть снята, как только данные считаны (Read Committed), либо же удерживаться до конца транзакции (Repeatable Read и выше). Так же блокировку можно удерживать до конца транзакции, указав в запросе соответствующие табличные подсказки (например, HOLDLOCK, SERIALIZABLE и т.д.)
В случае, когда блокировки снимаются по мере чтения данных, они могут быть сняты даже до момента завершения запроса (SELECT). Т.е. если мы выбираем 10 строк и установлено 10 совмещаемых блокировок уровня строки, то, как только считаны данные первой строки, её блокировка снимается, не дожидаясь считывания оставшихся 9 строк.

Монопольная (Exclusive) блокировка

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

Блокировка обновления (Update)

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

  1. Поиск данных для обновления
  2. Обновление найденных данных.

Мы уже знаем, что для корректного обновления данных необходимо установить монопольную (exclusive) блокировку. Но, если монопольная блокировка будет установлена с самого начала выполнения запроса (на этапе поиска данных), то мы сделаем невозможным даже чтение данных из других транзакций. Поэтому на первом этапе (поиск данных) лучше будет установить разделяемую блокировку и только если данные найдены, то преобразовать её в монопольную и произвести изменение. Это позволит другим транзакциям избежать ожидания при чтении данных, пока транзакция ищет данные для обновления.
Вроде всё отлично с предлагаемым подходом. Создаём разделяемую блокировку на первом этапе и преобразуем её в монопольную на втором. Производительность улучшена и все счастливы. Но увы тут есть подвох. Если, по описанному выше алгоритму, две различные транзакции будут одновременно пытаться произвести обновление одних и тех же данных, то мы гарантированно получаем взаимоблокировку (Deadlock). Ниже показано как она возникает.

Читать еще:  Пять анонимных поисковых систем — альтернатив Google и Яндекс

Поэтому потребовался новый режим блокировки – блокировка обновления (Update). Он ведёт себя как что-то среднее между совмещаемой (Shared) и монопольной (Exclusive) блокировкой. «Монопольность» заключается в том, что на ресурсе может быть только одна блокировка обновления, а «совмещаемость» в том, что на этапе поиска данных блокировка может совмещаться с другими совмещаемыми блокировками.

Примечание: блокировка обновления (update) используется не только для операций UPDATE, но и для удаления данных (DELETE) на этапе поиска удаляемых данных. В случае вставки новых строк в таблицу с кластерным индексом INSERT, данный тип блокировки также применим на этапе поиска правильного положения в индексе; когда положение найдено, блокировка обновления (update) преобразуется в монопольную блокировку индекса и происходит вставка новой строки.

Блокировки с намерением (Intent)

Данный тип блокировок не представляет собой особый режим. Он служит для оптимизации работы алгоритма установки блокировок, описанных выше.
В основе лежит простая идея. Перед установкой низкоуровневых блокировок (уровня строки или страницы), мы всегда сначала устанавливаем блокировку намерения (Intent) на более высоком уровне – на уровне таблицы. Если такой блокировки нет, то мы можем избежать проверки наличия уже существующих блокировок на интересующих нас ресурсах (строках, страницах) и сразу их установить. Если она есть, то можно более оптимально принять решение о возможности установки определённой блокировки низкого уровня. Например, если существует совмещаемая блокировка с намерением, то нет смысла пытаться получить эксклюзивную блокировку на уровне таблицы.
В зависимости от типа низкоуровневых блокировок можно выделить следующие типы блокировок с намерением:

  • Блокировка с намерением совмещаемого доступа (IS)
  • Блокировка с намерением монопольного доступа (IX)
  • Совмещаемая блокировка с намерением монопольного доступа (SIX)
  • Блокировка с намерением обновления (IU)
  • Совмещаемая блокировка с намерением обновления (SIU)
  • Блокировка обновления с намерением монопольного доступа (UIX)

Блокировки схем (Schema)

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

  • Блокировка изменения схемы (Sch-M) – устанавливается при обновлении схемы таблицы и на время существования запрещает любой доступ к данным таблицы
  • Блокировка стабильности схемы (Sch-S) – устанавливается при выполнении запросов; данная блокировка добавляется ко всем объектам схемы (схема таблицы, индексы и т.п.), которые задействованы в запросе и на время существования предотвращает их изменение

Блокировка массового обновления (Bulk update)

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

  • указана подсказка TABLOCK
  • установлен параметр «блокировка таблицы при массовой загрузке (table lock on bulk load)» при помощи хранимой процедуры sp_tableoption

Блокировка диапазона ключа (Range)

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

Совместимость блокировок

Как уже упоминалось выше, некоторые типы блокировки могут быть успешно установлены на одном и том же ресурсе. Другие же, напротив, будут ожидать завершение всех блокировок. Ниже представлена полная матрица совместимости, которая показывает, может ли конкретный тип блокировки быть установлен, при наличии уже имеющихся блокировок.
Например, нас интересует может ли быть установлена разделяемая блокировка (Shared), если на ресурсе установлена монопольная блокировка (Exclusive). Для этого мы находим строку, соответствующую запрашиваемой блокировке (выделена синей рамкой) и находим значение в соответствующем столбце (выделено красной рамкой). В нашем примере мы видим значение «К», что говорит о конфликте, т.е. разделяемая блокировка (S) будет вынуждена ждать, пока с ресурса не будет снята монопольная блокировка (X).

Заключение

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

SQL – Транзакция

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

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

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

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

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

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

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

  • COMMIT – для сохранения изменений.
  • ROLLBACK – откат изменений.
  • SAVEPOINT – создает точки внутри групп операций, которые следует откатить.
  • SET TRANSACTION – размещает имя транзакции.

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

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

Команда COMMIT

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

Синтаксис команды COMMIT выглядит следующим образом:

Рассмотрим таблицу клиентов, имеющих следующие записи:

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

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

Команда ROLLBACK

Команда ROLLBACK является транзакционной командой и используется для отмены операций, которые еще не были сохранены в базе данных. Эта команда может быть использована только для отката транзакции после последних команд commit или rollback.

Синтаксис команды ROLLBACK выглядит следующим образом:

Рассмотрим таблицу клиентов, имеющую следующие записи:

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

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

Команда SAVEPOINT

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

Синтаксис команды SAVEPOINT является таким, как показано ниже:

Эта команда служит только в создании SAVEPOINT среди всех транзакционных заявлений. Команда ROLLBACK используется для отмены группы операций.

Синтаксис для отката SAVEPOINT является таким, как показано ниже:

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

Рассмотрим таблицу клиентов, имеющих следующие записи:

Следующий блок кода содержит ряд операций.

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

Обратите внимание на то, что только первое удаление имело место, так как вы откатили к SP2.

Команда RELEASE SAVEPOINT

Команда RELEASE SAVEPOINT используется для удаления SAVEPOINT, который вы создали.

Синтаксис команды RELEASE SAVEPOINT выглядит следующим образом.

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

Команда SET TRANSACTION

Команда SET TRANSACTION может быть использована для инициирования транзакции базы данных. Эта команда используется для определения характеристик для транзакции, которая следует. Например, вы можете указать транзакцию только для чтения или чтения и записи.

Синтаксис команды SET TRANSACTION выглядит следующим образом.

Если вы нашли ошибку, пожалуйста, выделите фрагмент текста и нажмите Ctrl+Enter.

Управление транзакциями в среде MS SQL Server;

Сайт СТУДОПЕДИЯ проводит ОПРОС! Прими участие 🙂 — нам важно ваше мнение.

ELSE

COMMIT TRAN

END

ROLLBACK TRAN

BEGIN

END

ROLLBACK TRAN

BEGIN

BEGIN TRAN

IF (SELECT COUNT(Поставки.Количество_ед_товара)

FROM Товары INNER JOINПоставки

ONТовары. ID_товара = Поставки.Товар

WHERE Товары.Название_товара = ‘Кофе Ирландский крем’) >=400

RETURN

UPDATE Продажи

SET Цена_ед_товара = Цена_ед_товара + 500

FROMТовары INNER JOINПродажи

ONТовары. ID_товара = Продажи.Товар

WHERE Товары.Название_товара = ‘Кофе Ирландский крем’

— анализ кода ошибки производится при обновлении таблицы

IF @@error <>0

RETURN

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

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

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

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

Если во время транзакции произойдут ошибки (или если этого требует логи­ка программы), выполняется команда rollback tran.

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

Примечание

Команда rollback tran не влияет на последовательность выполнения ко­манд программы. Следовательно, после отката необходимо выполнить коман­ду RETURN.

Пример 2. В этом примере иллюстрируется последова­тельность выполнения транзакций.

BEGIN TRAN Tr1

INSERT MyTable VALUES (1)

PRINT @@TRANCOUNT — значение = 1

SAVE TRAN Point_1

INSERT MyTable VALUES (2) — здесь может быть любой SQL-код

——— Если возникла ошибка, откат к точке Point_1

Читать еще:  Установка cwm recovery с помощью ПК

IF @@error <> 0

ROLLBACK TRAN Point_1

———- Если кол-во записей >3, осуществляется откат к началу транзакции

IF (SELECT COUNT(*)FROM MyTable) > 3

ROLLBACK TRANTr1

INSERT MyTable VALUES (3) — здесь может быть любой SQL-код

——- Если возникла ошибка, откат к началу транзакции

IF @@error <> 0

ROLLBACK TRAN Tr1

COMMIT TRAN Tr1

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

Команда save tran используется в качестве «закладки». Она не влияет на значение @@trancount и не изменяет уровня вложенности транзакции, она лишь позволяет позднее вернуть транзакцию к этой точке.

Серверзапоминает факт начала транзакции, обновляя глобальную перемен­ную @@trancount. Вначале переменная @@trancount равна 0. Она увеличива­ется на 1 для каждой новой вложенной транзакции.

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

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

Пользователи в основном должны указывать только начало и конец транзакции, используя команды SQL или API (прикладного интерфейса программирования).

Если пользователь попытается установить соединение снова и продолжить выполнение транзакции, то это не удастся.

SQL Server поддерживает три вида определения транзакций:

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

При этом если отдельная команда выполнена успешно, то ее изменения фикси­руются. Если при выполнении команды произошла ошибка, то сделан­ные изменения отменяются и система возвращается в первоначальное состояние.

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

Установка режима явного опре­деления транзакций выполняется посредством другой команды:

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

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

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

Незавершенное (черновое) чтение (read uncommitted) — минимальный уровень изоляции гарантирует только физическую целостность при записи данных. Процессы-читатели могут считывать данные незавершенной транзакции процесса-писателя.

Подтвержденное чтение (read committed) — процессы-читатели не могут считывать данные незавершенной транзакции, но процессы-писатели могут изменять уже прочитанные читателем данные.

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

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

Моментальный срез (snapshot) — данный вид изоляции не входит в рекомендации стандарта SQL 92, но он реализован во многих СУБД. Процессы-читатели не ждут завершения транзакций писателей, а считывают данные, точнее их версию, по состоянию на момент начала своей транзакции.

Испытания

Рассмотрим поведение системы в типовых случаях на простом примере. Для проведения эксперимента воспользуемся СУБД MS SQL Server 2005 или 2008.

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

Подготовка

USE master
ALTER DATABASE Test SET ALLOW_SNAPSHOT_ISOLATION ON
GO
USE test

Создаем «подопытные» таблицы для тестов и заполняем их данными. Предположим, что мы собираем поступающую с датчиков информацию в таблицу DevicesData. Поле DeviceId содержит идентификатор устройства, а поле Value — последнее полученное значение.

CREATE TABLE DevicesData (
DeviceId int not null,
Value int not null, CONSTRAINT PK_DevicesData PRIMARY KEY (DeviceId)
)
GO

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

TRUNCATE TABLE DevicesData
DECLARE @n int
SET @n = 999
DECLARE @List TABLE (n int)
WHILE @n >= 0 BEGIN
INSERT INTO @List (n)
SELECT @n
SET @n = @n — 1
END
INSERT INTO DevicesData (DeviceId, Value)
SELECT A.n * 1000 + B.n, 0
FROM @List A CROSS JOIN @List B
GO

Проверка

В SQL Server Management Studio откроем два окна для запросов к нашей базе данных Test.

Подтвержденное чтение (read committed)

Установим для каждого процесса уровень изоляции READ COMMITTED. В первом окне запустим процесс-писатель, который меняет значение поля Value у двух случайным образом выбранных записей в таблице. Первое значение увеличивается на 1, второе уменьшается на 1. Изначально все значения поля Value в таблице равны нулю, и значит, их сумма также будет равна нулю. Следовательно, после завершения каждой транзакции сумма значений поля Value в таблице будет оставаться равной нулю. Во втором окне запустим процесс-читатель, подсчитывающий эту самую сумму значений поля Value.

Процесс 1 (писатель)

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
DECLARE @Id int
WHILE 1 = 1 BEGIN
SET @Id = 500000 * rand()
BEGIN TRANSACTION
UPDATE DevicesData
SET Value = Value + 1
WHERE DeviceId = @Id
UPDATE DevicesData
SET Value = Value — 1
WHERE DeviceId = 500000 + @Id
COMMIT
WAITFOR DELAY ’00:00:00.100′
END

Процесс 2 (читатель)

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SELECT SUM(Value) FROM DevicesData

Нетрудно убедиться, что выбранный уровень не обеспечивает логической целостности. Если при запущенном «Процессе 1» в другом окне вручную запустить несколько раз «Процесс 2», то возвращаемые выборкой значения будут отличаться от нуля. Если же «Процесс 1» прервать, то «Процесс 2» снова покажет нулевую сумму.

Повторяемое чтение (repeatable read)

Чтобы избежать подобной проблемы, повысим уровень до повторяемого чтения, установив REPEATABLE READ. Повторив наш предыдущий эксперимент, легко убедиться в этом: процесс-читатель всегда возвращает нулевую сумму. Если же посмотреть накладываемые сервером блокировки (EXEC sp_lock), то можно увидеть многочисленные разделяемые блокировки уровня страниц (page shared lock). По сути, на запись блокируется вся таблица.

Процесс 1 (писатель)

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
DECLARE @Id int
WHILE 1 = 1 BEGIN
SET @Id = 500000 * rand()
BEGIN TRANSACTION
UPDATE DevicesData
SET Value = Value + 1
WHERE DeviceId = @Id
UPDATE DevicesData
SET Value = Value — 1
WHERE DeviceId = 500000 + @Id
COMMIT
WAITFOR DELAY ’00:00:00.100′
END

Процесс 2 (читатель)

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRANSACTION
SELECT SUM(Value) FROM DevicesData
EXEC sp_lock
COMMIT

Моментальный срез (snapshot)

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

Снова повторяемое чтение и фантомы

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

Процесс 1 (читатель)

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRANSACTION
SELECT SUM(Value) FROM DevicesData WHERE DeviceId > 999000
WAITFOR DELAY ’00:00:03′
SELECT SUM(Value) FROM DevicesData WHERE DeviceId > 999000
COMMIT

Процесс 1. Результат — 0 — 111

Процесс 2 (писатель)

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRANSACTION
INSERT INTO DevicesData (DeviceId, Value) VALUES (1000000, 111)
WAITFOR DELAY ’00:00:00.100′
COMMIT

Чтобы не блокировать таблицу, запустим читателя («Процесс 1») на небольшом диапазоне записей, а в паузе между двумя чтениями этого диапазона запустим «Процесс 2», добавляющий новую запись. В результате первая выборка вернет 0, а вторая — 111.

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

Процесс 1 (писатель)

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
DECLARE @SumVal int
BEGIN TRANSACTION
SELECT @SumVal = SUM(Value) FROM DevicesData WHERE DeviceId > 999000
WAITFOR DELAY ’00:00:03′
IF (@SumVal) = 0
INSERT INTO DevicesData (DeviceId, Value) VALUES (1000000, 111)
COMMIT

Процесс 1. Результат

Msg 2627, Level 14, State 1, Line 18
Violation of PRIMARY KEY constraint ‘PK__DevicesData__51BA1E3A’. Cannot insert duplicate key in object ‘dbo.DevicesData’.
The statement has been terminated.

Процесс 2 (писатель)

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRANSACTION
INSERT INTO DevicesData (DeviceId, Value) VALUES (1000000, 111)
WAITFOR DELAY ’00:00:00.100′
COMMIT

Сериализуемость (serializable), или полная изоляция

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

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

Если повторить наш предыдущий пример с уровнем SERIALIZABLE, то ошибка добавления записи возникнет уже в «Процессе 2».

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

Ссылка на основную публикацию
Статьи c упоминанием слов:
Adblock
detector