sql server - Why is a NOLOCK query blocking reads/writes?

Question

We have a view in SQL Server (2016) DB that uses NOLOCK hints for all the tables it selects from. We're finding that when this view is being consumed, it is actually blocking reads and writes.

According to this, a NOLOCK query issues a Sch-S lock and thus will only block things that, like DDL and re-index jobs. However, we're seeing DML read/write being blocked also, causing our whole app to go down. Why?

Note: the select from View query does not use NOLOCK hint, but not sure this should matter if the view itself is using NOLOCKs.

Note2: apologies for the light-on-facts question--I have limited privileges in the environment, and it's difficult to investigate using queries that require elevated permissions, so hoping for a purely theoretical analysis.

Thanks!

Answer 1

Myth: NOLOCK / READ UNCOMMITTED means no locks

First off, they're the same thing And they do have to acquire some locks:

• Schema-stability locks (Sch-S) to prevent the structure of the table/index changing
• BULK_OPERATION locks on heaps to prevent reading of unformatted pages

And they still have to take latches to access the physical page images in memory, so there's still some potential for blocking at the latch level

https://tenbulls.co.uk/2011/10/14/nolock-hits-mythbusters/ https://dba.stackexchange.com/questions/8627/shared-lock-issued-on-isolationlevel-readuncommitted

I have checked BULK_OPERATION (HBT [BULK_OPERATION]) locks are easily reproduced for a table with no clustered index and select with nolock hint. If clustered index exists than HoBT lock is not applied.

So, it should be a rare case but still possible that using WITH (NOLOCK) you will have other queries blocked.