NOLOCK is definitely not what you may expect …

For those who still believe that using nolock hint is a lock-free process, keep reading this blog post. I wrote a previous blog post 3 years ago, when I was at one of my customer and we had an interesting discussion about the nolock hint with reporting queries. I convinced him that nolock continues to generate locks even if the shared lock was evicted in this case.

So this time, I experienced an interesting another case where we were blocked by a SELECT query that used the nolock hint. I can’t reveal the real scenario but I was able to reproduce this case in my lab environment by using the famous AdventureWorks database.

The SELECT query is pretty basic as follows:

SELECT * 
FROM dbo.bigTransactionHistory_stg as T (nolock) 
WHERE Quantity between 1 and 100

To relate with my customer context, this real query aimed to analyze some data from different staging tables during the load data activity.

Then let’s have a look at query related to the customer’s bulk insert activity into the staging tables. The load activity was performed by scheduled robots.

begin tran; 

insert [dbo].[bigTransactionHistory_stg] with (tablock) 

select * 
from  [dbo].[bigTransactionHistory]

...

commit tran

According to the Microsoft documentation here, my customer wanted to use the tablock hint in order to guarantee the minimally logging behaviour of the INSERT statement. The database recovery model was set to SIMPLE in this context.

Here a picture of the locking situation during the bulk data insert. At the same time the customer tried to get data from the staging tables for troubleshooting purposes but it was blocked even with the nolock hint.

About the INSERT statement 

Firstly, you may notice that the lock manager has granted an Sch-S lock to the table itself (issued by the tablelock hint) as well as a special intended exclusive lock with the BULK_OPERATION subtype that concerned the allocation unit id = 72057594059554816). This allocation unit corresponds to the index 0 of the table bigTransactionHistory_stg.  Finally there exist other exclusive locks that concern the page itself.

About the SELECT statement

At the same time, we may notice a pending session (session id = 56) related to our SELECT statement that is waiting on the same hobtid than the previous INSERT statement with the same BULK_OPERATION lock subtype and an attempt to get a shared locked to the concerned resource. You may also notice an additional granted Sch-S lock as documented in the BOL here.

Well, this surprising behaviour is in fact expected because the both locks are incompatible on the same resource.

The bottom line is that we have to remember that adding the nolock hint to a query is not a lock-free operation. Reading heap structures (at certain conditions documented here), could generate both Sch-S locks and special other bulk-operation subtype locks in order to prevent reading unformatted pages (referring to Paul Randal’s Pass summit session in 2011 #Myth 34).

See you!

By David Barbarin