Единороги, радуги и операции с индексами в режиме ONLINE

Автор: Paul Randal, A SQL Server DBA myth a day: (8/30) unicorns, rainbows, and online index operations

Миф: Операции с индексами в режиме ONLINE не устанавливают блокировок.

ЛОЖЬ

Операции с индексами в режиме ONLINE — это не сплошные единороги и радуги (об информации по единорогам и радугам см. http://whiteboardunicorns.com/ — безопасно для рабочего просмотра).

Операции с индексами в режиме ONLINE устанавливают кратковременные блокировки в начале и в конце операции, что может вызвать серьёзные проблемы с блокировками.

В начале операции с индексом в режиме ONLINE требуется разделяемая блокировка таблицы (режим S-lock). Эта блокировка удерживается, пока создаётся новый пустой индекс, запускается версионное сканирование старого индекса и увеличивается на единицу младшая версия схемы таблицы.

Проблема в том, что эта S-блокировка ставится в очередь вместе со всеми другими блокировками таблицы. Никакие блокировки, несовместимые с S-блокировкой таблицы, не могут быть предоставлены, пока S-блокировка находится в очереди или уже предоставлена. Это означает, что операции обновления блокируются до тех пор, пока блокировка не будет предоставлена и операция не начнётся. Аналогично, S-блокировка не может быть предоставлена, пока не завершатся все текущие операции обновления и не будут сняты их IX- или X-блокировки таблицы.

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

Пока выполняется длительная часть операции с индексом (где «длительная» — это обычное время перестроения вашего индекса), никакие блокировки не удерживаются.

Когда операция с индексом завершена, новый и старый индексы синхронизированы в плане обновлений. Для завершения операции требуется блокировка модификации схемы (режим SCH_M lock). Можно думать о ней как о супер-монопольной блокировке таблицы (X-lock) — она необходима для увеличения старшей версии таблицы — никакие операции не могут выполняться с таблицей, и никакие планы не могут компилироваться, пока удерживается эта блокировка.

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

Как только блокировка получена, структуры размещения старого индекса отключаются и помещаются в очередь отложенного удаления, структуры размещения нового индекса подключаются к метаданным старого индекса (чтобы идентификатор индекса не изменился), старшая версия таблицы увеличивается, и, вуаля! У вас есть сверкающий новый индекс.

Как видите — существует множество возможностей для блокировок в начале и конце операции с индексом в режиме ONLINE. Так что её следовало бы назвать «почти онлайн-операцией с индексами», но это не такой уж удачный маркетинговый термин…

Вы можете прочитать больше об операциях с индексами в режиме ONLINE в техническом документе Online Indexing Operations in SQL Server 2005.