Thursday, July 5, 2012

What is NOLOCK Hint in SQL server ?

Using the NOLOCK query optimiser hint is generally considered good practice in order to improve concurrency on a busy system. When the NOLOCK hint is used in a SELECT statement, no locks are taken when data is read. The result is a Dirty Read, which means that another process could be updating the data at the exact time you are reading it. There are no guarantees that your query will retrieve the most recent data.  SELECT statements take Shared locks, means multiple SELECT statements are allowed simultaneous access, but other processes are blocked from modifying the data. The updates will queue until all the reads have completed, and reads requested after the update will wait for the updates to complete. The result to your system is delay(blocking).

The slight disadvantage is that one may not be sure that they are getting the data which is currently being updated in the Table ie Without lock protection, you cannot be guaranteed that the data isn’t changing during the time that the query is running.


How to use:
SELECT * FROM TableName (NOLOCK)