Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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

like image 524
Chris McCall Avatar asked Nov 05 '09 17:11

Chris McCall


People also ask

What does with Nolock do in SQL Server?

The NOLOCK hint allows SQL to read data from tables by ignoring any locks and therefore not get blocked by other processes. This can improve query performance by removing the blocks, but introduces the possibility of dirty reads.

Can Nolock cause deadlock?

Please note that in some cases Optimizer might think to behave differently even when query hints are used. You cannot always say optimizer would only make decision based on Query hint. Although its highly unlikely for NOLOCK to cause deadlock but it can.

Should you use with Nolock?

The WITH (NOLOCK) table hint is a good idea when the system uses explicit transactions heavily, which blocks the data reading very frequently. The WITH (NOLOCK) table hint is used when working with systems that accept out of sync data, such as the reporting systems.

Can we use Nolock for delete statement?

A delete statement acquires an exclusive intent lock on the reference table; therefore, during that time, no other transactions can modify the data. You can use NOLOCK hint to read the data.


1 Answers

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.

like image 122
Quassnoi Avatar answered Sep 23 '22 03:09

Quassnoi