Миф №8 – Фоновые операции с индексами не вызывают блокировок
FALSE
Фоновые операции с индексами вызывают краткосрочные блокировки в начале и в конце операции, что может привести к значительным проблемам с производительностью.
Примечание: Под фоновой операцией с индекcом понимается инструкция CREATE INDEX, ALTER INDEX, DROP INDEX и ALTER TABLE выполняющаяся с параметром ONLINE.
В начале фоновой оперции с индексом накладывается совмещаемая блокировка (S lock) на таблицу. Эта блокировка удерживается пока новый, пустой индекс не будет создан; версионный скан старого индекса не будет запущен; и младший номер схемы таблицы не сместится на 1.
Проблема в том, что эта совмещаемая блокировка стоит в очереди с остальными блокировками таблицы. Никакие блокировки, которые несовместимы с ней не могут быть получены, пока S lock стоит в очереди, либо уже получена. Это значит, что операции обновления данных не выполняются пока S lock не будет получена и операция не закончится. Кроме этого S lock не может быть получена, пока все, выполняющиеся в данный момент, операции UPDATE не выполнятся и пока IX или X блокировки не будут сняты.
После того как блокировка была установлена (это очень быстро), блокировка снимается, но вы можете видеть как возникают блокировки обновления. Смещение младшего номера версии схемы приводит к тому, что все планы запросов, которые обновляют таблицу, перекомпилируются. В этом случае операторы плана запроса будут использовать уже новый индекс.
Во время проведения длительных операций с индексом никакие блокировки не удерживаются (под "длительным" следует понимать время, обычно занимаемое при перестроении индекса).
Когда операция с индексом заканчивается, старый и новый индекс связаны, до тех пор пока происходят операции обновления. Блокировка схемы (SCH_M lock mode) необходима для завершения операции. Вы можете представлять себе это как super-table-X lock – это необходимо для смещения старшего номера версии таблицы – никакие операции не выполняются над таблицей и никакие планы не могут быть перекомпилированы, пока удерживается эта блокировка.
Эта проблема с блокировками похожа на ту, что мы разбирали вначале, возникающую при старте операции – но в этот раз никакие операции чтения или записи не выполняются, пока блокировка находится в состоянии ожидания либо получена, и не может быть получена, пока все текущие операции чтения/записи не будут завершены.
Во время удержания блокировки , структуры распределения старых индексов освобождаются и индекс ставится в очередь на отложенное удаление, в то же время структуры распределения нового индекса связываются с метаданными старого (поэтому ID у индекса не меняется), старший номер таблицы смещается, и вуаля! Вы получили новенький индекс.
Как видите, есть возможность для возникновения продолжительных блокировок в начале и в конце операции. Скорее это должно называться "Почти фоновые операции с индексами", но это не очень хороший маркетинговый ход…
Узнать больше о фоновых операциях с индексами вы можете здесь - Online Indexing Operations in SQL Server 2005.
Об авторе:
Пол С. Рэндал (Paul S. Randal) — генеральный директор SQLskills.com является MVP по SQL Server. Он работал в группе подсистемы хранилища SQL Server в корпорации Майкрософт с 1999 по 2007 г. Пол является автором DBCC CHECKDB/восстановления SQL Server 2005 и в ходе разработки SQL Server 2008 отвечал за программу базовой подсистемы хранилища. Пол является специалистом по аварийному восстановлению, высокой доступности и обслуживанию базы данных и часто дает презентации на различных конференциях по всему миру. Он ведет блог по адресу SQLskills.com/blogs/paul.
Ссылки по теме:
- Оригинал статьи
- Online Indexing Operations in SQL Server 2005
- Выполнение операции с индексами в сети
- О фоновых операциях с индексом
- Правила выполнения фоновых операций с индексами