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

— в деталях, но простыми словами.

Rustem Mustafin

Published in

Pseudo Blog

9 min readJul 24, 2018

Если понять 50%, а запомнить хотя бы 10% этой статьи, то на интервью ответить не составит труда.

В Аквелоне мы часто готовимся (и готовим) к различным проектам и интервью. Иногда всплывают темы, которые хотелось бы знать точнее, детальнее. Именно из такого исследования принципов ACID, а именно изолированности, и родилась эта статья.

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

Зачем это надо, и как оно появилось?

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

За точку отсчёта возьмём начало семидесятых, когда Рэймонд Бойс (англ. Raymond Boyce, 1947–1974) и Дональд Чемберлин (англ. Donald D. Chamberlin) (род. 1944), работая в IBM, создают язык запросов SEQUEL (тот самые “сиквел”), позднее переименованный в SQL и впервые стандартизированный в 1986 как “SQL-86”.

Кстати, Бойс — это человек, придумавший нормальную форму Бойса-Кодда, которую мы все учили. В общем-то, и сам SQL появился, когда Рэй и Дональд узнали от Кодда про реляционную модель данных, которую тот придумал. А ещё чуть позднее, Кодд придумал NULL и его особенности, из-за которых нам теперь приходится писать IS NOT NULL.

Дональд Чемеберлен, соавтор SQL, photo by Dicklyon — Own work, CC BY-SA 4.0, https://commons.wikimedia.org/w/index.php?curid=63380467

Рэй Бойс, соавтор SQL, photo from https://alchetron.com/Raymond-F-Boyce

Тед Кодд, который всё это затеял, photo from https://freefeast.info/personality-motivation/famous_it_personalities/invention-of-relational-model-for-database-ted-codd/

Далее — понеслось. Сама IBM в этот язык и реляционную модель не поверила, зато её взяла за основу компания “Relational Software”, ныне всем известная как Oracle.

К середине восьмидесятых технический прогресс обеспечил возможность повсеместного использования реляционных БД, а к началу девяностых (рискну сказать “и по сей день”) они уже доминировали на рынке масштабного хранения и обработки данных.

Ещё тогда эти ребята придумали концепции транзакций, их свойства (ACID), и так далее. Одна из секций стандарта SQL как раз посвящена уровням изоляции, и даёт ответы на два главных вопроса:

Что может пойти не так, если две транзакции будут в недостаточной мере изолированы друг от друга? Виды этих ошибок называются “побочные эффекты”.
Как избежать всех побочных эффектов пункта (1), при этом не создав взаимных блокировок (deadlock) и не ухудшив производительность?

Побочные эффекты

Стандарт даёт описания пяти побочных эффектов, сначала идут простые, затем более сложные. Под “сложностью” побочного эффекта я имею ввиду “сложнее его отловить и исправить”. Например отсутствие LOST UPDATE обеспечить проще, чем отсутствие DIRTY READ. Для понимания этих эффектов, надо держать в уме, что речь всегда идёт о взаимодействии двух и более транзакций одновременно, а сам побочный эффект — это когда желаемый результат не совпал с реальным.

Потерянное обновление / Lost Update

Когда несколько транзакций что-то обновили в БД, но по итогам результат такой, будто отработала лишь часть транзакций.

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

Грязное чтение / Dirty Read

Когда ваша транзакция может прочитать данные, которые были добавлены/изменены другой транзакцией, пока та ещё не вызвала COMMIT этих данных (ещё другая транзакция могла что-то удалить, тогда ваша транзакция перестанет это видеть).

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

Неповторяющееся чтение / Non-Repeatable Read

Когда одинаковый запрос в одной транзакции может вернуть разные данные; NON-REPEATABLE READ касается лишь только набора данных, который однажды уже был прочитан в транзакции — это значит, если вы прочитали первые 10 строк таблицы А, то NON-REPEATABLE READ случится, если вы заново прочитаете первые 10 строк таблицы А и получите другой результат;

Это уже менее опасный эффект, но более сложный в понимании. Допустим, одна транзакция считает полную стоимость услуги на основе тарифов, а другая меняет параллельно эти тарифы. Non-repeatable read случится, если последовательность действий будет такова:

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

Фантомное чтение / Phantom Reads

Когда: одинаковый запрос может вернуть НОВЫЕ строки, которые были закомичены из другой транзакции. Отличие от NON-REPEATABLE READ в том, что строки, которые мы уже разок прочитали остаются такими же, соответствнно здесь нет NON-REPEATABLE READ. Но при этом, запрос который сначала вернул 5 строк, может вернуть эти же пять строк, плюс ещё две свежие строки из другой транзакции — это PHANTOM READ;

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

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

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

При этом, “простые” эффекты включают в себя все более сложные, то есть если есть шанс на DIRTY READ, то и всё что сложнее его — можно тоже спокойно получить.

В прошлом сообщении эти эффекты описаны как раз в порядке возрастания “сложности”:

LOST UPDATE < DIRTY READ < NON-REPEATABLE READ < PHANTOM READ

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

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

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

Подходы к реализации уровней изоляции

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

Версионирование (snapshot) означает, что транзакции будут работать со своей копией данных, не влияя друг на друга, но впоследствии несколько изменённых копий надо будет как-то слить в одну. Строгость версионирования регулирует моменты (когда) и размеры (сколько данных копировать) этих копий.

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

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

Пессимистичная блокировка происходит как можно раньше, чтобы предотвратить как можно больше побочных эффектов, но транзакции чаще ждут друг-друга. Например, блокирование на обновление строк, которые прочитала транзакция в режиме read committed — это пессимистичная блокировка, потому что она происходит, как только ваша транзакция прочитала строки, не смотря на то, будет ли их кто-то обновлять или нет.
Оптимистичная блокировка происходит как можно позже, чтобы транзакции реже ждали друг-друга. Плата за такой вид блокировки — необходимость обрабатывать “конфликт обновления”. Конфликт обновления происходит, например, когда “ваша” транзакция прочитала строки и хочет их обновить, но другая транзакция их уже обновила — при оптимистичной блокировке эта ошибка возникнет в “вашей” транзакции и её надо корректно обработать: как минимум, повторить транзакцию позже, как максимум, обработать ошибку прямо в коде транзакции.

Далее, рассмотрим подходы к блокировке/версионированию в MS SQL.

Реализация уровней изоляции в MS SQL

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

Примечание 1: здесь начинается слабодокументированная зона, и я могу ошибаться, но готов вносить исправления и улучшать.

Примечание 2: возможно, эта глава будет расширена описанием shared-, exclusive-, range-locks: что это такое, в каком режиме и как оно используется.

Read Uncommitted

Ничего не происходит в режиме read uncomitted. То есть ничего не блокируется и не создаются снэпшоты, транзакция просто читает всё что хочет. По смыслу, этот режим можно отнести к (очень) оптимистичному — блокировки редки и коротки.

Read Committed

Read committed (то есть отсутсвие dirty reads) обеспечивается блокировкой на запись данных (строк), которые мы пытаемся прочитать. Эта блокировка гарантирует, что мы подождём завершения транзакций, которые уже меняют наши данные, или заставим их подождать, пока мы будем читать. В итоге, мы точно прочитаем только данные, которые были закоммичены, избежав тем самым грязное чтение. Этот режим — типичный пример пессимистичной блокировки, так как мы блокируем данные на запись, даже если в них никто реально не пишет.

Read Committed Snapshot

Read commited snapshot (второй способ избежать dirty reads) обеспечивается версионированием блока данных, который мы читаем. Любое его параллельное изменение на затронет нашу версию, и нам достанутся данные на момент начала чтения. Таким образом грязное чтение отсутствует, и ещё отсутствуют какие-либо блокировки*. Этот режим скорее оптимистичный, так как заранее ничего не блокируется, и тут как раз может быть конфликт обновления.

Repeatable Read

Repeatable read (то есть отсутствие всего, кроме фантомных чтений) обеспечивается почти как read commited, за тем исключением, что блокировка на запись работает до конца транзакции, а не отдельной операции. Единожды “коснувшись” блока данных, транзакция блокирует его изменение до конца работы, что обеспечивает отсутствиуе dirty reads и non-repeatable reads. Это более пессимистичный вид блокировки, чем read committed, так как блокировка держится ещё дольше.

Serializable

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

Snapshot

Snapshot обеспечивается созданием нашей отдельной версии данных, с которыми мы работаем, без блокировок*. Другие транзакции не будут иметь доступ к нашей версии, чем и обеспечивается отсутствие всего вплоть до фантомных чтений. Это оптимистичный вид блокировки, но менее оптимистичный, чем read committed snapshot, так как больше шансов получить конфликт обновления.

Примечания

При любом использовании read committed snapshot и snapshot, необходимо обеспечить правильную обработку конфликтов изменений, либо через повтор транзакции, либо через обработку конфликта внутри транзакции;
Вот как выглядит текст ошибки конфликта (врага надо знать в лицо!):

Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table ‘dbo.Table’ directly or indirectly in database ‘DB’ to update, delete, or insert the row that has been modified or deleted by another transaction. Retry the transaction or change the isolation level for the update/delete statement.

В режимах read committed snapshot и snapshot, в момент коммита транзакций всё-таки будут установлены блокировки, чтобы избежать конфликтов записи — это написано в документации. Также, логично предположить, что некие блокировки всё же будут установлены на время создания снэпшота;
В MS SQL snapshot-режимы по умолчанию вообще отключены;
В MS SQL существует более 20 видов блокировок (locks), некоторые из которых лишь вскользь упомянуты в этой статье;
В рамках одной транзакции можно переключать (!!!) режим изоляции, за исключением режима snapshot: переключение в snapshot из любого другого режима вызовет откат транзакции, только если транзакция изначально не была в режиме snapshot — тогда сработает;
Блокировки в режиме serializable могут быть взяты на всю таблицу, вместо конкретного набора строк. Это происходит в случае, если на таблице нет индекса, который мог бы быть использован для обеспечения такой блокировки. Очевидно, что это ещё сильнее скажется на производительности — чем больше блокировок, тем медленнее.

Заключение

Разные уровни изоляций обеспечивают контроль целостности БД, через устранение различных побочных эффектов.
Чем более строгий уровень изоляции выбран, тем больше шансов, что транзакции будут заблокированы, и тем меньше общая производительность системы.
Контроль над уровнями изоляции дан разработчикам, чтобы выбрать наилучший баланс между строгостью контроля целостности и производительностью системы.
Побочные эффекты: LOST UPDATE, DIRTY READ, NON-REPEATABLE READ, PHANTOM READ.
Уровни изоляции: Read Uncommitted < Read Committed (Snapshot) < Repeatable Read < Serializable / Snapshot.
Чем раньше блокировка происходит и чем дольше держится, тем более пессимистичной она считается. Соответственно, чем позже и меньше — тем более оптимистичной.

Обо мне

Меня зовут Рустем, последние несколько лет работаю проект-менеджером в Аквелоне. Ещё у меня более 10-ти лет опыта разработки софта и других классных штук. Связаться со мной можно через twitter.com/rulikkk или подписаться на мой канал в tg: techno_ru.

Полезные ссылки

По этим ссылкам, а также пользуясь здравым смыслом, я и собрал статью: