I have a question about the use of NOLOCK.
I understand that NOLOCK hint is not always the best approach, but in some case it is very helpful. I am not trying to build a bad habit of using it all the time
I just want to understand the exact behavior of it. Have this unrealistic assumtion that a process that is updating record where id = 10 UPDATE table1 SET status = 2 WHERE id = 10 which is taking 30 seconds to update. And at the same time I execute SELECT * FROM table1 WITH NOLOCK where id = 10
Will my select statement read the row even though my first query had an exclusive lock on the record or will my select query wait until no locks what so ever on the record before it allows the read?
I want to know if the use of NOLOCK could be causing delays or not.
The short answer to the question as stated is: "No."
In most cases the NOLOCK hint will speed up the query in question, as well as, any other queries operating against the specified table at the same time. The reason is that no locks are checked or obtained. You've listed the possible side effects in your question so I won't cover those here.
At the end of the day the query will be faster, but the results will be suspect.