Blog Post

Using WITH NOLOCK

,

A colleague of mine and I were recently talking about some scripts that they had seen containing the WITH NOLOCK hint. They asked me what it was and my basic answer is this…Using WITH NOLOCK will return records as they currently are stored regardless of if someone is updating them at that point in time. Updating tables causes a lock to be held. If you don’t use WITH NOLOCK (the WITH is optional by the way), when someone is updating the table, you will not get results until the update has completed. If you do use the WITH NOLOCK, then you’ll get what is called a “dirty read”. Often times this is fine, but you’ll need to look at your application and the objective of the query to begin with in order to make that call. At any rate, I’ve created the following script that can be used to see exactly what I mean.

 

Script 1: Script to Lock the Table

USE AdventureWorksDW2008

GO

 

BEGIN TRANSACTION

      UPDATE FactInternetSales

      SET SalesOrderNumber = LOWER(SalesOrderNumber)

      WHERE RIGHT(SalesOrderNumber,5) < '44000'

 

--Just wait for a bit please! 🙂

WAITFOR DELAY '00:00:30'

 

--I really didn't want to commit this 🙂

ROLLBACK TRANSACTION

 

Script 2: Try to Get Results

--Script 2: Try to get results while the update is running

SELECT TOP 100 * FROM FactInternetSales

WHERE RIGHT(SalesOrderNumber,5) < '44000'

 

Script 3: Get Results using NOLOCK

--Script 3: Get results using the (NOLOCK) while the update is running

SELECT TOP 100 * FROM FactInternetSales (NOLOCK)

WHERE RIGHT(SalesOrderNumber,5) < '44000'

 

As you’ll see, script 2 will not run while script 1 is still executing. However, if you run script 3 while executing script 1, results will be returned.

 

Until next time you can follow me on Twitter using @briankmcdonald, “keep your ear to the grindstone” – Good Will Hunting

 

 

 

Brian K. McDonald, MCDBA, MCSD
Business Intelligence Consultant – Pragmatic Works Consulting

Email: bmcdonald@pragmaticworks.com | Blog: BI Developer Network

Convert with DTS xChange  | Develop with BI xPress  | Process with TaskFactory | Document with BI Documenter

 

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating