When to use WITH (NOLOCK)?

Question

Select * 
from MinhaTabela with (NOLOCK)

I know colleagues who always insert the clause With (NOLOCK) in their selects and I know others who preach that NOLOCK is bad practice and should never be used.

Some claim that with the NOLOCK we have more performance, others say they can generate errors legends "dirty records".

Extremists scare me. What is the point of balance? That is, when to use or not to use WITH (NOLOCK) in my darlings?

Answer 1

Your question is very interesting and is much deeper than my simple answer and depends on factors like: What is intended with the query, the DBMS in question and type of reading you intend.

Let’s define two concepts before:

Unconfirmed or dirty reading "Dirty read": read data from a table that has not been confirmed via commit. That is, data that is temporarily there and may disappear.

read confirmed "Committed read": read the data that has been confirmed and that will not disappear in the middle of a transaction at that instant, for example.

When we use NOLOCK and the DBMS allows this, dirty reading data mixed with confirmed data will be returned. Already, if you avoid the use, will be presented only the data that were confirmed with commit.

Therefore, a data that is not "committed" should be treated as a data that does not exist for certain situations. Are rare cases you need a read of "unconfirmed data", temporary in the table in question, because they could "disappear" given some situation and you get inconsistent results in return.

In short: If your DBMS allows it, use NOLOCK when you want to view unconfirmed data and not when you only want to work with data that is actually confirmed.

I hope I was didactic, explaining the difference. A hug

Answer 2

In short, if you choose to use WITH(NOLOCK) you will be performing a consistency-by-performance trade-off.