Why use NOLOCK and NOWAIT together?

Question

A colleague wrote a query which uses the hints "with (NOLOCK,NOWAIT)".

e.g.

select first_name, last_name, age
from people with (nolock,nowait)

Assumptions:

NOLOCK says "don't worry about any locks at any level, just read the data now"

NOWAIT says "don't wait, just error if the table is locked"

Question:
Why use both at the same time? Surely NOWAIT will never be realised, as NOLOCK means it wouldn't wait for locks anyway ... ?

Solution 1

It's redundant (or at least, ineffective). In one query window, execute:

create table T (ID int not null)
begin transaction
alter table T add ID2 int not null

leave this window open, open another query window and execute:

select * from T WITH (NOLOCK,NOWAIT)

Despite the NOWAIT hint, and despite it being documented as returning a message as soon as any lock is encountered, this second query will hang, waiting for the Schema lock.

Read the documentation on Table Hints:

NOWAIT:

Instructs the Database Engine to return a message as soon as a lock is encountered on the table

Note that this is talking about a lock, any lock.

NOLOCK (well, actually READUNCOMMITTED):

READUNCOMMITTED and NOLOCK hints apply only to data locks. All queries, including those with READUNCOMMITTED and NOLOCK hints, acquire Sch-S (schema stability) locks during compilation and execution. Because of this, queries are blocked when a concurrent transaction holds a Sch-M (schema modification) lock on the table.

So, NOLOCK does need to wait for some locks.

Solution 2

NOLOCK is the same as READUNCOMMITTED, for which MSDN states:

... exclusive locks set by other transactions do not block the current transaction from reading the locked data.

Based on that sentence, I would say you are correct and that issuing NOLOCK effectively means any data locks are irrelevant, so NOWAIT is redundant as the query can't be blocked.

However, the article goes on to say:

READUNCOMMITTED and NOLOCK hints apply only to data locks

You can also get schema modification locks, and NOLOCK cannot ignore these. If you issued a query with NOLOCK whilst a schema object was being updated, it is possible your query would be blocked by a lock of type Sch-M.

It would be interesting to see if in that unlikely case the NOWAIT is actually respected. However for your purposes, I would guess it's probably redundant.

Solution 3

It does not make any sense to use them together. NOLOCK overrides the behavior of NOWAIT. Here's a demonstration of the NOWAIT Functionality. Comment in the NOLOCK and watch the records return despite the Exclusive Lock.

Create the table. Execute the 1st SSMS window without commiting the transaction. Execute the second window get an error because of no wait. Comment out the first query and execute the second query with the NOLOCK and NOWAIT. Get results. Rollback your transaction when you are done.

DDL

USE [tempbackup]
GO

/****** Object:  Table [TEST_TABLE]    Script Date: 02/19/2014 09:14:00 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [TEST_TABLE](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](50) NULL,
 CONSTRAINT [PK_TEST_TABLE] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

INSERT INTO tempbackup.dbo.TEST_TABLE(Name) VALUES ('MATT')

GO

SSMS WINDOW 1

BEGIN TRANSACTION

UPDATE tempbackup.dbo.TEST_TABLE WITH(XLOCK) SET Name = 'RICHARD' WHERE ID = 1

--ROLLBACK TRANSACTION

SSMS WINDOW 2

SELECT * FROM tempbackup.dbo.TEST_TABLE WITH(NOWAIT)
--SELECT * FROM tempbackup.dbo.TEST_TABLE WITH(NOLOCK,NOWAIT)

Comments

[Damien_The_Unbeliever]

Just done the test. NOWAIT still blocks waiting for the schema lock, so it does seem to be redundant after all.

[Martin Smith]

@Damien_The_Unbeliever - It won't even be able to compile the query and begin executing so the hint never really enters into it. SET LOCK_TIMEOUT 0;GO select * from T WITH (NOLOCK) would be the only way to do this AFAIK.