Изоляция на основе блокировок в SQL Server

Введение

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

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

Обзор свойств ACID

• Атомарность (A): Либо все операции в транзакции проходят успешно, либо ни одна.
• Согласованность (C): транзакция переводит базу данных из одного согласованного состояния в другое, контролируя уровень изоляции и соблюдая правила целостности, определенные в базе данных (например, первичные ключи, внешние ключи и уникальные ограничения).
• Изоляция (I): Параллельные транзакции могут обращаться только к согласованным данным, другими словами, транзакции происходят независимо друг от друга без вмешательства, что может быть достигнуто путем контроля уровня изоляции.
• Долговечность (D): изменения, внесенные успешной транзакцией, должны храниться в постоянном хранилище, чтобы они сохранялись даже в случае сбоя системы.

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

Модели изоляции (пессимистический и оптимистический контроль параллелизма)

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

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

• Блокировка (пессимистическая): Если текущее состояние данных непоследовательно, читатели блокируются (должны ждать), пока данные не станут последовательными.
• Версионность строк (оптимистическая): Если текущее состояние данных непоследовательно, читатели получают более старую версию согласованных данных, поэтому читатели не блокируются (не должны ждать).

В этой статье мы рассмотрим модель на основе блокировок (пессимистичный контроль параллелизма).

Режимы блокировки с изоляцией

Чтобы знать, как работает механизм изоляции в SQL Server, необходимо ознакомиться с двумя режимами блокировки:

• Исключительная: Когда транзакция модифицирует (обновляет, удаляет или вставляет) данные, она запрашивает эксклюзивную блокировку, и если она будет предоставлена, то эта блокировка будет удерживаться до завершения транзакции (всей транзакции). Он называется «эксклюзивным», потому что вы не можете получить эксклюзивную блокировку на ресурс, если этот ресурс уже имеет какой-либо режим блокировки; а если ресурс уже имеет эксклюзивный режим, никакой другой режим блокировки не может быть получен на этом ресурсе.
• Совместное использование: Когда транзакция читает (выбирает) данные, она запрашивает общую блокировку, и она освобождается, как только оператор чтения или вся транзакция завершается (в зависимости от уровня изоляции). Она называется «общей», потому что несколько транзакций могут одновременно иметь общие блокировки на ресурсе.

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

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

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

Основная цель уровней изоляции — контролировать следующие эффекты:

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

На самом деле, каждый механизм баз данных реализует свои уровни изоляции по-разному, но SQL Server поддерживает шесть уровней изоляции, четыре уровня основаны на блокировке (READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ и SERIALIZABLE) и два уровня основаны на версиях строк (SNAPSHOT и READ COMMITTED SNAPSHOT).

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

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

Чтобы опробовать эти примеры на своей базе данных SQL Server, выполните следующие шаги:

1- Создайте собственную базу данных (в моем случае она называется «isolation_db»).
2- Создайте таблицу «users» и вставьте эту фальшивую строку

    CREATE TABLE users (user_id INT PRIMARY KEY IDENTITY (1, 1), age INT NOT NULL);
    INSERT INTO users(age) VALUES (20);

3- Откройте два соединения с вашей базой данных Con1 и Con2.
4- Выполните шаги, показанные на изображениях.
5- После того, как вы закончите любой из приведенных ниже примеров, зафиксируйте все запущенные транзакции, а затем выполните следующие операторы для очистки:

    SET TRANSACTION ISOLATION LEVEL READ COMMITTED; -- Reset to the default
    UPDATE users SET age = 20 WHERE user_id = 1;
    DELETE users WHERE age > 30;

Без лишних слов перейдем к рассмотрению уровней изоляции на основе блокировки от самого низкого к самому высокому:

1- Read Uncommitted

Как это работает

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

Аномалии, которые он решает

— Грязная запись: возникает в ситуации, когда транзакция обновляет ресурс, а другая транзакция обновляет тот же ресурс до завершения первой.

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

Давайте перейдем к практическому примеру Example-1, чтобы пояснить, как этот уровень позволяет избежать грязной записи:

Аномалии, которые он имеет

— Грязное чтение: Возникает в ситуации, когда транзакция читает изменения из другой незафиксированной транзакции.

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

Следуйте этому примеру Example-2 :

2- Read Committed

Как это работает

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

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

Этот уровень изоляции является уровнем по умолчанию в SQL Server, вы можете получить опцию isolation level, выполнив этот оператор DBCC useroptions;.

Аномалии, которые он решает

— Грязные чтения

Следуйте этому примеру Example-3 :

Аномалии, которые он решает

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

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

Разъясним эту аномалию на примере Example-4 :

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

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

Если это объяснение не понятно, пожалуйста, следуйте этому примеру Example-5 :

3- Повторяющееся чтение

Как это работает

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

Аномалии, которые она решает

— Неповторяющиеся чтения

Следуйте этому примеру Example-6 :

— Потерянное обновление

Следуйте этому примеру Пример-7 :

Аномалии, которые он имеет

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

Если это объяснение недостаточно понятно, пожалуйста, следуйте этому примеру Example-8 :

4- Serializable

Как это работает

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

Аномалии, которые она решает

— Фантомные чтения

Давайте узнаем, как этот уровень предотвращает фантомные строки на примере Example-9 :

Посмотрите в этой сводной таблице уровни изоляции и их аномалии

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

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

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

Заключение

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

Ресурсы

• Основы T-SQL 3-й
• Понимание уровней изоляции
• Практическое руководство по изоляции транзакций SQL