Меню
ГлавнаяОшибки

Уровни изоляции транзакций. Уровни изоляции транзакций с примерами на PostgreSQL

В стандарте SQL описывается четыре уровня изоляции транзакций - Read uncommited (Чтение незафиксированных данных), Read committed (Чтение зафиксированных данных), Repeatable read (Повторяемое чтение) и Serializable (Сериализуемость). В данной статье будет рассмотрен жизненный цикл четырёх параллельно выполняющихся транзакций с уровнями изоляции и .


Для уровня изоляции Read committed допустимы следующие особые условия чтения данных:


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


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


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

ACID или 4 свойства транзакций

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


Atomicity (атомарность) - выражается в том, что транзакция должна быть выполнена в целом или не выполнена вовсе.


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


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


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

Уровень изоляции Read Committed

По умолчанию в PostgreSQL уровень изоляции Read Committed. Такой уровень изоляции всегда позволяет видеть изменения внесённые успешно завершёнными транзакциями в оставшихся параллельно открытых транзакциях. В транзакции, работающей на этом уровне, запрос SELECT (без предложения FOR UPDATE/SHARE) видит только те данные, которые были зафиксированы до начала запроса; он никогда не увидит незафиксированных данных или изменений, внесённых в процессе выполнения запроса параллельными транзакциями. По сути запрос SELECT видит снимок базы данных в момент начала выполнения запроса. Однако SELECT видит результаты изменений, внесённых ранее в этой же транзакции, даже если они ещё не зафиксированы. Также заметьте, что два последовательных оператора SELECT могут видеть разные данные даже в рамках одной транзакции, если какие-то другие транзакции зафиксируют изменения после выполнения первого SELECT.


Суть уровня изоляции Read Committed показана на диаграмме 1.


Примечание: В таблице уже находится запись с первой версией данных (v1). Прошу воспринимать команды SELECT v1; - как команду возвращающую данные версии v1, а UPDATE v1 to v2; - как команду обновления данных с первой версии до второй.


Создадим к базе-данных 4 подключения и откроем в каждом из подключений по транзакции с уровнем изоляции Read Committed



Шаг 1. В начальный момент времени до каких-либо изменений данных всем транзакциям доступна изначальная версия данных (v1);




Шаг 4. Закрытие Первой транзакции. Все изменения сделанные в ходе её работы успешно фиксируются;


Шаг 5. После закрытия Первой транзакции (предыдущий шаг), изменения сделанные в ходе её выполнения над данными (обновление с v1 до v2) были распространены на остальные транзакции, SELECT запрос в оставшихся 3 открытых транзакциях возвращает v2 («Неповторяемое чтение», отличите уровня изоляции Read Committed от Serializable);


Шаг 6. Запрос на обновление данных во Второй транзакции до «третьей версии» успешно выполняется, но запросы на обновление данных блокируют изменяемые строки на дальнейшее их изменение, до завершения Второй транзакции;


Шаг 7. Из-за блокировки наложенной на данные в предыдущем шаге, Третья транзакция переходит в режим ожидания с запросом на удаление данных. Ожидание Третьей транзакции будет происходить до закрытия Второй транзакции;


Шаг 8. Несмотря на то, что Третья транзакция ожидает закрытия Второй, как Вторая так и Четвёртая транзакции без каких либо проблем продолжают свою работу, возвращая данные согласно своим версиям. Вторая возвращает v3, Четвёртая возвращает v2;


Шаг 9. Закрытие Второй транзакции приводит к разблокированию данных для изменения. Уровень изоляции Read Committed позволяет продолжить работу Третьей транзакции без вызова ошибки. Получив доступ на изменение новой версии данных (v3) Третья транзакция УСПЕШНО тут же их «удаляет» (отличие Read Committed от Serializable);


Шаг 10. До закрытия Третьей транзакции, данные будут удалёнными только внутри Третьей транзакции. Четвёртой транзакции до закрытия Третьей данные доступны (SELECT запрос в Четвёртой транзакции возвращает v3);


Шаг 11. Закрытие Третьей транзакции. Все изменения сделанные в ходе её работы успешно фиксируются;


Шаг 12. Запрос на получение данных в Четвёртой транзакции ничего не возвращает («Фантомное чтение», SELECT запрос возвращает 0 записей).


Примечание. На диаграмме не показано действие запроса INSERT. В рамках данного уровня изоляции, строки добавленные, например в шаге 3, в Первой транзакции, были бы ВИДНЫ остальным транзакциям после завершения Первой транзакции.


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

Уровень изоляции Serializable

Изоляция уровня Serializable обеспечивает беспрепятственный доступ к базе данных транзакциям с SELECT запросами. Но для транзакций с запросами UPDATE и DELETE, уровень изоляции Serializable не допускает модификации одной и той же строки в рамках разных транзакций. При изоляции такого уровня все транзакции обрабатываются так, как будто они все запущены последовательно (одна за другой). Если две одновременные транзакции попытаются обновить одну и туже строку, то это будет не возможно. В таком случае PostgreSQL принудит транзакцию, вторую, да и все последующие, что пытались изменить строку к отмене (откату - ROLLBACK).


Суть уровня изоляции Serializable показана на диаграмме 2.


Создадим к базе-данных 4 подключения и откроем в каждом из подключений по транзакции с уровнем изоляции Serializable



Шаг 1. Всем транзакциям доступна изначальная версия данных (v1);


Шаг 2. В ходе работы Первой транзакции данные без каких либо блокировок успешно обновляются до «второй версии» (v2);


Шаг 3. Изменения сделанные в Первой транзакции будут видны только ей самой (SELECT возвращает v2), и не будут доступны остальным транзакциям (SELECT запрос во Второй и Четвёртой транзакциях возвращает v1);


Шаг 4. Запрос на обновление данных в первой транзакции (шаг 2), блокирует обновляемые строки, и переводит в режим ожидания Вторую транзакцию с запросом на удаление данных. Блокировка транзакций на обновляемые данных будет происходить до закрытия Первой транзакции;


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


Шаг 6. Завершение Первой транзакции снимает блокировку с обновляемых данных, но в рамках уровня изоляции Serializable повторное обновление данных в параллельных транзакциях запрещено, и поэтому в ходе выполнения Второй транзакции возникает ошибка (отличие Serializable от Read Committed);


Шаг 7. Запрос SELECT во Второй транзакции становится не возможным, так как ошибка возникшая на предыдущем шаге отменяет («блокирует») транзакцию. Запрос SELECT в Третьей и Четвертой транзакциях возвращают первоначальную версию данных (v1). Несмотря на то, что Первая транзакция была завершена успешно, изменения не стали видны остальным открытым транзакциям (отличие Serializable от Read Committed). Открытие Пятой транзакции в левом верхнем окне;


Шаг 8. Закрытие Второй транзакции. Все изменения сделанные данной транзакцией будут отменены, из-за возникшей ошибки в ходе её работы;


Шаг 9. Запрос SELECT в Пятой транзакции возвращает новую версию данных (v2). Запрос SELECT в Третьей и Четвёртой транзакциях возвращают первоначальную версию данных (v1);


Шаг 10. Уровень изоляции Serializable всё также не даёт обновлять данные, запрос UPDATE в Третьей транзакции завершается не удачно, с вытекающими последствиями для хода всей транзакции (несмотря на то, что Первая транзакция уже удачно завершилась, и все внесённые ей изменения сохранены в базе данных). А вот запрос UPDATE в Пятой транзакции завершается успешно, так как она открыта после завершения Первой транзакции, и работает с новой версией данных;


Шаг 11. Закрытие Третьей транзакции. Все изменения сделанные данной транзакцией будут отменены, из-за возникшей ошибки в ходе её работы;


Шаг 12. Транзакция Четыре всё также показывает, что у транзакций с SELECT запросами никаких нет проблем, а Пятая транзакция получает уже обновлённые же собой данные (v5).


Примечание. На диаграмме не показано действие запроса INSERT. В рамках данного уровня изоляции, строки добавленные, например в шаге 3, в Первой транзакции, были бы НЕ ДОСТУПНЫ Второй, Третьей и Четвёртой транзакциям после завершения Первой транзакции. Также на диаграмме не показан результат ROLLBACK (Шаги 8 и 11). В случае если бы Вторая и Третья транзакции делали какие либо изменения над не заблокированными данными, то все эти изменения не были бы зафиксированы, так как транзакции завершаются неудачно (суть свойства - Atomicity).


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


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

Аномалия сериализации (Потерянное обновление)

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


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


Документация на сайте PostgreSQL PRO пишет, что Read Committed допускает «Serialization Anomaly» . Отечественная Wikipedia, не настаивая на то, что таблица относится именно к PostgreSQL, пишет, что Read Commited предотвращает аномалию сериализации . Английская Википедиа о таком феномене чтения данных умалчивает . Но немецкая Википедия приводит в своей версии таблицы феномен «Lost Updates» , указывая на то, что Read Committed может быть не подвержен потере обновлений с дополнительной защитой через курсор (Cursor Stability). Украинская Википедия поддерживает русскоязычную версию статьи, испанская Википедия поддерживает английскую версию статьи. Англоязычная документация по PostgreSQL не отличается от документации с сайта PostgreSQL PRO.


Cursor Stability расширяет блокировочное поведение уровня READ COMMITED для SQL-курсоров, добавляя новую операцию чтения (Fetch) по курсору rc (означает read cursor, т.е. чтение по курсору) и требуя, чтобы блокировка устанавливалась на текущем элементе курсора. Блокировка удерживается до тех пор, пока курсор не будет перемещен (пока не измениться его текущий элемент) или закрыт, возможно, операцией фиксации. Естественно, транзакция, читающая по курсору, может изменить текущую строку (wc – запись по курсору), и в этом случае блокировка по записи этой строки будет сохраняться до тех пор, пока транзакция не зафиксируется, даже после передвижения курсора с последующей выборкой следующей строки.

Вот такой результат получился в PostgreSQL 9.6


Заключение

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

Литература

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

Управляет поведением блокировки и версиями строк инструкций Transact-SQL, выданных при соединении с SQL Server.

Синтаксис

SET TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SNAPSHOT | SERIALIZABLE } [ ; ]

Аргументы

    READ UNCOMMITTED
    Указывает, что инструкции могут считывать строки, которые были изменены другими транзакциями, но еще не были зафиксированы.

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

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

    • уровня изоляции READ COMMITTED с параметром базы данных READ_COMMITTED_SNAPSHOT, находящимся в состоянии ON;

      уровня изоляции моментального снимка (SNAPSHOT).

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

    Поведение READ COMMITTED зависит от настройки аргумента базы данных READ_COMMITTED_SNAPSHOT.

    • Если параметр READ_COMMITTED_SNAPSHOT находится в состоянии OFF (по умолчанию), компонент Компонент Database Engine при выполнении операций считывания текущей транзакцией использует совмещаемые блокировки для предотвращения изменения строк другими транзакциями. Совмещаемые блокировки также блокируют инструкции от считывания строк, измененных другими транзакциями, пока не завершится другая транзакция. От типа совмещаемой блокировки зависит время ее освобождения. Блокировка строки освобождается перед обработкой следующей строки. Блокировка строки освобождается при чтении следующей страницы, а блокировка таблицы освобождается при завершении выполнения инструкции.

      Примечание

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

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

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

    Примечание

    При установке параметра READ_COMMITTED_SNAPSHOT разрешается только то соединение с базой данных, которое выполняет команду ALTER DATABASE.До завершения инструкции ALTER DATABASE в базе данных не должно быть других открытых соединений.База данных не обязательно должна находиться в однопользовательском режиме.

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

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

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

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

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

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

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

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

    Примечание

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

    SERIALIZABLE
    Указывает следующее.

    • Инструкции не могут считывать данные, которые были изменены другими транзакциями, но еще не были зафиксированы.

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

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

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

Замечания

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

Уровни изоляции транзакции определяют тип блокировки, применяемый к операциям считывания. Совмещаемые блокировки, применяемые для READ COMMITTED или REPEATABLE READ, как правило, являются блокировками строк, но при этом, если в процессе считывания идет обращение к большому числу строк, блокировка строк может быть расширена до блокировки страниц или таблиц. Если строка была изменена транзакцией после считывания, для защиты такой строки транзакция применяет монопольную блокировку, которая сохраняется до завершения транзакции. Например, если транзакция REPEATABLE READ имеет разделяемую блокировку строки и при этом изменяет ее, совмещаемая блокировка преобразуется в монопольную.

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

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

Если инструкция SET TRANSACTION ISOLATION LEVEL использовалась в хранимой процедуре или триггере, то при возврате управления из них уровень изоляции будет изменен на тот, который действовал на момент их вызова. Например, если уровень изоляции REPEATABLE READ устанавливается в пакете, а пакет затем вызывает хранимую процедуру, которая меняет уровень изоляции на SERIALIZABLE, при возвращении хранимой процедурой управления пакету, настройки уровня изоляции меняются назад на REPEATABLE READ.

Примечание

Определяемые пользователем функции и типы данных среды CLR не могут выполнять инструкцию SET TRANSACTION ISOLATION LEVEL.Однако уровень изоляции можно переопределить с помощью табличного указания.Дополнительные сведения см. в разделе Табличные указания (Transact-SQL) .

Если для привязки двух сеансов используется процедура sp_bindsession, каждый сеанс сохраняет свои настройки уровня изоляции. Применение инструкции SET TRANSACTION ISOLATION LEVEL для изменения настройки уровня изоляции одного сеанса не повлияет на настройки других сеансов, привязанных к нему.

Инструкция SET TRANSACTION ISOLATION LEVEL работает во время выполнения, но не во время синтаксического анализа.

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

    READ UNCOMMITTED

    READ COMMITTED с использованием управления версиями строк

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

Базы данных с поддержкой FILESTREAM поддерживают следующие уровни изоляции транзакций.

Примеры

В следующем примере устанавливается уровень изоляции TRANSACTION ISOLATION LEVEL для сеанса. Для каждой последующей инструкции Transact-SQLSQL Server сохраняет все совмещаемые блокировки до конца транзакции.

USE AdventureWorks2012; GO SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; GO BEGIN TRANSACTION; GO SELECT * FROM HumanResources.EmployeePayHistory; GO SELECT * FROM HumanResources.Department; GO COMMIT TRANSACTION; GO

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

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

Проблемы одновременного конкурентного доступа

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

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

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

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

Компонент Database Engine и уровни изоляции

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

    READ UNCOMMITTED

Уровни изоляции READ UNCOMMITTED, REPEATABLE READ и SERIALIZABLE доступны только в пессимистической модели одновременного конкурентного доступа, тогда как уровень SNAPSHOT доступен только в оптимистической модели одновременного конкурентного доступа. Уровень изоляции READ COMMITTED доступен в обеих моделях. Далее рассмотрены четыре уровня изоляции, которые доступны только в пессимистической модели, а уровень SNAPSHOT описан в следующей статье.

Уровень изоляции READ UNCOMMITTED

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

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

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

Уровень изоляции READ COMMITTED

Как уже упоминалось, уровень READ COMMITTED имеет две формы. Первая форма применяется в пессимистической модели одновременного конкурентного доступа, а вторая - в оптимистической. В этом разделе рассматривается первая форма этого уровня изоляции.

Транзакция, которая читает строку и использует уровень изоляции READ COMMITTED, выполнят проверку только на наличие монопольной блокировки для данной строки. Если такая блокировка отсутствует, транзакция извлекает строку. (Это выполняется с использованием разделяемой блокировки.) Таким образом предотвращается чтение транзакцией данных, которые не были подтверждены и которые могут быть позже отменены. После того, как данные были прочитаны, их можно изменять другими транзакциями.

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

Уровень изоляции READ COMMITTED для компонента Database Engine является уровнем изоляции по умолчанию.

Уровень изоляции REPEATABLE READ

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

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

Уровень изоляции SERIALIZABLE

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

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

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

Установка и редактирование уровня изоляции

Уровень изоляции можно установить, используя следующие средства:

    параметр TRANSACTION ISOLATION LEVEL инструкции SET;

    подсказки уровня изоляции.

Параметру TRANSACTION ISOLATION LEVEL можно присвоить пять постоянных значений, которые имеют такие же имена и смысл, как и только что рассмотренные уровни изоляции. Предложение FROM инструкции SELECT также поддерживает эти подсказки уровней изоляции. Задание уровня изоляции в предложении FROM инструкции SELECT перекрывает текущее его значение, установленное инструкцией SET TRANSACTION ISOLATION LEVEL.

Инструкция DBCC USEROPTIONS возвращает информацию о текущих значениях параметров инструкции SET, включая значение уровня изоляции, которое возвращается в параметре ISOLATION LEVEL.


С приходом master-master репликаций остро встает вопрос о целостность с достоверностью базы данных.
  • Целостность базы данных - соответствие имеющейся в базе данных информации её внутренней логике, структуре и всем явно заданным правилам.
  • Достоверность (или истинность) - соответствие фактов, хранящихся в базе данных, реальному миру

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

Например:

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

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

Механизмы блокировок

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

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

Основными видами блокировок являются на чтение и на запись:

  • Если клиент хочет читать данные, то другие клиенты тоже могут читать данные, но никто не может записывать, пока первый клиент не закончит чтение (read lock ).
  • Если клиент хочет записать данные, то другие клиенты не должны ни читать ни писать эти данные пока первый клиент не закончит (write lock ).

Блокировка может быть наложена явно или неявно .

Если клиент не назначает блокировку, MySQL сервер неявно устанавливает необходимый тип блокировки на время выполнения выражения или транзакции. В случае выполнения оператора SELECT сервер установит READ LOCK, а в случае UPDATE - WRITE LOCK. При неявной блокировке уровень блокировки зависит от типа хранилища данных: для MyISAM, MEMORY и MERGE блокируется вся таблица, для InnoDB - только используемые в выражении строки (в случае, если набор этих строк может быть однозначно определен - иначе, блокируется вся таблица).

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

Изоляция транзакций

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

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

1) Потерянное обновление (англ. lost update)

При одновременном изменении одного блока данных разными транзакциями, одно из изменений теряется;

Имеются две транзакции, выполняемые одновременно:

Транзакция 1 Транзакция 2
UPDATE tbl1 SET f2=f2+20 WHERE f1=1; UPDATE tbl1 SET f2=f2+25 WHERE f1=1;

В обеих транзакциях изменяется значение поля f2, при этом одно из изменений теряется. Так что, f2 будет увеличено не на 45, а только на 20 или 25.

Причина :

  1. Первая транзакция прочитала текущее состояние поля.
  2. Вторая транзакция сделала свои изменения, основываясь на своих сохраненных в память данных.
  3. Первая делает обновление поля, используя свои «старые» данные.

2) «Грязное» чтение (англ. dirty read)

Чтение данных, добавленных или изменённых транзакцией, которая впоследствии не подтвердится (откатится);

Транзакция 1 Транзакция 2
SELECT f2 FROM tbl1 WHERE f1=1;
ROLLBACK WORK;

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

3) Неповторяющееся чтение (англ. non-repeatable read)

При повторном чтении в рамках одной транзакции, ранее прочитанные данные оказываются изменёнными.

Предположим, имеются две транзакции, открытые различными приложениями, в которых выполнены следующие SQL-операторы:

Транзакция 1 Транзакция 2
SELECT f2 FROM tbl1 WHERE f1=1; SELECT f2 FROM tbl1 WHERE f1=1;
UPDATE tbl1 SET f2=f2+1 WHERE f1=1;
COMMIT;
SELECT f2 FROM tbl1 WHERE f1=1;

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

4) Фантомное чтение (англ. phantom reads)

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

Транзакция 1 Транзакция 2
SELECT SUM(f2) FROM tbl1;
INSERT INTO tbl1 (f1,f2) VALUES (15,20);
COMMIT;
SELECT SUM(f2) FROM tbl1;

В транзакции 2 выполняется SQL-оператор, использующий все значения поля f2. Затем в транзакции 1 выполняется вставка новой строки, приводящая к тому, что повторное выполнение SQL-оператора в транзакции 2 выдаст другой результат. Такая ситуация называется фантомным чтением. От неповторяющегося чтения оно отличается тем, что результат повторного обращения к данным изменился не из-за изменения/удаления самих этих данных, а из-за появления новых (фантомных) данных.

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

Serializable (упорядочиваемость)

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

Repeatable read (повторяемость чтения)

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

Read committed (чтение фиксированных данных)

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

Read uncommitted (чтение незафиксированных данных)

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

Поведение при различных уровнях изолированности