Stefan Grigat

In this blog I will investigate the effect of the table hint „with (nolock)“, when a view is queried, on access and locking on the underlying tables. Is it necessary to place this hint directly on (every) table used in the view or is maybe the hint on the query of the view enough?

Given the situation of a heavily used OLTP-System where we want to query a view which uses an often updated table.
More Details about locking can be found here: https://technet.microsoft.com/en-US/library/ms175519.aspx.
You should also read this article carefully to get to know the risks of using the table hint „with (nolock)“: https://msdn.microsoft.com/en-Us/library/ms187373.aspx

Let’s start the investigation.
First we need two tables in the SQL Server to use in a view:

create table TableA (
AId int not null identity(1,1),
TextA nvarchar(10) )

create table TableB (
BId int not null identity(1,1),
TextB nvarchar(10))

And we need some example data:

INSERT INTO [dbo].[TableA]
([TextA])

VALUES

(N’TextA1′),(N’TextA2′)
GO

INSERT INTO [dbo].[TableB]
([TextB])

VALUES

(N’TextB1′),(N’TextB2′)
GO

Now we create our view:

creat View MyView as
select AId, TextA, BId, TextB
from dbo.TableA
join dbo.TableB
on TableA.AId = TableB.BId

It is very simple but will do the trick for today.
To test the nolock effect we will produce some locking on TableA and for keep the lock for two minutes to have a chance to test:

begin tran UpdateTableA

print ‚Update started: ‚ + CONVERT(char(10),GetDate(),108)
UPDATE [dbo].[TableA]
SET [TextA] = N’TextNew‘

waitfor delay ’00:02′;

print ‚Update finished: ‚ + CONVERT(char(10),GetDate(),108)
rollback
tran UpdateTableA

In another connection we will start a normal select against our view which uses the update-locked-table

— Start Query in separate Sessions:

print ‚Query with no Lock hint started: ‚ + CONVERT(char(10),GetDate(),108)

SELECT [AId]
,[TextA]
,[BId]
,[TextB]
FROM [dbo].[MyView]

print ‚Query with no Lock hint finished: ‚ + CONVERT(char(10),GetDate(),108)

And the same time we will start the query with the table hint in a third SQL Server connection:

— Start Query in separate Sessions:

print ‚Query with no Lock hint started: ‚ + CONVERT(char(10),GetDate(),108)

SELECT [AId]
,[TextA]
,[BId]
,[TextB]
FROM [dbo].[MyView] with (nolock)

print ‚Query with no Lock hint finished: ‚ + CONVERT(char(10),GetDate(),108)

Now let’s have a look at the output:

Update started: 11:46:03

(2 row(s) affected)

Update finished: 11:48:03

The Transaction with the update took exactly the 2 Minutes. But how long did the queries take from start to finish:

Query with no Lock hint started: 11:46:05

(2 row(s) affected)

Query with no Lock hint finished: 11:48:03

The query without the table hint was running until the time the update finished. So it had to wait to get the lock.
But what about the query with the nolock hint:

Query with Lock hint started: 11:46:14

(2 row(s) affected)

Query with Lock hint finished: 11:46:14

It finished immediately. So it did not have to wait to get the lock on TableA.

Conclusion:

If you need to query data without shared read locks you can use the table hint with (nolock). Even in views it effects all underlying tables and you don’t have to re-write your view.
So users who want to get reliable data can use the same view as users who need the data fast, with no locks and the risks related to this.