Настройка блокировки индекса
Компонент SQL Server Database Engine использует механизм динамической блокировки, при которой для большинства запросов выбирается оптимальная степень гранулярности. Не рекомендуется переопределять уровни блокировки по умолчанию, для которых установлены блокировки страниц и строк, за исключением случаев, когда методы доступа хорошо понятны и постоянны и не приходится решать проблему конкуренции за получение ресурсов. Переопределение уровня блокировки может существенно затруднить параллельный доступ к таблице или индексу. Например, задание только блокировок на уровне таблицы для крупной таблицы, к которой обращается большое количество пользователей, может привести к возникновению узких мест, так как пользователям придется ждать снятия блокировки на уровне таблицы перед доступом к таблице.
В некоторых случаях запрет блокировки страниц или строк может быть полезным, если методы доступа хорошо понятны и согласованы. Допустим, приложение базы данных использует таблицу уточняющих запросов, которая еженедельно обновляется в ходе пакетной обработки. Параллельные обращения агентов чтения к таблице выполняются с совмещаемой блокировкой (S), а еженедельное пакетное обновление получает доступ к таблице с монопольной блокировкой (X). Отключение блокировки строк или страниц в таблице уменьшает затраты ресурсов на управление блокировками в течение недели благодаря возможности параллельного доступа агентов чтения через общие блокировки таблицы. При выполнении пакетного задания обновление может быть проведено более эффективно, так как ему предоставляется монопольная блокировка таблицы.
Отключение блокировки страниц и строк может быть как приемлемым, так и нет, поскольку еженедельное пакетное обновление будет запрещать параллельные обращения агентов чтения к таблице в процессе обновления. Если пакетное задание изменяет только строки или страницы, можно изменить уровень блокировки, чтобы разрешить блокировку на уровне строк и страниц, что позволит другим сеансам выполнять чтение из таблицы без блокировки. Если в пакетном задании имеется большое количество обновлений, получение монопольной блокировки на таблицу может быть самым эффективным способом завершения пакетного задания.
Иногда взаимоблокировка возникает, когда две параллельные операции накладывают блокировки на уровне строк в одной таблице, а затем взаимоблокируются, так как обеим нужно блокировать страницу. Запрет блокировок на уровне строк вынуждает одну из операций ждать, что позволяет избежать взаимоблокировки.
Степень гранулярности блокировок индекса настраивается при помощи инструкций CREATE INDEX и ALTER INDEX. Настройки блокировок применяются как к страницам индекса, так и к страницам таблиц. Ее можно настроить в ограничениях PRIMARY KEY и UNIQUE инструкций CREATE TABLE и ALTER TABLE. Кроме того, системная хранимая процедура sp_indexoption, обеспечивающая обратную совместимость, также позволяет настроить степень гранулярности. Текущее значение параметра для заданного индекса можно узнать при помощи функции INDEXPROPERTY. Для любого индекса можно запретить блокировку страниц, блокировку строк или их сочетание.
Запрещенные блокировки |
При обращении к индексу используются |
|---|---|
Уровня страниц |
Блокировки уровня строк и таблиц |
Уровня строк |
Блокировки уровня страниц и таблиц |
Уровня строк и страниц |
Блокировки уровня таблиц |