Приветствую всех посетителей сайта Info-Comp.ru! В данном материале мы рассмотрим типы блокировок, которые существуют в Microsoft SQL Server, поговорим о том, как и для чего они используются.
Блокировка – это механизм, с помощью которого SQL Server организовывает одновременный доступ нескольких пользователей к одному участку данных.
Заметка! Подробнее о том, что такое блокировки и для чего они нужны, мы рассматривали в статье – Блокировки в Microsoft SQL Server.
Типы блокировок
SQL Server блокирует ресурсы с помощью различных режимов блокировки, их также называют типы блокировок, которые определяют доступ одновременных транзакций к ресурсам.
В следующей таблице представлены типы блокировок в SQL Server.
| Блокировка | Описание |
| Shared (S) | Совмещаемая блокировка |
| Exclusive (X) | Эксклюзивная блокировка |
| Update (U) | Блокировка обновления |
| Intent shared (IS) | Блокировка с намерением совмещаемого доступа |
| Intent exclusive (IX) | Блокировка с намерением эксклюзивного доступа |
| Intent update (IU) | Блокировка с намерением обновления |
| Shared with intent exclusive (SIX) | Совмещаемая блокировка с намерением эксклюзивного доступа |
| Shared intent update (SIU) | Совмещаемая блокировка с намерением обновления |
| Update intent exclusive (UIX) | Блокировка обновления с намерением эксклюзивного доступа |
| Schema modification (Sch-M) | Блокировка изменения схемы |
| Schema stability (Sch-S) | Блокировка стабильности схемы |
| Bulk Update (BU) | Массовое обновление |
| Key-range | Блокировка диапазона строк |
Давайте чуть более подробно рассмотрим каждый тип блокировки.
Совмещаемые блокировки
Обозначение блокировки – Shared (S)
Такие блокировки используются для операций чтения данных (инструкции SELECT).
Пока на ресурсе существуют совмещаемые блокировки, другие транзакции не могут изменять данные.
Совмещаемые блокировки снимаются, когда завершится операция чтения данных, если только не установлен уровень изоляции транзакции Repeatable Read или более высокий уровень.
Заметка! Транзакции в T-SQL – основы для новичков с примерами.
Эксклюзивные блокировки
Обозначение блокировки – Exclusive (X)
Используется для операций модификации данных: INSERT, UPDATE или DELETE. Такие блокировки гарантируют, что несколько изменений не будет выполнено одновременно для одного ресурса, иными словами, эксклюзивная блокировка запрещает транзакциям одновременный доступ к ресурсу.
Если на ресурс наложена эксклюзивная блокировка, то другие транзакции не могут изменять данные, а операции чтения данных допускаются только при наличии хинта NOLOCK или уровня изоляции Read Uncommitted.
Блокировки обновления
Обозначение блокировки – Update (U)
Применяется к тем ресурсам, которые могут быть обновлены. Такие блокировки нацелены на предотвращение потенциальных взаимоблокировок (Deadlock), возникающих, когда несколько сеансов считывают, блокируют и затем, возможно, обновляют ресурс.
Чтобы понять, для чего нужна блокировка обновления, давайте представим, как выглядел бы процесс обновления в SQL Server, если бы не было блокировок обновления.
Сначала, чтобы обновить данные, SQL Server должен их считать и применить все условия отбора данных, таким образом, он должен наложить совмещаемую блокировку (S). Затем, у тех записей, которые не подходят под условия отбора, совмещаемая блокировка отпускается, а по записям, которые подходят, т.е. их нужно обновить, SQL Server преобразует совмещаемую блокировку в эксклюзивную (X), так как мы уже выяснили, изменять данные может только одна транзакция, и для этого ей на этом ресурсе нужна эксклюзивная блокировка.
Однако при таком процессе очень легко может возникнуть взаимоблокировка, так как, если два сеанса одновременно поставят совмещаемую блокировку на одном ресурсе и затем будут пытаться преобразовать ее в эксклюзивную, то они будут ожидать друг друга, так как эксклюзивная блокировка несовместима с другими типами блокировок, т.е. чтобы преобразовать блокировку в эксклюзивную, на ресурсе не должно быть других блокировок.
Именно поэтому SQL Server, когда считывает данные для обновления и проверяет условия отбора, не ставит совмещаемую блокировку (S), как я описал ранее, а вместо нее ставит блокировку обновления (U), которая потом как раз и преобразуется в эксклюзивную (X).
Такую блокировку может устанавливать для ресурса одновременно только одна транзакция, но при этом блокировка обновления (U) совместима с совмещаемой (S) блокировкой, за счет чего мы не блокируем данные для чтения другими транзакциями, и в то же время мы легко можем преобразовать ее в эксклюзивную (X) для фактического изменения данных.
Заметка! Архитектура обработки SQL запросов в Microsoft SQL Server.
Блокировки с намерением
В SQL Server блокировки с намерением применяются для защиты размещения совмещаемой (S) или эксклюзивной (X) блокировки ресурса на более низком уровне. Блокировки с намерением называются так потому, что их получают до блокировок более низкого уровня, т.е. они обозначают намерение поместить блокировку на более низком уровне.
Блокировка с намерением выполняет две функции:
- Предотвращает изменение ресурса более высокого уровня другими транзакциями таким образом, что это сделает недействительной блокировку более низкого уровня;
- Повышает эффективность SQL Server при распознавании конфликтов блокировок на более высоком уровне гранулярности.
Например, блокировка с намерением совмещаемого доступа запрашивается на уровне таблицы до того, как будут запрошены совмещаемые блокировки для страниц или строк в этой таблице. Установка блокировки намерений на уровне таблицы предотвращает последующее получение другой транзакцией эксклюзивной (X) блокировки таблицы, содержащей эту страницу. Блокировки намерений повышают производительность, поскольку SQL Server проверяет блокировки намерений только на уровне таблицы, чтобы определить, может ли транзакция безопасно получить блокировку этой таблицы. Это устраняет необходимость проверять блокировки в каждой строке и на каждой странице в таблице, чтобы определить, может ли транзакция заблокировать всю таблицу.
Заметка! План выполнения запроса в Microsoft SQL Server – что это такое и для чего он нужен.
В следующей таблице представлен перечень блокировок, которые входят в состав блокировок с намерением.
| Тип блокировки | Описание |
| Блокировка с намерением совмещаемого доступа (IS) | Защищает запрошенные или полученные совмещаемые блокировки на некоторых (но не на всех) ресурсах на более низком уровне иерархии. |
| Эксклюзивная блокировка намерения (IX) | Защищает запрошенные или полученные эксклюзивные блокировки на некоторых (но не на всех) ресурсах на более низком уровне иерархии. Режим IX является расширенным режимом IS, кроме того, он защищает запрос на совмещаемые блокировки на ресурсах более низкого уровня. |
| Совмещаемая блокировка с намерением эксклюзивного доступа (SIX) | Защищает запрошенные или полученные совмещаемые блокировки на всех ресурсах более низкого уровня иерархии, а также блокировки с намерением на некоторых (но не всех) ресурсах более низкого уровня. На ресурсах верхнего уровня допускаются одновременные блокировки IS. Одновременно для одного ресурса может быть установлена только одна блокировка SIX, что предотвращает обновление ресурса другими транзакциями, хотя эти транзакции могут считывать данные с ресурсов более низкого уровня в иерархии, получая блокировки IS уровня таблицы. |
| Блокировка с намерением обновления (IU) | Защищает запрошенные или полученные блокировки обновления на всех ресурсах более низкого уровня в иерархии. Блокировки IU применяются только на страничных ресурсах. Если выполняется операция обновления, то блокировки IU преобразуются в IX. |
| Совмещаемая блокировка с намерением обновления (SIU) | Сочетание блокировок S и IU в результате раздельного запрашивания этих блокировок и одновременного удержания их обеих. Например, транзакция выполняет запрос с указанием PAGLOCK, затем выполняет операцию обновления. Запрос с указанием PAGLOCK получает блокировку S, а операция обновления получает блокировку IU. |
| Блокировка обновления с намерением эксклюзивного доступа (UIX) | Сочетание блокировок U и IX в результате раздельного запрашивания этих блокировок, и одновременного удержания их обеих. |
Блокировки схемы
Обозначение блокировки:
- Schema modification (Sch-M) – блокировка изменения схемы
- Schema stability (Sch-S) – блокировка стабильности схемы
Блокировка изменения схемы (Sch-M) в SQL Server применяется с операциями языка DDL для таблиц, например, при добавлении столбца или очистке таблицы. Пока удерживается блокировка изменения схемы (Sch-M), одновременный доступ к таблице запрещен. Это означает, что любые операции вне блокировки изменения схемы (Sch-M) будут запрещены до снятия блокировки.
Также блокировка изменения схемы (Sch-M) применяется с некоторыми операциями языка обработки данных, например, усечением таблиц (TRUNCATE TABLE), чтобы предотвратить одновременный доступ к таблице.
Блокировка стабильности схемы (Sch-S) применяется в SQL Server при компиляции и выполнении запросов. Такая блокировка не влияет на блокировки транзакций, включая эксклюзивные (X) блокировки. Поэтому другие транзакции (даже транзакции с эксклюзивной блокировкой (X) для таблицы) могут продолжать работу во время компиляции запроса. Однако одновременные операции DDL и DML, которые запрашивают блокировки изменения схемы (Sch-M), не могут выполняться над таблицей.
Блокировки массового обновления
Обозначение блокировки – Bulk Update (BU)
Блокировка массового обновления (BU) позволяет поддерживать несколько одновременных потоков массовой загрузки данных в одну и ту же таблицу и при этом запрещать доступ к таблице любым другим процессам, отличным от массовой загрузки данных.
SQL Server использует блокировки массового обновления (BU) в следующих случаях:
- Используется инструкция BULK INSERT, функция OPENROWSET(BULK) или одна из таких команд массовой вставки API, как .NET SqlBulkCopy, OLEDB Fast Load APIs или ODBC Bulk Copy APIs, для массового копирования данных в таблицу;
- Используется хинт TABLOCK или установлен параметр таблицы table lock on bulk load с помощью хранимой процедуры sp_tableoption.
Примечание! В отличие от инструкции BULK INSERT, которая удерживает менее строгую блокировку массового обновления (BU), инструкция INSERT INTO…SELECT с указанием TABLOCK удерживает блокировку таблицы с намерением эксклюзивного доступа (IX). Это означает, что отсутствует возможность вставки строк с помощью параллельных операций вставки.
Блокировки диапазона строк
Такие блокировки защищают диапазон строк, неявно включенный в набор записей, который считывает инструкция T-SQL при использовании уровня изоляции Serializable.
Иными словами, при данном уровне изоляции необходимо, чтобы любой запрос, выполняемый в транзакции, получал одинаковый набор строк при каждом выполнении в рамках этой транзакции. Блокировка диапазона ключей обеспечивает выполнение этого требования, запрещая другим транзакциям вставку новых строк, ключи которых попадают в считанный диапазон ключей, тем самым предотвращая фантомное чтение.
Блокировка диапазона ключей применяется к индексу, указывая начальное и конечное значения ключа. Данная блокировка предотвращает все попытки вставки, обновления или удаления строк со значением ключа, находящимся в этом диапазоне, поскольку для выполнения этих операций потребуется получение блокировки индекса.
Блокировки диапазона ключей содержат и компонент диапазона, и компонент строки:
- Компонент диапазона соответствует режиму блокировки, защищающему диапазон между любыми двумя последовательными элементами индекса;
- Компонент строки соответствует режиму блокировки, защищающему сами элементы индекса.
Режимы блокировки диапазона ключей состоят из двух частей. Первая представляет собой тип блокировки, используемой для блокировки диапазона индекса, а вторая представляет тип блокировки, используемой для блокировки конкретных ключей, эти две части соединены дефисом (-).
| Диапазон | Строка | Режим | Описание |
| RangeS | S | RangeS-S | Блокировка общего диапазона и общего ресурса |
| RangeS | U | RangeS-U | Совмещаемая блокировка диапазона, блокировка обновления ресурса |
| RangeI | NULL | RangeI-N | Блокировка диапазона для вставки, блокировка ресурса не определена. Используется для проверки диапазонов перед вставкой новых ключей в индекс |
| RangeX | X | RangeX-X | Эксклюзивная блокировка диапазона, эксклюзивная блокировка ресурса. Используется при обновлении ключа в диапазоне. |
Заметка! Всем тем, кто только начинает свое знакомство с языком SQL, рекомендую прочитать книгу «SQL код» – это самоучитель по языку SQL для начинающих программистов. В ней очень подробно рассмотрены основные конструкции языка.
На сегодня это все, надеюсь, материал был Вам полезен!
