The Curious Case of… the BULK_OPERATION lock with a heap NOLOCK scan

(The Curious Case of… used to be part of our bi-weekly newsletter but we decided to make it a regular blog post instead so it can sometimes be more frequent. It covers something interesting one of us encountered when working with a client, doing some testing, or were asked in a random question from the community.) I had an email question over the weekend where someone noticed that while executing a scan of a heap using NOLOCK, there was a BULK_OPERATION lock held on the heap for the duration of the scan. The question was why is the BULK_OPERATION lock needed, as surely there’s no way for the NOLOCK scan to read a problematic page? Well, the answer is that the extra lock is needed *precisely* because the NOLOCK scan *can* read a problematic page if there’s a bulk operation happening on the heap at the same time. To show you what the lock looks like, I created a large heap, kicked off a SELECT * using WITH (NOLOCK) and then ran the following code in another window: SELECT [resource_type], [resource_subtype], [resource_associated_entity_id], [request_mode] FROM sys.dm_tran_locks WHERE [resource_type] != N’DATABASE’; GO And the results were: resource_type resource_subtype resource_associated_entity_id request_mode HOBT BULK_OPERATION 72057594042449920 S OBJECT 2105058535 Sch-S [/sourecode]