Press "Enter" to skip to content

BULK_OPERATION Locks during a NOLOCK Scan

Paul Randal does some explaining:

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.

And don’t call me Shirley.

Click through for a demonstration of this answer.