Nov 5, 2009

SQL Server NOLOCK: Should I use or should I not?

Well, the simple answer is NO!

What is NOLOCK?
NOLOCKS enables you to make a SELECT statement while avoiding current locks on the tables by other statements such as DELETE and UPDATE.

Why should you use NOLOCK?
Well, the answer is simple, you have locks in the database, users in your website receive exceptions and errors instead of answers, and your boss is getting nervous. The simple way is just place an extra WITH(NOLOCK) and things seems to be OK:

SELECT field_name FROM table_name WITH(NOLOCK)

Why should you avoid NOLOCK?
If your database suffers from locks, avoiding these performance issues now, will result in larger problems in the future. Your database is a key feature in your architecture, and your should take care of him and not avoid the problems.
Moreover, using NOLOCK does not gurrentee that your users will receive updated and current data which may sensitive when financial or sensitive data is getting into place.

Keep Performing,
Moshe Kaplan.


Ilya Chernyakov said...

I did not get it .
so when I DO need to use it ?

Moshe Kaplan said...

Well Ilya, it's a great question
Use NOLOCKS only, and only if your don't have time and you need to bring up your system up back to the air.
After that, do yourself a favor and take care of your database system...


Anonymous said...

well, how would you then tackle a very busy operational table with alot of updates on it which also requires many reports to run on that very same table?
if you dont do nolock your report query is killing the operational functionality...

Moshe Kaplan said...


Thanks for the great question, maybe it worth a post by itself. But to be short:
1. If it's an operational table, do not base on it MANY reports. Operational tables are not aimed to be you OLAP environment
2. If you are having a lot of UPDATEs, I recommend you to have a second look on your database design. You should avoid such a case by summarizing several UPDATEs into a single one using cache or by storing first INSERTS to temporal table, and then UPDATE at once.

However, it's based on a case by case scenario and you can feel free to drop your case so I'll be able to better analyze it.



Intense Debate Comments

Ratings and Recommendations