The Truth About NOLOCK Hints

The Truth About NOLOCK Hints

They need a MasterLock hint for MCMs to use
There was a post this week from a well-known blogger who was investigating the NOLOCK locking hint. He says that he was asked whether a query using NOLOCK hint takes locks or not. After he responded that NOLOCK does not take any locks, the person who posed the question suggested that he think on it further. Through some testing, he came to the conclusion that a NOLOCK hint does indeed take a shared schema lock on the object that it is querying.

I wish he had investigated further. There were some issues with his testing method, and he only got part of the picture. This will all make more sense when we take a look at his test.

His Test:

He ran the following query:

SELECT *
FROM sys.all_objects a WITH (NOLOCK)
CROSS JOIN sys.all_objects b WITH (NOLOCK)

And then used this code to check the locks taken by the query (in another query window I’m assuming):

I noticed some things right away. First of all, there were less locks than I expected to see, but that’s easily explainable by the fact that he’s simply querying the system catalogs in the master database. All queries whether using NOLOCK or not should be taking a shared lock on the database. This prevents certain operationsto the database (such as dropping it) while there are queries running against it. The fact that it did not take a shared database lock is because the actual objects are in the resource database and you can’t take a lock against that database.

Additionally, I expected the query to expand out to more than underlying tables. The view sys.all_objects unions together two other system views, sys.objects and sys.system_objects. These two views in turn expand out to sys.objects$, sys.system_objects$, and sys.syspalnames. So if NOLOCK places a lock on the underlying table, why is there only one lock? When I ran his test, the answer became clear. He wasn’t querying fast enough. The locks taken on sys.system_objects$ and sys.syspalnames aren’t needed long term. You have to be quick to catch those locks.

Second thing I noticed is that the object being locked is in a different database than the one he is querying. It is in database ID of 32767 which is always the ID of the resource database, a hidden system database that is normally not directly accessible (there are ways to access it directly, but that’s for another topic). Makes perfect sense that would be the case because the catalog views in the master database are views over tables stored in the resource database. So the table objects for the views are actually in the resource database and that is why we see the lock there.

If he had performed his test on actual tables in the master database or better yet on a user table in a user database, he would have seen different results. I’m going to run the test that way, but first, let’s re-run his test while taking a closer look at the locking. I’m going to make some modifications to the query so that we can see the names of the objects being locked. In order to get the object names of tables in the resource database, I’m going to connect the query window where the check runs using the dedicated admin connection (DAC).

My New Query to Check Locks

For this query, I am first taking note of the SPID that the other query is running in to ensure that I only see locks being generated by that session. My SPID was 51, and if you run the test, you will need to set the session ID equal to your SPID.

If I keep checking the locks repeatedly, we eventually get down to the lock that was noted by the previous test:

resource_database_id

request_mode

request_type

ResourceName

32767

Sch-S

LOCK

sysobjrdb

So it would appear that there is even further obfuscation going on under the covers for these catalog view in the master database. Four objects got locked, two in the resource database and two in master.

A New Test

All of this backdoor stuff with master and resource database only serves to confuse the results we’re trying to see. Let’s simplify it now and query a user table in a user database. For this test, I’m going to query the sales.SalesOrderDetails table in the AdventureWorks2008R2 database.

The Query

I’m going to use the modified query above to check the locks and the query below to generate the locks.

IMHO, I would always avoid using system tables and the master database for showing a locking hint. Master database queries most of its information from the mssqlsystemresource database starting from SQL Server 2005. Since the resource database is a hidden database, it shouldn’t be used for any kind of repro to display any locking behavior as you do not know which tables you are touching in the background. My normal repro for a locking scenario is to start an explicit transaction and then execute the query,

[…] The Truth About NOLOCK Hints – Another great post from Robert L. Davis(Blog|Twitter) this time looking at the locking behaviour of the NOLOCK hint. I think you’ll be surprised to see what actually goes on. […]