На пути к правильным SQL транзакциям (Часть 2)25.06.2015 16:03

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

Как вы могли видеть в предыдущем разделе, существует два способа поддержания изоляции:

• Основанный на блокировке ресурсов
• Основанный на создании версионной копии ресурсов.

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

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

Организация памяти в MS SQL

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

• Data — данные таблицы (за исключением колонок переменного и очень большого размера);
• Index — индексы;
• Text/Image — колонки переменного и очень большого размера
• Другие типы, содержащие вспомогательную информацию

Экстент
Экстент — основная единица управления пространством (64Kb), содержащая последовательные 8-м страниц (8×8Kb). Бывают однородные (Uniform) и смешанные (Mixed) экстенты. В однородных содержаться страницы одного типа, а в смешанных — разного типа. По мере появления новых страниц одного типа в составе смешанных экстентов, сервер старается создавать однородные экстенты, перемещая страницы между смешанными экстентами.
Теперь, зная, как организована память в сервере, можно ввести первую классификацию блокировок, а именно по типу блокируемого ресурса.

Разновидности блокировок по типу блокируемых ресурсов

Блокируемые ресурсы:

• Конкретная строка в таблице
• Ключ (один или несколько ключей в индексе)
• Страница
• Экстент
• Таблица и все относящиеся к ней данные (индексы, ключи, данные)
• База (блокируется, когда меняется схема базы)

Эскалация блокировок

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

Разновидности блокировок по режиму блокирования

Совмещаемая (Shared) блокировка
Используются для операций считывания (SELECT) и предотвращают изменение (UPDATE, DELETE) заблокированного ресурса. Как следует из названия, данная блокировка может быть совмещена с другими блокировками (совмещаемыми или блокировками обновления, описаны ниже). Это значит, что если транзакция T1 считывает данные и устанавливает совмещаемые блокировки на считываемые строки, то другая транзакция T2 может так же установить блокировки на те же строки, не дожидаясь снятия блокировок транзакцией T1.
В зависимости от уровня изоляции транзакции, блокировка может быть снята, как только данные считаны (Read Committed), либо же удерживаться до конца транзакции (Repeatable Read и выше). Так же блокировку можно удерживать до конца транзакции, указав в запросе соответствующие табличные подсказки (например, HOLDLOCK, SERIALIZABLE и т.д.)
В случае, когда блокировки снимаются по мере чтения данных, они могут быть сняты даже до момента завершения запроса (SELECT). Т.е. если мы выбираем 10 строк и установлено 10 совмещаемых блокировок уровня строки, то, как только считаны данные первой строки, её блокировка снимается, не дожидаясь считывания оставшихся 9 строк.Монопольная (Exclusive) блокировка
Используется для операций изменения данных (UPDATE, DELETE). Данная блокировка не может быть установлена, если существуют какие-либо другие блокировки на ресурсе, т.е. команда будет ждать снятия всех существующих блокировок. Будучи успешно установленной, данная блокировка не позволяет установку новых блокировок любых типов, т.е. все запросы, пытающиеся получить доступ к заблокированному ресурсу, будут ожидать снятия монопольной блокировки.Блокировка обновления (Update)
Что бы понять, почему потребовалось ввести данный тип блокировок, давайте рассмотрим подробней процесс обновления данных. Логически, его можно разделить на два этапа:

1. Поиск данных для обновления
2. Обновление найденных данных.

Мы уже знаем, что для корректного обновления данных необходимо установить монопольную (exclusive) блокировку. Но, если монопольная блокировка будет установлена с самого начала выполнения запроса (на этапе поиска данных), то мы сделаем невозможным даже чтение данных из других транзакций. Поэтому на первом этапе (поиск данных) лучше будет установить разделяемую блокировку и только если данные найдены, то преобразовать её в монопольную и произвести изменение. Это позволит другим транзакциям избежать ожидания при чтении данных, пока транзакция ищет данные для обновления.
Вроде всё отлично с предлагаемым подходом. Создаём разделяемую блокировку на первом этапе и преобразуем её в монопольную на втором. Производительность улучшена и все счастливы. Но увы тут есть подвох. Если, по описанному выше алгоритму, две различные транзакции будут одновременно пытаться произвести обновление одних и тех же данных, то мы гарантированно получаем взаимоблокировку (Deadlock). Ниже показано как она возникает.

Поэтому потребовался новый режим блокировки — блокировка обновления (Update). Он ведёт себя как что-то среднее между совмещаемой (Shared) и монопольной (Exclusive) блокировкой. «Монопольность» заключается в том, что на ресурсе может быть только одна блокировка обновления, а «совмещаемость» в том, что на этапе поиска данных блокировка может совмещаться с другими совмещаемыми блокировками.

Примечание: блокировка обновления (update) используется не только для операций UPDATE, но и для удаления данных (DELETE) на этапе поиска удаляемых данных. В случае вставки новых строк в таблицу с кластерным индексом INSERT, данный тип блокировки также применим на этапе поиска правильного положения в индексе; когда положение найдено, блокировка обновления (update) преобразуется в монопольную блокировку индекса и происходит вставка новой строки.

Блокировки с намерением (Intent)
Данный тип блокировок не представляет собой особый режим. Он служит для оптимизации работы алгоритма установки блокировок, описанных выше.
В основе лежит простая идея. Перед установкой низкоуровневых блокировок (уровня строки или страницы), мы всегда сначала устанавливаем блокировку намерения (Intent) на более высоком уровне — на уровне таблицы. Если такой блокировки нет, то мы можем избежать проверки наличия уже существующих блокировок на интересующих нас ресурсах (строках, страницах) и сразу их установить. Если она есть, то можно более оптимально принять решение о возможности установки определённой блокировки низкого уровня. Например, если существует совмещаемая блокировка с намерением, то нет смысла пытаться получить эксклюзивную блокировку на уровне таблицы.
В зависимости от типа низкоуровневых блокировок можно выделить следующие типы блокировок с намерением:

• Блокировка с намерением совмещаемого доступа (IS)
• Блокировка с намерением монопольного доступа (IX)
• Совмещаемая блокировка с намерением монопольного доступа (SIX)
• Блокировка с намерением обновления (IU)
• Совмещаемая блокировка с намерением обновления (SIU)
• Блокировка обновления с намерением монопольного доступа (UIX)

Блокировки схем (Schema)
Данные блокировки служат для предотвращения изменения структуры базы данных (таблиц, представлений, индексов и т.д.) и подразделяются на два вида:

• Блокировка изменения схемы (Sch-M) — устанавливается при обновлении схемы таблицы и на время существования запрещает любой доступ к данным таблицы
• Блокировка стабильности схемы (Sch-S) — устанавливается при выполнении запросов; данная блокировка добавляется ко всем объектам схемы (схема таблицы, индексы и т.п.), которые задействованы в запросе и на время существования предотвращает их изменение

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

• указана подсказка TABLOCK
• установлен параметр «блокировка таблицы при массовой загрузке (table lock on bulk load)» при помощи хранимой процедуры sp_tableoption

Блокировка диапазона ключа (Range)
Данная блокировка, позволяет предотвратить проблему появления фантомов, путём блокирования диапазона выбранных строк. Т.е. для выбранного набора строк будет гарантированно отсутствие появления новых строк (подпадающих под критерий запроса), а также удаление строк из выбранного набора. На основе данной блокировки реализован уровень изоляции SERIALIZABLE. Более подробно тут.

Совместимость блокировок

Как уже упоминалось выше, некоторые типы блокировки могут быть успешно установлены на одном и том же ресурсе. Другие же, напротив, будут ожидать завершение всех блокировок. Ниже представлена полная матрица совместимости, которая показывает, может ли конкретный тип блокировки быть установлен, при наличии уже имеющихся блокировок.
Например, нас интересует может ли быть установлена разделяемая блокировка (Shared), если на ресурсе установлена монопольная блокировка (Exclusive). Для этого мы находим строку, соответствующую запрашиваемой блокировке (выделена синей рамкой) и находим значение в соответствующем столбце (выделено красной рамкой). В нашем примере мы видим значение «К», что говорит о конфликте, т.е. разделяемая блокировка (S) будет вынуждена ждать, пока с ресурса не будет снята монопольная блокировка (X).

Заключение

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

© Habrahabr.ru