The Truth About NOLOCK Hints
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):
SELECT resource_database_id, request_mode, request_type, * FROM sys.dm_tran_locks
And these are the results he reported:
resource_database_id | request_mode | request_type | resource_type |
32767 | Sch-S | LOCK | OBJECT |
My Comments on His Test and Results
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.
SELECT resource_database_id, request_mode, request_type, ResourceName = Case resource_type When 'database' Then db_name(resource_database_id) When 'object' Then OBJECT_NAME(resource_associated_entity_id, resource_database_id) End FROM sys.dm_tran_locks WHERE request_session_id = 51
If I immediately check the locks taken, these are the locks I see:
resource_database_id | request_mode | request_type | ResourceName |
1 | Sch-S | LOCK | sysschobjs |
32767 | Sch-S | LOCK | syspalnames |
1 | Sch-S | LOCK | syssingleobjrefs |
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.
Select * from Sales.SalesOrderDetail a with(nolock) Cross Join Sales.SalesOrderDetail b with(nolock)
The results I see no matter how quickly I check or how many time I recheck while it is running is as follows:
resource_database_id | request_mode | request_type | ResourceName |
10 | S | LOCK | AdventureWorks2008R2 |
10 | Sch-S | LOCK | SalesOrderDetail |
This test shows exactly what I expected to see. A query using the NOLOCK hint takes a shared schema lock on the object (table or partition) and a shared lock on the database.
Dave Ballantyne
On the back of this , using the Lock:Acquired event in profiler i could see a Sch-S lock there too.
I used the simpler,
” Select *
from Sales.SalesOrderDetail a with(nolock)”
using your cross join more lock acquires were reported. Just saying š
SQLSoldier
Good info Dave!! The only reason for using the Cross Join is to make the transaction last longer.
Amit Banerjee
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,
SQLSoldier
Agree 100%. Running his test against the system catalogs in master were his first mistake.
Pinal Dave
Thank you Robert,
I have learned something new. You are correct.
Kind Regards,
Pinal
SQLSoldier
Thanks!! I’m always happy to hear when someone learns something from my posts!!
SQL SERVER – What Kind of Lock WITH (NOLOCK) Hint Takes on Object? « Journey to SQLAuthority
[…] http://www.sqlsoldier.com/wp/sqlserver/thetruthaboutnolockhints […]
Meher
I tested this in SQL 2005 as well and see the same results as SQL Server 2008. Thanks to Robert for the repro scripts.
Also Thanks you Robert for Providing the Simple DMV to check locks.
Meher
Something for the Weekend – SQL Server Links 15/05/11 | John Sansom - SQL Server DBA in the UK
[…] 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. […]