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

by

The question:

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?

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

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?


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 Comment