Когда дело доходит до извлечения данных из базы данных SQL Server, разработчики часто используют подсказку NOLOCK, чтобы предотвратить блокировку и улучшить производительность. Однако существует распространенное заблуждение о поведении NOLOCK, которое может привести к неожиданным последствиям.
NOLOCK, также известный как уровень изоляции “читать незафиксированное”, позволяет запросу читать данные без получения блокировок. Это может быть полезно в ситуациях, когда согласованность данных не является критической, например, при выполнении длительных запросов ночью. Предполагается, что NOLOCK-запросы не будут блокировать другие процессы на сервере.
Однако недавние открытия показали, что NOLOCK-запросы могут фактически блокировать выполнение других запросов. Это происходит потому, что хотя NOLOCK не блокирует данные на уровне строк, он блокирует структуру таблицы с помощью блокировки стабильности схемы (Sch-S). Блокировка Sch-S предотвращает изменение структуры таблицы во время выполнения запроса.
Команды, запрашивающие блокировку модификации схемы (Sch-M), такие как перестроение индексов или перекомпиляция таблицы, блокируются блокировкой Sch-S, полученной NOLOCK-запросами. Это означает, что те самые запросы, которые вы пытались избежать влияния с помощью NOLOCK, могут оказаться заблокированными, вызывая задержки в заданиях по обслуживанию базы данных и последующих запросах.
Давайте рассмотрим пример, чтобы понять это поведение. Предположим, у вас есть долго выполняющийся NOLOCK-запрос, который читает миллиард записей. Во время выполнения этого запроса, если вы попытаетесь перестроить индекс на той же таблице, операция перестроения будет заблокирована блокировкой Sch-S, полученной NOLOCK-запросом.
Это блокирующее поведение может иметь каскадный эффект, при котором последующие запросы к таблице также блокируются блокировкой Sch-M, запрошенной операцией перестроения индекса. Это может значительно повлиять на своевременность заданий по обслуживанию и общую производительность запросов.
Итак, какие решения существуют для устранения этой проблемы?
Решение №1: Не выполняйте длительные запросы во время окон обслуживания
Один из подходов – избегать выполнения длительных запросов во время запланированного времени выполнения заданий по обслуживанию. Таким образом, вы можете предотвратить задержки в задачах по обслуживанию и последующих запросах. Однако это означает, что ваш долго выполняющийся запрос должен быть выполнен в другое время, что может повлиять на производительность сервера в более загруженные периоды.
Решение №2: Используйте опцию WAIT_AT_LOW_PRIORITY для перестроения индексов
Начиная с SQL Server 2014, вы можете использовать опцию WAIT_AT_LOW_PRIORITY при выполнении онлайн-перестроения индексов. Эта опция позволяет блокирующим запросам SELECT завершиться в течение указанного времени перед их прерыванием и продолжением перестроения индекса. Кроме того, вы можете установить опцию ABORT_AFTER_WAIT для прерывания самой операции перестроения, позволяя NOLOCK-запросу завершиться без блокировки других запросов.
Решение №3: Программный подход с REBUILD или REORGANIZE
Другое решение – реализовать программный подход, который пытается перестроить индекс, но при обнаружении блокировки Sch-M переходит к его реорганизации. Такой подход позволяет выполнять обслуживание индексов, минимизируя влияние на другие запросы. Вы можете настроить это решение в соответствии с вашими конкретными требованиями, например, установить тайм-аут или периодически проверять блокировку.
Важно отметить, что нет универсального решения для этой проблемы. Выбор решения будет зависеть от вашей конкретной ситуации и компромиссов, которые вы готовы сделать.
В заключение, хотя NOLOCK может быть полезным инструментом для улучшения производительности запросов, важно понимать его влияние на другие запросы и задачи по обслуживанию. Будучи осведомленным о потенциальном блокировочном поведении и реализуя соответствующие решения, вы можете обеспечить более гладкую и эффективную работу вашей базы данных SQL Server.