Любопытный случай… блокировки BULK_OPERATION при просмотре кучи (heap) с NOLOCK

Автор: Paul Randal, The Curious Case of… the BULK_OPERATION lock during a heap NOLOCK scan

Мне пришёл вопрос по электронной почте: один специалист заметил, что во время выполнения просмотра кучи с использованием NOLOCK на всю продолжительность операции на куче удерживалась блокировка BULK_OPERATION. Вопрос заключался в том, зачем нужна эта блокировка, ведь просмотр с NOLOCK, казалось бы, не должно читать проблемные страницы?

Ответ заключается в том, что эта дополнительная блокировка нужна именно потому, что сканирование с NOLOCK может прочитать проблемную страницу, если в тот же момент над кучей выполняется операция массовой загрузки (bulk operation).

Чтобы показать, как выглядит эта блокировка, я создал большую кучу, запустил запрос SELECT * с указанием WITH (NOLOCK), а затем выполнил следующий код в другом окне:

SELECT
    [resource_type],
    [resource_subtype],
    [resource_associated_entity_id],
    [request_mode]
FROM sys.dm_tran_locks
WHERE
    [resource_type] != N'DATABASE';
GO
    

Результаты были следующими:

resource_type resource_subtype resource_associated_entity_id request_mode
------------- ---------------- ----------------------------- ------------
HOBT          BULK_OPERATION   72057594042449920             S
OBJECT                         2105058535                    Sch-S
    

Что означают эти блокировки? Прежде чем углубиться в это, я объясню, как работают две эти операции.

Просмотр с NOLOCK — это, по сути, неупорядоченное чтение страниц объекта, которое выполняется путём загрузки в объект просмотра списка IAM-страниц. Просмотр проходит по IAM-страницам, отыскивая распределённые экстенты, проверяя статус распределения страниц в найденном экстенте с помощью соответствующей PFS-страницы, а затем обрабатывая все распределённые страницы.

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

Блокировка стабильности схемы объекта (OBJECT Sch-S) необходима, по сути, для предотвращения изменения цепочек IAM во время выполнения сканирования.

Блокировка BULK_OPERATION предотвращает выполнение массовой загрузки в то время, когда происходит просмотр с NOLOCK (или версионный просмотр). Она захватывается в режиме S (разделяемая), что позволяет выполняться нескольким одновременным просмотров. Массовая загрузка будет захватывать эту блокировку в режиме IX (совместная монопольная), не позволяя начаться любым просмотрам с NOLOCK или версионным просмотрам до завершения массовой загрузки. Она известна как «блокировка подресурса hobt» (hobt subresource lock). Единственная другая подобная блокировка подресурса hobt, которую я могу припомнить, — это та, что я добавил в SQL Server 2000 для предотвращения одновременного выполнения двух операций DBCC INDEXDEFRAG над одним индексом (которая изначально была блокировкой подресурса индекса, пока в SQL Server 2005 не добавили hobt) — она будет отображаться как INDEX_REORGANIZE в выводе из sys.dm_tran_locks.

Таким образом, поскольку массовая загрузка может создавать страницы, которые кажутся распределёнными, но ещё не отформатированными, они могут быть выбраны просмотром с NOLOCK или версионным просмотром и привести к аварийному завершению.

Суть: использование блокировки подресурса — это самый простой способ согласовать несовместимые операции, не вызывая при этом никаких других проблем с блокировками.