What can happen as a result of using (nolock) on e

Question

I get that the (nolock) optimizer hint allows for "dirty reads", but under what very specific scenarios is this a bad idea? I've never seen such widespread use of (nolock) in an organization, and it makes me nervous. I'd like an explanation in terms of user stories. "Paul does A, Peter does B, X happens instead of Y".

Answer 1

Reposting this answer:

---

NOLOCK means placing no locks at all.

Your query may returns portions of data as of before UPDATE and portions as of after UPDATE in a single query.

Like, a debit without a credit and these kinds of stuff.

For instance, I just ran this query on a large table:

SELECT  SUM(LEN(name))
FROM    master WITH (NOLOCK)
OPTION (MAXDOP 1)

---
18874367

All name's have length of 1.

Then I reran it and in the middle of the query updated the table:

UPDATE  master
SET     name = 'tt'
WHERE   id <= 10000

SELECT  SUM(LEN(name))
FROM    master WITH (NOLOCK)
OPTION (MAXDOP 1)

---
18874944

As we can see, this query noticed 577 rows as updated (length 2), all other rows as not updated (length 1).

SELECT  SUM(LEN(name))
FROM    master WITH (NOLOCK)
OPTION (MAXDOP 1)

---
18884367

And this query, run right after the previous one finished, sees all updates.

Answer 2

See this link for examples.

http://blogs.msdn.com/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx

Answer 3

I recently spent a great deal of time figuring out some time and blocking issues for a data warehouse build process. As it turns out, due to the read-only nature of the data for loading the warehouse, I added nolock hints to the source data queries for the etl to reduce the requirement for lock escalation on the sql server and kept the etl load from failing. For this one I had very little control over the sql server and the application. Again, this was a targeted solution and I don't recommend widespread use of any query hints as a general rule. Like all performance testing and review, there are key areas to look at to determine where the problem lies and what might be the best way to attack it.