Does NOLOCK Really Applies No Lock? – Interview Question of the Week #209

Question: Does NOLOCK Really Applies No Lock?

Answer: The answer is NOLOCK do apply the lock and it is Sch-S lock. Sch-S stands for Schema Stability. Quite a few people say it is actually Schema Shared lock and it is not correct.

Sch-S or Schema Stability lock is one of the most common types of the lock and it is applied on the object when any process does not want that particular object to change its definition. For example, if you are running a long query or index maintenance at that time, you can expect on that table there will be an Sch-S lock. This way the table schema (structure) does not change while the query is running and this way, you can avoid any unexpected results or errors.

Here is a simple script you can run on your database for any table with NOLOCK.

USE AdventureWorks2014
GO
SELECT *
FROM [Sales].[SalesOrderDetail] sod (NOLOCK)
CROSS JOIN [Sales].[SalesOrderDetail] sod1 (NOLOCK)
CROSS JOIN [Sales].[SalesOrderDetail] sod2 (NOLOCK)
CROSS JOIN [Sales].[SalesOrderDetail] sod3 (NOLOCK)
GO

Now you can run the following script, which will list the kind of the lock above query acquires on the table.

SELECT *
FROM sys.dm_tran_locks
WHERE resource_type = 'OBJECT'
GO

When you run the above script you can see following results where the query with NOLOCK has taken a schema stability lock (Sch-S) on the table. So yeah, it is not true that NOLOCK does not take any lock, it does take a lock when the query is executed.

Reference: Pinal Dave (https://blog.sqlauthority.com)