I'm in the business of making website and applications that are not mission critical -> eg. banking software, space flight, intensive care monitoring application, etc. You get the idea.
So, with that massive disclaimer, is it bad using the NOLOCK hint in some Sql statement? A number of years ago, it was suggested by a fellow Sql Administrator that I should use NOLOCK if I'm happy with a "dirty read" which will give me a bit more performance out of my system because each read doesn't lock the table/row/whatever.
I was also told that it's a great solution if I'm experiencing dead-locks. So, I started following that thought for a few years until a Sql guru was helping me with some random code and noticed all the NOLOCKS in my sql code. I was politely scolded and he tried to explain it to me (why it's not a good thing) and I sorta got lost. I felt that the essence of his explanation was 'it's a band-aid solution to a more serious problem .. especially if you're experiencing deadlocking. As such, fix the root of the problem'.
I did some googling recently about it and came across this post.
So, can some sql db guru sensei's please enlighten me?
Prior to working on Stack Overflow, I was against NOLOCK
on the principal that you could potentially perform a SELECT
with NOLOCK
and get back results with data that may be out of date or inconsistent. A factor to think about is how many records may be inserted/updated at the same time another process may be selecting data from the same table. If this happens a lot then there's a high probability of deadlocks unless you use a database mode such as READ COMMITED SNAPSHOT
.
I have since changed my perspective on the use of NOLOCK
after witnessing how it can improve SELECT
performance as well as eliminate deadlocks on a massively loaded SQL Server. There are times that you may not care that your data isn't exactly 100% committed and you need results back quickly even though they may be out of date.
Ask yourself a question when thinking of using NOLOCK
:
Does my query include a table that has a high number of
INSERT
/UPDATE
commands and do I care if the data returned from a query may be missing these changes at a given moment?
If the answer is no, then use NOLOCK
to improve performance.
NOLOCK
keyword within the code base for Stack Overflow and found 138 instances, so we use it in quite a few places.