Question:
Where I work in stored procedures in sql, the WITH(NOLOCK) construction is quite actively used. It is used for data that can be requested very often and I was told that its use is justified for performance reasons. As far as I know WITH(NOLOCK) is analogous to the READ UNCOMMITTED isolation level and means no table locks when executing queries.
However, on Habré, in the article " 7 things that a developer should know about SQL Server ", they write that, firstly, WITH(NOLOCK) does not guarantee the absence of locks, and secondly, I quote:
about WITH NOLOCK. What amazed me was not that there would be locks, but that someone not only seriously uses this anathema, but also expects that there will be no locks.
Question: is it evil to use WITH(NOLOCK) ? Is it absolute evil or not? When can its use be justified?
Answer:
WITH (NOLOCK) not absolute evil. It's just a hint. Evil is its misuse. You just need to understand exactly all the consequences of its use – reads of lines that will never become strings, phantom reads, possible duplicates of the same value during scans – in order to understand that it is not worth using it in a live application.
NOLOCK / READ UNCOMMITTED has one explicit purpose – to read uncommitted data. For example, I have to use it regularly when debugging (in the form of SET TRANSACTION ISOLATION LEVEL ), when the code is stopped in the middle of a transaction, and I need to find out what is happening in the database from the point of view of the code.
For all other cases in which previously used NOLOCK / READ UNCOMMITTED , it is now much better suited READ_COMMITTED_SNAPSHOT .
PS But SNAPSHOT is really evil!