Что определяет уровень изоляции транзакции

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

Введение

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

Зачем нужна изоляция транзакций?

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

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

Имеющиеся типы изоляции транзакций

SQL Server имеет 4 уровня изоляции.

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

2. READ COMMITTED: Это установка по умолчанию для большинства запросов SQL Server. Она определяет, что транзакция в текущем сеансе не может читать данные, которые были модифицированы другой транзакцией. Тем самым при этой установке предотвращается грязное чтение.

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

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

1. Текущая транзакция может читать только зафиксированные данные, модифицированные другой транзакцией данные.
2. Другие транзакции ставятся в очередь ожидания пока первая транзакция не завершит выполнение.
3. Никаким транзакциям не разрешается вставлять данные, которые отвечают условию текущей транзакции.

Изменение уровня изоляции в SSMS

Существует два способа установки изоляции транзакций в SSMS:

1. Использование GUI
2. Использование команд T-SQL

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

1. Щелкнуть правой кнопкой мышки в окне запроса и выбрать «Query Options» (Параметры запроса).

2. Выбрать «Advances» (дополнительно) в группе «Execution» (выполнение) на левой панели.
3. Щелкнуть по выпадающему списку рядом с «SET TRANSACTION ISOLATION LEVEL».

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

Использование команд T-SQL

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

Заключение

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

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

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

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

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

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

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

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

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

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

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

Уровни изоляции 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.

Транзакции, блокировки, уровни изолированности транзакций в MySQL

С приходом master-master репликаций остро встает вопрос о целостность с достоверностью базы данных.

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

• Достоверность (или истинность) — соответствие фактов, хранящихся в базе данных, реальному миру

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

Например:

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

UPDATE accounts SET money=money-1100 WHERE account=”PC1″; UPDATE accounts SET money=money+1100 WHERE account=”PC2″;

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Источники:

https://sql-ex.ru/blogs/optimization/what-is-transaction-isolation-in-sql.html
https://professorweb.ru/my/sql-server/2012/level3/3_16.php
https://shurshun.ru/tranzaktsii-blokirovki-urovni-izoliovannosti-tranzaktsiy-v-mysql/