NOLOCK is not bad

All over the internet when someone asks about using NOLOCK they are flooded with responses that say not to use it, never to use it, etc… But NOLOCK is not as bad as it is made to be. It is infact really good when used under right circumstances.

NOLOCK can be highly useful when you are reading old data from a frequently used table. Consider the following example,

You have a stored procedure to access data of inactive projects. You don’t want this stored procedure to lock the frequently used Projects table while reading old data. Using NOLOCK in this case will reduce the chances of a deadlock.

NOLOCK is also useful when dirty reads are not a problem and data is not frequently modified such as in the following cases, Reading list of countries, currencies, etc… from a database to show in the form. Here the data remains unchanged and a dirty read will not cause a big problem as it will occur very rarely.

However starting with SQL server 2005 the benefits of NOLOCK is very little due to the introduction of row versioning.