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



  • 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?



  • 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

    enter image description here




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2