Recently, I was asked to review,
already written stored procedures for optimization purpose. During this review
process I have found that a group of developers is regularly committing a big
mistake. This group of developers believes that table hint NOLOCK is used to
execute queries quickly, as this hint will avoid placing any lock on target
table records and it can you used in any query. Even they have applied this NOLOCK
in DML statements.
WRONG
First thing, NOLOCK hint means,
it will not take care of any lock (instead of placing lock). It will return
data, that could be dirty (NOT YET COMMITTEED by other transactions). We can
use this table hint to get results quickly when we are dead sure that dirty
data is TOTALLY bearable.
In DELETE/UPDATE queries it
should be totally avoided as it can produce junk results. Let’s prove.
In following example, we need to
correct discount column of SalesOrderDetail, but according to discount provided
in lookup table of SpecialOffer. Before we execute our update statement (Statement
#2 in Transaction# 2), someone has accidently changed SpecialOffer, but
good thing is that, he has not committed these changes yet. But as we have
placed NOLOCK hint in our Statement #2 in Transaction# 2, it will change
data according to dirty data, though, later on transaction#1 is
rolledback.