Home sql WITH NOLOCK Evil?





Where I work in stored procedures in SQL, the WITH (NOLOCK) structure is quite active. It is used for data that can be very often requested and how I was told to use it justified due to performance considerations. As far as I know WITH (NOLOCK) is an analogue of the READ uncommitted insulation level and means the lack of table locks when executing queries.

However, in the article in the article “7 things that the developer should know about SQL Server ” write, That first With (NOLOCK) does not guarantee the lack of locks, and secondly quote:

About with NOLOCK. It struck not the fact that there will be blocking, but the fact that someone this anathema not only uses seriously, but also expects that there will be no locks

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

Answer 1, Authority 100%

With (NOLOCK) is not an absolute evil. It’s just a hint. Evil is the use of it is not intended. It is necessary to just make it necessary to imagine all the consequences of its application – reading lines that will never be rows, phantom readings, possible duplicates of the same value when scans – to understand that it is not worth using it in a live application.

NOLOCK / Read Uncommitted There is one obvious purpose – reading non-verbated data. For example, I have to use it regularly with a debug (in the form of Set Transaction Isolation LEVEL ) when the code is stopped in the middle of the transaction, and it is necessary to find out that from the point of view of the code now occurs in the database.

For all other cases in which they used to use NOLOCK / READ UNCOMMITED , now it’s much better than read_committed_snapshot .

p.s. But Snapshot is really evil!

Programmers, Start Your Engines!

Why spend time searching for the correct question and then entering your answer when you can find it in a second? That's what CompuTicket is all about! Here you'll find thousands of questions and answers from hundreds of computer languages.

Recent questions