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?
With NOLOCK hint, the transaction isolation level for the SELECT statement is READ UNCOMMITTED. This means that the query may see dirty and inconsistent data.
This is not a good idea to apply as a rule. Even if this dirty read behavior is OK for your mission critical web based application, a NOLOCK scan can cause 601 error which will terminate the query due to data movement as a result of lack of locking protection.
I suggest reading When Snapshot Isolation Helps and When It Hurts - the MSDN recommends using READ COMMITTED SNAPSHOT rather than SNAPSHOT under most circumstances.