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