{"id":5221,"date":"2021-11-01T14:26:47","date_gmt":"2021-11-01T21:26:47","guid":{"rendered":"https:\/\/www.sqlskills.com\/blogs\/paul\/?p=5221"},"modified":"2021-11-01T14:26:47","modified_gmt":"2021-11-01T21:26:47","slug":"the-curious-case-of-the-bulk_operation-lock-during-a-heap-nolock-scan","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-the-bulk_operation-lock-during-a-heap-nolock-scan\/","title":{"rendered":"The Curious Case of&#8230; the BULK_OPERATION lock during a heap NOLOCK scan"},"content":{"rendered":"<p style=\"text-align: justify;\"><em>(The Curious Case of\u2026<\/em>\u00a0used to be part of our bi-weekly\u00a0<a href=\"https:\/\/www.sqlskills.com\/join-the-sqlskills-insider-community\/\" target=\"_blank\" rel=\"noopener noreferrer\">newsletter<\/a>\u00a0but we decided to make it a\u00a0regular 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.)<\/p>\n<p style=\"text-align: justify;\">I had an email question over the weekend where someone noticed that while executing a scan of a heap using <em>NOLOCK<\/em>, there was a <em>BULK_OPERATION<\/em> lock held on the heap for the duration of the scan. The question was why is the\u00a0<em>BULK_OPERATION<\/em> lock needed, as surely there&#8217;s no way for the\u00a0<em>NOLOCK<\/em> scan to read a problematic page?<\/p>\n<p style=\"text-align: justify;\">Well, the answer is that the extra lock is needed *precisely* because the <em>NOLOCK<\/em> scan *can* read a problematic page if there&#8217;s a bulk operation happening on the heap at the same time.<\/p>\n<p style=\"text-align: justify;\">To show you what the lock looks like, I created a large heap, kicked off a\u00a0<em>SELECT *<\/em> using\u00a0<em>WITH (NOLOCK)<\/em> and then ran the following code in another window:<\/p>\n<pre class=\"brush: plain; gutter: false; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nSELECT\r\n    &#x5B;resource_type],\r\n    &#x5B;resource_subtype],\r\n    &#x5B;resource_associated_entity_id],\r\n    &#x5B;request_mode]\r\nFROM sys.dm_tran_locks\r\nWHERE\r\n    &#x5B;resource_type] != N'DATABASE';\r\nGO\r\n<\/pre>\n<p>And the results were:<\/p>\n<pre class=\"brush: plain; gutter: false; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nresource_type resource_subtype resource_associated_entity_id request_mode\r\n------------- ---------------- ----------------------------- ------------\r\nHOBT          BULK_OPERATION   72057594042449920             S\r\nOBJECT                         2105058535                    Sch-S\r\n<\/pre>\n<p style=\"text-align: justify;\">So what do these locks mean? Before I go into that, I&#8217;ll explain how the two operations work.<\/p>\n<p style=\"text-align: justify;\">A <em>NOLOCK<\/em> 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.<\/p>\n<p style=\"text-align: justify;\">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&#8217;t used, so these are then deallocated again.<\/p>\n<p>The\u00a0<em>OBJECT<\/em> schema-stability lock is essentially to prevent the IAM chains from changing while the scan is progressing<\/p>\n<p style=\"text-align: justify;\">The <em>BULK_OPERATION<\/em> lock prevents a bulk load from happening while a <em>NOLOCK<\/em> scan (or versioned scan) is happening. It&#8217;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 <em>NOLOCK<\/em> or versioned scans from starting until the bulk load has finished. It&#8217;s known as a &#8220;hobt subresource lock&#8221;, and the only other hobt subresource lock I can think of is the one I added in SQL Server 2000 to prevent two <em>DBCC INDEXDEFRAG<\/em> 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 <em>INDEX_REORGANIZE<\/em> in the output from <em>sys.dm_tran_locks<\/em>.<\/p>\n<p>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\u00a0<em>NOLOCK<\/em> or versioned scan and cause a crash.<\/p>\n<p>Bottom line: using a subresource lock is the easiest way to coordinate between the incompatible operations without causing any other locking problems.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>(The Curious Case of\u2026\u00a0used to be part of our bi-weekly\u00a0newsletter\u00a0but we decided to make it a\u00a0regular 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 [&hellip;]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[48,57,62,115],"tags":[],"class_list":["post-5221","post","type-post","status-publish","format-standard","hentry","category-inside-the-storage-engine","category-locking","category-on-disk-structures","category-the-curious-case-of"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>The Curious Case of... the BULK_OPERATION lock during a heap NOLOCK scan - Paul S. Randal<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-the-bulk_operation-lock-during-a-heap-nolock-scan\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"The Curious Case of... the BULK_OPERATION lock during a heap NOLOCK scan - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"(The Curious Case of\u2026\u00a0used to be part of our bi-weekly\u00a0newsletter\u00a0but we decided to make it a\u00a0regular 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 [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-the-bulk_operation-lock-during-a-heap-nolock-scan\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2021-11-01T21:26:47+00:00\" \/>\n<meta name=\"author\" content=\"Paul Randal\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Paul Randal\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-the-bulk_operation-lock-during-a-heap-nolock-scan\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-the-bulk_operation-lock-during-a-heap-nolock-scan\/\",\"name\":\"The Curious Case of... the BULK_OPERATION lock during a heap NOLOCK scan - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2021-11-01T21:26:47+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-the-bulk_operation-lock-during-a-heap-nolock-scan\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-the-bulk_operation-lock-during-a-heap-nolock-scan\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-the-bulk_operation-lock-during-a-heap-nolock-scan\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"The Curious Case of&#8230; the BULK_OPERATION lock during a heap NOLOCK scan\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\",\"name\":\"Paul S. Randal\",\"description\":\"In Recovery...\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\",\"name\":\"Paul Randal\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"caption\":\"Paul Randal\"},\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/paul\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"The Curious Case of... the BULK_OPERATION lock during a heap NOLOCK scan - Paul S. Randal","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-the-bulk_operation-lock-during-a-heap-nolock-scan\/","og_locale":"en_US","og_type":"article","og_title":"The Curious Case of... the BULK_OPERATION lock during a heap NOLOCK scan - Paul S. Randal","og_description":"(The Curious Case of\u2026\u00a0used to be part of our bi-weekly\u00a0newsletter\u00a0but we decided to make it a\u00a0regular 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 [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-the-bulk_operation-lock-during-a-heap-nolock-scan\/","og_site_name":"Paul S. Randal","article_published_time":"2021-11-01T21:26:47+00:00","author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-the-bulk_operation-lock-during-a-heap-nolock-scan\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-the-bulk_operation-lock-during-a-heap-nolock-scan\/","name":"The Curious Case of... the BULK_OPERATION lock during a heap NOLOCK scan - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2021-11-01T21:26:47+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-the-bulk_operation-lock-during-a-heap-nolock-scan\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-the-bulk_operation-lock-during-a-heap-nolock-scan\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-the-bulk_operation-lock-during-a-heap-nolock-scan\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"The Curious Case of&#8230; the BULK_OPERATION lock during a heap NOLOCK scan"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/","name":"Paul S. Randal","description":"In Recovery...","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce","name":"Paul Randal","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","caption":"Paul Randal"},"sameAs":["http:\/\/3.209.169.194\/blogs\/paul"],"url":"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/5221","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/comments?post=5221"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/5221\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=5221"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=5221"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=5221"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}