MS SQL Server allows using the TSQL hint ‘WITH (NOLOCK)’ to the joins of SELECT statements. At times the WITH (NOLOCK) hint can be justified and useful, but it comes with inherent dangers that are often ignored or not understood.
The WITH (NOLOCK) hint is often overused or is used when it should not be. In many cases adding the hint is done to circumvent problems associated with poor database design and/or poor query syntax.
The typical reason for using the WITH (NOLOCK) SQL hint is trying to avoid having a query hang if there are database deadlocks. In theory if a table is deadlocked, then a SELECT statement can hang indefinitely, which is certainly not desired behavior.
The MSDN documentation is quite clear on the downsides of using the WITH (NOLOCK) hint, the primary one of which is the introduction of seemingly random bugs. The WITH (NOLOCK) hint will pick up data from uncommitted transactions that could contain data that will be rolled back, or it could possibly pick up partial data from incomplete transactions.
If the WITH (NOLOCK) hint is being used to modify information in the database it could be saving bad data. This bad data is especially insidious since the origin will have been data from a transaction that was rolled back or was incomplete at the time, so tracking down the cause will be especially difficult.
Even worse, it can happen that fully committed rows can be missed, or rows can be counted twice. This is due to the underlying page split mechanism of SQL Server and how it deals with clustered or unclustered indexes. If commands causing page splits are taking place against specific tables and a query using the WITH (NOLOCK) hint is run against the affected tables, then rows can be double counted or dropped entirely.
Many programmers indiscriminately use the WITH (NOLOCK) hint regardless of the function they are writing, which can cause serious problems with the integrity of the data affected by their code.
However, the WITH (NOLOCK) hint can be useful as long as the programmer understands the dangers associated with its use. In general reports are an instance where the WITH (NOLOCK) hint makes sense.
If the data is not mission critical and no modifications are being made to the database based on the SELECT that uses WITH (NOLOCK), then using the hint allows the query to run without having to worry about table locks. This can definitely be a boon, and in some situations it does make sense to carefully weigh the pros and cons inherent with the WITH (NOLOCK) hint.
References:
Justin Cooney 