What can happen as a result of using (nolock) on every SELECT in SQL Server?

optimizer-hints, sql, sql-server

Solution

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.

Problem

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".

Original source