Should "NOLOCK" hint be used with select statements? We have a table in our application which has a data getting modified frequently by different threads. So is it required to have "NOLOCK" hint on the select queries executed on this table?
My answer would be, "It Depends but Usually no, No, NO, OH HELL NO!" ;-) for all the reasons that Rob mentioned. The reason why most people use it is to have queries run that aren't blocked by others and that's the real problem. Cruddy code causing blocking by others. The best thing to do would be to find the code that's doing the blocking and make it run faster, smarter, and with fewer resources. Yeah, yeah... I've heard all the excuses... "It would be too expensive"... "It would take too long"... "It would whatever".
And, no, it's not an enhancement to efficiency... it's a way to ignore horribly ineffecient code.
Just wait until some critical piece of data is either missing or doubled up that causes a contract to be lost, a critical report to a government agency being incorrect and the company gets fined for it, or someone gets fired because it looks like you actually have enough manpower on the manpower report. Now, THAT's going to be expensive. ;-)
It also saves on the costs of new servers which include a bunch of software licenses, cooling, maintenance, additional hard disk space, additional server room space (even if it's just a single blade), additional UPS/cabling, additional installation cost, additional etc....
Heh... stop putting bandaids on stab wounds... fix the code. ;-)
answered May 14 '10 at 10:05 PM
You should also have a look at switching to READ_COMMITTED_SNAPSHOT with row-level versioning, which you can read about here: http://msdn.microsoft.com/en-us/library/ms345124(SQL.90).aspx. It means you get a consistent view for the duration of your SELECT without being blocked by in-flight updates and without locking the rows you're reading. No dirty reads but you can end up reading a row that is being deleted by another transaction.
It does add some tempdb overhead but in my experience this is minimal. Your mileage may vary as they say, so you should test the effects on your system. It's quick and easy to switch on or off though, so if it causes problems you can quickly disable it again. And it ignores any NOLOCK hints so you don't have to change those queries to try it.
answered Mar 26 '10 at 04:18 AM
Feel free, just so long as you're fine about the idea of dirty reads.
For example - you want to count the rows in a table. The system uses the smallest index there is, but there are updates going on by your other threads. Using NOLOCK, you will read some rows twice, and some not at all... like trying to read through a phone book while someone's rearranging the names.
answered Mar 26 '10 at 03:59 AM