Select Query with Nolock Guideline

In my work, I noticed quite a few select queries with NOLOCK option specified. I am not a DBA so I did some research and consulted with my colleague about when to use or not to use the NOLOCK option. NOLOCK according to books online:

Do not issue shared locks and do not honor exclusive locks. When this option is in effect, it is possible to read an uncommitted transaction or a set of pages that are rolled back in the middle of a read. Dirty reads are possible. Only applies to the SELECT statement.

Michael Balloni's article wisely suggested that a NOLOCK hint can significantly improve performance when used correctly. Nevertheless, a lot of "performance freaks" would jump in and start using NOLOCKs everywhere.

When NOT to use NOLOCK:

1. The data you are retrieving will be re-written to the database. If you were reading dirty data into a form that would later persist the changes to the database you would run into a data concurrency issue.


2. The accuracy of the data you are retrieving is crucial. If you were retrieving a list of financial transaction history, that would be considered crucial. Use your judgment when determining whether accuracy is crucial.


3. One of the columns you are retrieving is part of a clustered index and can be modified. Using NOLOCK in the middle of a page split due to modification to a column in a clustered index will cause a "601 Could not continue scan with NOLOCK due to data movement" error.
   

When to use NOLOCK:

1. The data you are retrieving is read-only or rarely changes. If you were retrieving a list of statuses to populate a dropdown, use NOLOCK and cache the list.


2. The table you were trying to access is constantly being updated. If the table you were trying to retrieve records from has a table lock, use NOLOCK to bypass the lock.