(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
So what do these locks mean? Before I go into that, I’ll explain how the two operations work.
A NOLOCK scan is basically an unordered scan of the pages in the object and this is done by loading a scanning object with a list of the IAM pages. The scan runs through the IAM pages, looking for allocated extents, checking the allocation status of the pages in an allocated extent using the relevant PFS page, and then processing any allocated pages.
A bulk load operation uses a more efficient allocation mechanism that one-page-at-a-time, as that would require generating a log record for each page being allocated, marking it as such in the relevant PFS page. Instead it allocates an extent and marks all 8 pages as allocated at once, generating a single log record, and then formatting them as the load progresses. When the load finishes, the very last extent that was allocated may have one or more pages marked allocated that weren’t used, so these are then deallocated again.
The OBJECT schema-stability lock is essentially to prevent the IAM chains from changing while the scan is progressing
The BULK_OPERATION lock prevents a bulk load from happening while a NOLOCK scan (or versioned scan) is happening. It’s acquired in S mode so there can be multiple concurrent scans occurring. A bulk load will acquire the lock in IX mode, preventing any NOLOCK or versioned scans from starting until the bulk load has finished. It’s known as a “hobt subresource lock”, and the only other hobt subresource lock I can think of is the one I added in SQL Server 2000 to prevent two DBCC INDEXDEFRAG operations from running on an index (which was originally an index subresource lock until hobts were added in SQL Server 2005) and will show up as INDEX_REORGANIZE in the output from sys.dm_tran_locks.
And so because a bulk load can create pages that seem to be allocated but are not formatted, they could be picked up by a NOLOCK or versioned scan and cause a crash.
Bottom line: using a subresource lock is the easiest way to coordinate between the incompatible operations without causing any other locking problems.