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

Question: Does NOLOCK really mean that SQL Server takes no locks?

Answer: No. NOLOCK still requires a Sch-S, schema stability, lock during query compilation and execution. Sch-S means schema stability, not “schema shared.”

The name is memorable enough to be misleading. NOLOCK concerns data-locking behavior: it allows READ UNCOMMITTED reads. It does not allow the table’s definition to change underneath the query. A concurrent schema modification lock, Sch-M, can therefore block a NOLOCK query.

Observe the distinction in a small test

Use a disposable database and two connections to it. You do not need the original four-way cross join, which can create an enormous workload. Create one row first:

-- Run once in a disposable user database, before opening the two test sessions.
IF OBJECT_ID(N'dbo.SqlaNoLock184678', N'U') IS NOT NULL
    THROW 50001, 'The experiment table already exists.', 1;
CREATE TABLE dbo.SqlaNoLock184678 (ID int NOT NULL PRIMARY KEY);
INSERT dbo.SqlaNoLock184678 VALUES (1);

In Session A, hold a schema change open:

-- Session A, in the same disposable database.
BEGIN TRANSACTION;
ALTER TABLE dbo.SqlaNoLock184678 ADD TemporaryColumn int NULL;
-- Leave this transaction open only for the short test below.

In Session B, run the NOLOCK SELECT. Its 15-second timeout bounds the wait; it does not remove the schema lock requirement.

-- Session B, in the same disposable database.
SET LOCK_TIMEOUT 15000;
SELECT @@SPID AS ReaderSession;
BEGIN TRY
    SELECT ID FROM dbo.SqlaNoLock184678 WITH (NOLOCK);
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
SET LOCK_TIMEOUT -1;

While B waits, run this in A:

-- Run in Session A while Session B is waiting.
SELECT request_session_id, request_mode, request_status
FROM sys.dm_tran_locks
WHERE resource_type = N'OBJECT'
  AND resource_database_id = DB_ID()
  AND resource_associated_entity_id = OBJECT_ID(N'dbo.SqlaNoLock184678');

For this table, expect A’s granted Sch-M and B’s waiting Sch-S. If B has already timed out, repeat only the reader test while A still holds the transaction. Viewing other sessions’ locks requires the relevant server monitoring permission. Release the transaction promptly, then remove the test table:

-- Session A: release the schema change; this also undoes TemporaryColumn.
ROLLBACK TRANSACTION;
-- Once Session B has finished, clean up only the experiment table.
DROP TABLE dbo.SqlaNoLock184678;

The native capture used a separate observer with the recorded object ID so its own metadata lookup would not block behind the schema change. Its isolated database name and session IDs belong to that run. The complete Session A observer above uses the table name instead. After the observation, the transaction was rolled back, the reader completed and the test table was removed.

NOLOCK can also read uncommitted values, miss rows or read rows more than once. It is not a reliable general cure for blocking. Investigate the blocking work and the required isolation semantics; row-versioning approaches can reduce read/write contention while avoiding dirty reads, but those require their own database and workload review.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.