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 as I was told its use is justified due to performance considerations. As far as I know, WITH(NOLOCK) is an analogue of 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. It was struck not only by blocking, but by the fact that someone is not only seriously using this anathema, but also hopes that there will be no blocking.

Question: Is using WITH(NOLOCK) evil? Is this evil absolute or not? When can its use be justified?

    1 answer 1

    WITH (NOLOCK) not an absolute evil. This is just a hint. Evil is the misuse of it. You just need to accurately imagine all the consequences of its use - reading lines that never become lines, phantom reads, possible duplicates of the same value with scans - to understand that you should not use it in a live application.

    NOLOCK / READ UNCOMMITTED has one explicit purpose - reading uncommented data. I, for example, have to use it regularly during 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 NOLOCK / READ UNCOMMITTED previously used, READ UNCOMMITTED is now much better suited.

    PS But SNAPSHOT is really evil!