13.07.2016

How does the table query hint “with (nolock)” used on a view have an effect on the underlying tables in SQL Server?

Technical Value

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:

  1. create table TableA (
  2. AId int not null identity(1,1),
  3. TextA nvarchar(10) )
  4. create table TableB (
  5. BId int not null identity(1,1),
  6. TextB nvarchar(10))

And we need some example data:

  1. INSERT INTO [dbo].[TableA]
  2. ([TextA])
  3. VALUES
  4. (N'TextA1'),(N'TextA2')
  5. GO
  6. INSERT INTO [dbo].[TableB]
  7. ([TextB])
  8. VALUES
  9. (N'TextB1'),(N'TextB2')
  10. GO

Now we create our view:

  1. creat View MyView as
  2. select AId, TextA, BId, TextB
  3. from dbo.TableA
  4. join dbo.TableB
  5. 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

  1. TableA

and for keep the lock for two minutes to have a chance to test:

  1. begin tran UpdateTableA
  2. print 'Update started: ' + CONVERT(char(10),GetDate(),108)
  3. UPDATE [dbo].[TableA]
  4. SET [TextA] = N'TextNew'
  5.  
  6. waitfor delay '00:02';
  7. print 'Update finished: ' + CONVERT(char(10),GetDate(),108)
  8. rollback tran UpdateTableA

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

  1. -- Start Query in separate Sessions:
  2. print 'Query with no Lock hint started: ' + CONVERT(char(10),GetDate(),108)
  3. SELECT [AId]
  4. ,[TextA]
  5. ,[BId]
  6. ,[TextB]
  7. FROM [dbo].[MyView]
  8. 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:

  1. -- Start Query in separate Sessions:
  2. print 'Query with no Lock hint started: ' + CONVERT(char(10),GetDate(),108)
  3. SELECT [AId]
  4. ,[TextA]
  5. ,[BId]
  6. ,[TextB]
  7. FROM [dbo].[MyView] with (nolock)
  8. print 'Query with no Lock hint finished: ' + CONVERT(char(10),GetDate(),108)

Now let's have a look at the output:

  1. Update started: 11:46:03
  2.  (2 row(s) affected)
  3.  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:

  1.  Query with no Lock hint started: 11:46:05
  2.  
  3.  (2 row(s) affected)
  4.  
  5.  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:

  1.  Query with Lock hint started: 11:46:14
  2.  
  3.  (2 row(s) affected)
  4.  
  5.  Query with Lock hint finished: 11:46:14

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

  1. 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.

Teilen auf

Newsletter Anmeldung

Abonnieren Sie unseren Newsletter!
Lassen Sie sich regelmäßig über alle Neuigkeiten rundum ORAYLIS und die BI- & Big-Data-Branche informieren.

Jetzt anmelden