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
