NOLOCK Without WITH: Table Hint or Table Alias?

NOLOCK without WITH, and without parentheses, is not a table hint. SQL Server reads the word as a table alias and takes normal locks. No error appears, so the mistake can sit in code for years.

Three Ways to Write It

SQL Server accepts three spellings. Only two of them are hints. The table shows how SQL Server reads each one, and what the lock test below measured.

The first line is the trap. It looks like a hint. It runs without error, and it returns the right rows. It doesn’t do what the author wanted.

Prove It With a Lock Test

The demo creates a database named NolockAliasDemo with a small table. The first query uses NOLOCK as a name, to show that SQL Server accepts it as one.

IF DB_ID(N'NolockAliasDemo') IS NULL CREATE DATABASE NolockAliasDemo;
GO
USE NolockAliasDemo;
GO
DROP TABLE IF EXISTS dbo.Employees;
CREATE TABLE dbo.Employees (EmployeeID int IDENTITY(1,1) PRIMARY KEY, EmployeeName nvarchar(40) NOT NULL);
INSERT INTO dbo.Employees (EmployeeName) VALUES (N'Maya'), (N'Noah'), (N'Priya'), (N'Sam'), (N'Ravi');
GO
SELECT NOLOCK.EmployeeName FROM dbo.Employees NOLOCK WHERE NOLOCK.EmployeeID = 2;

The query qualified a column with NOLOCK. That works only because NOLOCK is the alias of the table. A real hint can’t be used that way.

Next, look at the locks. Under the default isolation level, shared locks vanish once a row is read. The test therefore uses REPEATABLE READ. That level keeps shared locks until the transaction ends, so they show up. The query reads the table with the bare word and then lists the locks that this session holds.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION;
SELECT COUNT(*) AS RowsRead FROM dbo.Employees NOLOCK;
SELECT resource_type AS ResourceType, request_mode AS LockMode, COUNT(*) AS Locks
FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID AND resource_database_id = DB_ID() AND resource_type IN (N'KEY', N'PAGE', N'RID', N'OBJECT')
GROUP BY resource_type, request_mode
ORDER BY resource_type;
ROLLBACK TRANSACTION;

Five shared key locks, one for each row. The query ignored the hint it appeared to carry. Now run the same test with the real hint, in the same window.

BEGIN TRANSACTION;
SELECT COUNT(*) AS RowsRead FROM dbo.Employees WITH (NOLOCK);
SELECT resource_type AS ResourceType, request_mode AS LockMode, COUNT(*) AS Locks
FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID AND resource_database_id = DB_ID() AND resource_type IN (N'KEY', N'PAGE', N'RID', N'OBJECT')
GROUP BY resource_type, request_mode
ORDER BY resource_type;
ROLLBACK TRANSACTION;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

The lock list comes back empty. The second query read the same five rows and held nothing. The form (NOLOCK) without WITH is a hint too, but it’s deprecated. The same test shows it.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION;
SELECT COUNT(*) AS RowsRead FROM dbo.Employees (NOLOCK);
SELECT COUNT(*) AS KeyLocks FROM sys.dm_tran_locks WHERE request_session_id = @@SPID AND resource_database_id = DB_ID() AND resource_type = N'KEY';
ROLLBACK TRANSACTION;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

The Compatibility Level Is Not the Cause

Older advice blamed compatibility level 80, the level of SQL Server 2000. Current versions no longer accept it. A database can sit at levels 100 to 170, and anything lower fails.

ALTER DATABASE NolockAliasDemo SET COMPATIBILITY_LEVEL = 80;

Msg 15048, Level 16, State 1, Line 1
Valid values of the database compatibility level are 100, 110, 120, 130, 140, 150, 160 or 170.

Lowering the level doesn’t change the reading either. At level 100, the oldest level that remains, the bare word still behaves as an alias.

ALTER DATABASE NolockAliasDemo SET COMPATIBILITY_LEVEL = 100;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION;
SELECT COUNT(*) AS RowsRead FROM dbo.Employees NOLOCK;
SELECT COUNT(*) AS KeyLocks FROM sys.dm_tran_locks WHERE request_session_id = @@SPID AND resource_database_id = DB_ID() AND resource_type = N'KEY';
ROLLBACK TRANSACTION;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

The five key locks are back. The cause is the syntax, and the fix is the syntax.

Find the Bad Syntax in Your Code

SQL Server counts every use of the deprecated form (NOLOCK) in a performance counter. The counter shows whether any code on the server still uses that form, not where.

SELECT instance_name, cntr_value FROM sys.dm_os_performance_counters WHERE object_name LIKE N'%Deprecated Features%' AND instance_name = N'Table hint without WITH';

The value is a running total since the last restart, so it differs on every server. To find the place, search the code. SQL Server keeps the text of every procedure, view and function in sys.sql_modules. One search finds the modules that mention NOLOCK but never write it in parentheses. The demo creates two procedures, one of each kind.

CREATE OR ALTER PROCEDURE dbo.ListEmployeesBare AS SELECT EmployeeName FROM dbo.Employees NOLOCK;
GO
CREATE OR ALTER PROCEDURE dbo.ListEmployeesHint AS SELECT EmployeeName FROM dbo.Employees WITH (NOLOCK);
GO
SELECT OBJECT_SCHEMA_NAME(m.object_id) AS SchemaName, OBJECT_NAME(m.object_id) AS ObjectName
FROM sys.sql_modules AS m
WHERE m.definition LIKE N'%NOLOCK%' AND m.definition NOT LIKE N'%(NOLOCK)%';

A module that uses both spellings passes this filter, so read the longer list by hand. The search also hits comments that mention NOLOCK, and it misses ( NOLOCK ) written with spaces. The check also can’t see queries that live in application code. Search the source files for the same pattern.

Spot the Symptom

NOLOCK without WITH shows up in production as blocking. A reader that waits for a writer shows the wait type LCK_M_S, which means a wait for a shared lock. A true NOLOCK reader never waits for that lock. If a query you believe reads dirty data still shows LCK_M_S waits, check how its hint is written.

Deadlocks are the other symptom. A reader that holds shared locks can deadlock with a writer, and the deadlock graph lists the shared locks. Nothing in the error points at the syntax, which is why the pattern survives for years.

Is NOLOCK Itself the Problem?

You could argue that the bare form is harmless, because it only adds locking that you’d have without any hint. That’s true when you never wanted dirty reads. If you did want them, you didn’t get them. Readers still wait for writers, and the waits can turn into blocking or deadlocks.

I avoid NOLOCK in new code. A hint that reads uncommitted rows can return rows twice, skip rows or show data that never commits. For reader and writer blocking, I prefer the READ_COMMITTED_SNAPSHOT database option. Readers then use row versions and skip shared locks.

What to Remember

NOLOCK without WITH is an alias. Write WITH (NOLOCK) if you need the hint, never the bare word. Search your modules for the pattern, and fix each hit on purpose. A changed compatibility level won’t help.

A fix changes behavior. The query starts reading uncommitted rows, and its results can change. Decide for each hit whether dirty reads are acceptable. If they aren’t, remove the bare word and leave the query with normal locking. Remove the demo database when you finish.

USE master;
GO
ALTER DATABASE NolockAliasDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE NolockAliasDemo;

A table hint is not a word that looks like one, it is a clause SQL Server reads.

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