If you are in a team that is obsessed with NOLOCK hints to your SQL queries, because you think it is faster and an all weather reliable friend, this post is for you to help you understand why you should avoid it (almost always) for it is not your friend.
NOLOCK in SQL Server is often abused as if it is the magical way to speed up read queries. It is unfortunate that teams forget that "there is NO Free Lunch". Every action is trading off one thing for the other and so when you take an action, be aware of what you are trading to give to get something.
The Exceptional Circumstances You Can Use NOLOCK
- When you are querying live DB for some Reporting, where some bad data doesn't alter the overall aggregate numbers that you compute.
- When you are using WITH (NOLOCK) for SELECT query in reporting database where data are already written and committed.
- When you are querying a live production DB to take a peak into it and not use the result as a source of truth, knowing that it might have bad data in it.
Unravelling NOLOCK
- NOLOCK is a Table Hint in T-SQL, that tells the Query Optimizer (which can generally pick the best optimization method without any hints being given to it) on what is the better way to execute the said query, as its author is an expert DB specialist who is fully aware that the data could well be bad and that it is alright.
- NOLOCK is synonymous to READUNCOMMITTED isolation level. Reading uncommitted data implies that you are potentially reading data before it is being committed.
- In an OLTP database where there are a lot of transactions happening, the database is intelligent to consider INSERT, UPDATE and DELETE statements with higher priority over SELECT query. Write has higher preference over Read. Why bother changing this status quo of inherent intelligence?
- In a highly transactional table NOLOCK hint yields you faster READs having UNCOMMITTED data that could potentially turn out to be bad if it is uncommitted.
- When you are employing WITH(NOLOCK) hint, you are trading rare Dead-Lock occurrence possibility and with Bad Data.
Conclusion
Always use the NOLOCK hint with great caution and don't be fooled into believing that the results are absolutely true.