Does selecting data from heap (table without clustered index) with nolock block writers?

All we need is an easy explanation of the problem, so here it is.

I tried replicating the scenario given here: https://www.sqlskills.com/blogs/paul/the-curious-case-of-the-bulk_operation-lock-during-a-heap-nolock-scan/

Created 2 tables – table1 with clustered index, table2 without any index.

When I write a select query with (lock) on table1, there is Sch-S lock applied. Similarly, when I write the same query on table2, there also it is a Sch-S lock.

Neither case applied the S lock like shown in the above link.

To check locks I use the following query:

SELECT * FROM sys.dm_tran_locks
  WHERE resource_database_id = DB_ID()
  AND resource_associated_entity_id = OBJECT_ID(N'dbo.table1');

Does selecting data from heap (table without clustered index) with nolock block writers?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Does selecting data from heap (table without clustered index) with nolock block writers?

Only writers performing a bulk load*, as explained by Paul Randal in the link provided:

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.

* bulk load here refers to the special mechanism that allows multiple concurrent bulk load to heap tables without indexes when using bcp, BULK INSERT, or SSIS. In these cases a BU table lock is taken instead of an exclusive lock. See the Data Loading Performance Guide for more details.

The special BULK_OPERATION lock does not conflict with ‘ordinary’ table, page, or row locks taken by ‘ordinary’ inserts/updates/deletes.

Bulk loading clustered tables is possible but uses a different mechanism where the BULK_OPERATION lock is not required.

Method 2

It worked for me. It helps if you flush the page cache before the table scan to slow it down. eg

dbcc dropcleanbuffers
go
select avg(somecol) ap
from someheap with (nolock)

And check the query plan to make sure you’re getting an unordered table scan.

Another trick for observing quick things is to introduce a time-wasting scalar-valued function like:

create or alter function dbo.delay(@ms int)
returns int as
begin
   declare @start datetime2 = sysdatetime()
   while datediff(ms,@start,sysdatetime()) < @ms
   begin
     declare @h bigint = checksum(0x29374190273401923470912384709123)
   end
   return @ms
end

So you can make a scan as slow as you want, so long as you have a streaming plan.

In AdventureWorksDW, I created a large heap like this

select top 25000000 s.* 
into someheap 
from FactInternetSales s,  FactInternetSales s2

then ran

dbcc dropcleanbuffers
go
select avg(UnitPrice) ap
from someheap with (nolock)

which took long enough to switch over and run

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

and see the BULK_OPERATION lock

Does selecting data from heap (table without clustered index) with nolock block writers?

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply