Выбор правильного уровня изоляции транзакции является важной частью процесса проектирования и разработки реляционной базы данных, поскольку он устраняет широкий спектр проблем параллелизма.
В этой статье мы сосредоточимся на тех уровнях изоляции транзакций, которые решают проблемы параллелизма с использованием блокировки, такие как чтение незафиксированных, чтение подтвержденных, повторяемое чтение и сериализуемое.
· Общие (S) против эксклюзивных (X) блокировок
· Чтение незафиксированных
∘ Проблема грязного чтения
∘ Чтение незафиксированных и подсказка таблицы NOLOCK
∘ Когда использовать Read Uncommitted?
· Read Committed
∘ Как Read Committed решает проблему грязного чтения?
∘ Проблема неповторяющихся чтений
∘ Проблема утерянных обновлений
· Повторяющееся чтение
∘ Как повторяющееся чтение решает проблему неповторяющихся чтений?
∘ Как повторное чтение решает проблему утерянных обновлений?
∘ Проблема с фантомным чтением
· Сериализуемый
∘ Как сериализуемый способ решает проблему с фантомом?
· Резюме
Совместно используемые (S) и эксклюзивные (X) блокировки
Понимание основных различий, по крайней мере, между разделяемыми (S) и эксклюзивными (X) блокировками, важно для понимания уровней изоляции транзакций.
- Операции чтения (SELECT) устанавливают общие блокировки ресурса (строки, ключа, таблицы, страницы и т. Д.) Перед чтением данных.
- Операции модификации данных (INSERT, UPDATE, DELETE) получают эксклюзивные блокировки перед изменением данных.
- Совместная блокировка может быть получена для ресурса, даже если у него уже есть другая общая блокировка. У ресурса может быть столько общих блокировок, сколько необходимо, потому что параллельное чтение - безопасная операция.
- Исключительная блокировка может быть получена для ресурса только в том случае, если у него еще нет общих или исключительных блокировок, чтобы гарантировать, что только одна транзакция может изменять данные одновременно.
- Если совместные или эксклюзивные блокировки не могут быть получены, транзакция будет ждать, пока блокировки не будут сняты (не применимо к уровню изоляции shapshot).
Читать незафиксированные
Этот уровень изоляции транзакции, как следует из названия, позволяет транзакции читать данные, которые были изменены другой транзакцией, но еще не были зафиксированы.
Незавершенное чтение - это самый низкий уровень изоляции в MS SQL Server, который может привести ко всем возможным типам явлений параллелизма:
- Грязные чтения
- Утраченные обновления
- Неповторяющиеся чтения
- Призрак читает
Проблема с грязным чтением
Давайте рассмотрим пример того, почему проблема «грязного» чтения может возникнуть на уровне изоляции «чтение незафиксированных».
Это классический пример явления параллелизма, называемого грязным чтением: вторая транзакция считывает данные, которые первая транзакция не зафиксировала.
Первая транзакция обновляет строку, поэтому она устанавливает исключительную блокировку (X) на строку, которую она обновляет.
Независимо от уровня изоляции транзакции, транзакция всегда получает эксклюзивную блокировку данных, которые она изменяет, и удерживает блокировку до конца транзакции.
Вторая транзакция не получает разделяемую блокировку (S) перед выполнением оператора SELECT (разделяемые блокировки вообще не устанавливаются на уровне изоляции чтения незафиксированных), поэтому ничто не препятствует чтению данных второй транзакцией. что первая транзакция в конечном итоге откатится.
Прочитать подсказку таблицы Uncommitted vs NOLOCK
Правила уровня изоляции транзакции применяются ко всем операциям внутри транзакции. Например, как упоминалось ранее, если уровень изоляции чтения установлен на чтение без фиксации, ни один из запросов SELECT в этой транзакции не установит общие блокировки для ресурсов, к которым они обращаются.
Подсказки таблицы NOLOCK могут использоваться для определенного запроса. Когда данные запрашиваются с подсказкой NOLOCK, общая блокировка (S) не берется. Другими словами, подсказка таблицы NOLOCK дает возможность установить уровень изоляции незафиксированного чтения для конкретного запроса, а не для всей транзакции.
Вторая транзакция выполняется на уровне подтвержденного чтения по умолчанию, но внутренний запрос выбора игнорирует уровень изоляции транзакции и выполняется так, как если бы уровень незафиксированного чтения был установлен из-за табличной подсказки NOLOCK.
Когда использовать чтение без фиксации?
Использование уровня изоляции чтения незафиксированных для транзакции или табличной подсказки NOLOCK для запросов SELECT - хороший выбор для запроса данных, которые вряд ли изменятся, или статических данных, которые фактически никогда не изменяются после их создания.
Чтение незафиксированного уровня изоляции или подсказки NOLOCK не требует общих блокировок, что повышает производительность при запросе данных.
Прочитано совершено
Изоляция зафиксированного уровня чтения является изоляцией по умолчанию на сервере MS SQL. Фиксированное чтение решает проблему грязного чтения, но эти проблемы все еще могут возникать:
- Утраченные обновления
- Неповторяющиеся чтения
- Призрак читает
Как Read Committed решает проблему грязного чтения?
Когда транзакция выполняется на уровне изоляции подтвержденного чтения, каждый запрос SELECT должен получить общую блокировку (S) на ресурсе, к которому он обращается.
Обратите внимание, что обе транзакции выполняются на уровне подтвержденного чтения, даже если он не установлен явно.
Чтобы понять, почему грязное чтение больше не является проблемой, давайте пошагово проанализируем, что происходит, когда обе транзакции выполняются одновременно:
- Первая транзакция получает эксклюзивную блокировку (X) перед запуском оператора UPDATE и удерживает ее до конца транзакции.
- Вторая транзакция пытается получить общую блокировку (S) для той же записи, что первая транзакция уже получила эксклюзивную блокировку (X).
- Вторая транзакция должна дождаться завершения выполнения первой транзакции и снятия монопольной блокировки, потому что общая блокировка не может быть получена для записи, которая уже имеет монопольную блокировку.
- Когда первая транзакция завершается, вторая продолжает выполнение и считывает не-грязное значение из таблицы.
Проблема неповторяющихся чтений
На уровне изоляции зафиксированного чтения (а также чтения без фиксации) может возникнуть проблема с неповторяющимся чтением.
Обе транзакции используют уровень изоляции зафиксированного чтения. Хотя оба запроса SELECT в первой транзакции одинаковы, они будут извлекать разные или неповторяющиеся значения.
В подтвержденных транзакциях чтения разделяемые блокировки устанавливаются перед выполнением запросов SELECT, в отличие от чтения незафиксированных транзакций.
Однако неповторяющиеся чтения по-прежнему возможны в транзакциях с подтвержденным чтением, поскольку общие блокировки снимаются, как только завершается выполнение запроса SELECT. Совместно используемые блокировки не удерживаются до конца транзакции на уровне изоляции зафиксированного чтения.
- Первая транзакция получает общую блокировку (S) для строки таблицы перед запуском первого запроса SELECT и снимает блокировку после чтения данных.
- Вторая транзакция устанавливает исключительную блокировку (X) на строку и обновляет эту строку в таблице. Вторая транзакция может это сделать, потому что первая транзакция уже сняла общую блокировку.
- Вторая транзакция завершается и снимает монопольную блокировку.
- Первая транзакция снова получает разделяемую блокировку для выполнения второго запроса SELECT и считывает новое значение из таблицы.
Проблема с утерянными обновлениями
Еще одна проблема, которая может возникнуть на уровне изоляции зафиксированной (и незафиксированной) транзакции при чтении, - это потеря обновлений.
В этом примере первая транзакция автоматически перезаписывает результаты второй транзакции. Если начальная цена перед выполнением какой-либо транзакции равна 500, то после выполнения обеих транзакций цена должна быть 3500. Однако окончательная цена равна 1500. Значение 2000 из второй транзакции теряется.
Основная причина проблемы потери обновлений такая же, как и для неповторяющегося чтения: общая блокировка снимается, как только завершается запрос SELECT.
Повторяющееся чтение
Изоляция на уровне повторяемого чтения предотвращает «грязное» чтение, неповторяющееся чтение и проблемы с потерянными обновлениями. Единственная проблема, которая может возникнуть - это фантомное чтение.
Как повторяющееся чтение решает проблему неповторимого чтения?
Установка уровня изоляции повторяемого чтения для транзакции гарантирует, что все идентичные запросы в этой транзакции возвращают одни и те же данные.
Проблема неповторяемого чтения не может возникнуть на уровне изоляции «чтение-повторение», поскольку транзакция удерживает общую блокировку до конца транзакции.
Весь этот процесс выглядит так:
- Первая транзакция начинается, получает разделяемую блокировку перед выполнением первого запроса SELECT и извлекает значение из таблицы.
- Вторая транзакция начинается и пытается получить монопольную блокировку строки, но должна ждать, пока первая транзакция не освободит общую блокировку.
- Первая транзакция выполняет второй запрос SELECT и получает то же значение, что и первая, потому что вторая транзакция не может его обновить.
- Первая транзакция завершается и снимает общую блокировку.
- Вторая транзакция может получить монопольную блокировку и обновить строку.
Как повторное чтение решает проблему утерянных обновлений?
Когда уровень изоляции транзакции установлен на повторяющееся чтение, проблема с потерянными обновлениями больше не возникает:
Проблема с утерянным обновлением больше не будет возникать, но возникнет проблема взаимоблокировки, поскольку общая блокировка удерживается до конца транзакции.
Вот что сейчас происходит:
- Первая транзакция начинается и получает общую блокировку для строки.
- Вторая транзакция начинается и получает общую блокировку для той же строки.
- Вторая транзакция пытается получить монопольную блокировку перед обновлением строки, но ждет, пока первая транзакция не освободит общую блокировку.
- Первая транзакция пытается получить монопольную блокировку перед обновлением строки, но ждет, пока вторая транзакция не снимет общую блокировку.
- Сервер MS SQL завершает одну из транзакций и показывает следующую ошибку: Транзакция зашла в тупик при блокировке ресурсов другим процессом и была выбрана жертвой тупика. Повторите транзакцию.
- Выигравшая транзакция завершается успешно и обновляет значение в таблице.
Тупиков нельзя избежать на 100%. Внутренняя логика должна иметь механизм повтора для транзакций, которые были прерваны из-за тупиковых ситуаций. Как только неудачная транзакция будет выполнена снова, окончательное значение в таблице будет 3500, как и ожидалось.
Проблема с фантомным чтением
Фантомное чтение означает, что несколько идентичных запросов SELECT, выполняемых в рамках одной транзакции, возвращают разное количество строк.
Проблема фантомного чтения аналогична проблеме неповторяющегося чтения. Однако фантомное чтение означает вставку или удаление строк из таблицы, а неповторяющееся чтение означает обновление значений в таблице без изменения количества строк.
Вот что происходит в примере:
- Первая транзакция запускается, получает общую блокировку ключей строк, соответствующих предикату (2 строки), и считывает эти строки.
- Вторая транзакция получает эксклюзивную блокировку для нового ключа строки и вставляет эту строку в таблицу.
- Первая транзакция снова считывает данные, используя тот же предикат, что и в первый раз, и получает дополнительную строку в результатах.
Сериализуемый
Это самый высокий уровень изоляции, который предотвращает все возможные типы явлений параллелизма в SQL Server, но, с другой стороны, сериализуемый уровень снижает производительность и увеличивает вероятность взаимоблокировок.
Как Serializable решает фантомную проблему?
Сериализуемый уровень изоляции заставляет запросы с ранжированными предикатами устанавливать блокировки диапазона, чтобы избежать фантомных чтений.
Давайте проанализируем, что происходит, когда уровень изоляции транзакции сериализуем:
- Первая транзакция запускается и получает блокировку общего диапазона (RangeS-S) перед выполнением первого запроса SELECT. Блокировка общего диапазона блокирует не только существующие записи, но и записи, которые потенциально могут попасть в диапазон предикатов, указанный в предложении WHERE (все записи с Id ≥2).
- Вторая транзакция пытается получить эксклюзивную блокировку для ключа строки, которую она собирается вставить, но она должна дождаться, пока первая транзакция не освободит блокировку общего диапазона, потому что новый идентификатор строки (4) попадает в диапазон предиката (Id ≥2) .
- Первая транзакция считывает такое же количество строк во второй раз и снимает блокировку общего диапазона в конце транзакции.
- Вторая транзакция вставляет новую строку в таблицу и завершается.
Резюме
- Чем выше уровень изоляции транзакции, тем лучше защита от проблем параллелизма, но тем ниже производительность.
- Serializable - это самый высокий уровень изоляции, который защищает транзакции от всех типов явлений параллелизма.
- Уровень изоляции повторяемого чтения не защищает транзакции от проблемы фантомного чтения.
- Уровень изоляции зафиксированных транзакций при чтении не защищает транзакции от фантомных чтений, неповторяющихся чтений и проблем с потерей обновлений.
- Незавершенное чтение не защищает транзакции от фантомного чтения, неповторяющегося чтения, потерянных обновлений и проблем грязного чтения.
