Chris Harshman wrote:
Chris Harshman wrote:
NOLOCK probably does not do what you think it does. It still will take out locks for itself on the tables you are querying
I don't think so. Where did you get that from? Other than the locks absolutely necessary for schema/meta-data and other high-level considerations, NOLOCK does not take any row locks on the table being read.
Sorry for not being specific enough. But yes it does take out a "Sch-S" lock on the table. No it doesn't take out page or row level locks, I never said that it did.
Actually the full quote made it seem clear that you were talking about row/page locks.
It still will take out locks for itself on the tables you are querying, it just ignores locks that other sessions have on those tables. That's what allows it to see uncommitted data, duplicate data, bad data, etc.
It was a bad hack in the SQL Server 2000 days
Since a schema lock can't be ignored, only row/page locks can, I think there's a clear inference that you were referring to the latter type of locks, as uncommitted data, etc., also have nothing to do with schema-level locks. What am I missing?
The only thing that's really important is that people understand accurately what NOLOCK does save in terms of locking. It does prevent the normal row share locks that SQL otherwise would have to take. This does provide a performance benefit, although it can be a slight one; then again, it can also prevent some deadlock situations. NOLOCK is a legitimate option today, not just in SQL 2000. And there are very good reasons not to automatically put every db in snapshot mode.
Even Oracle has moved away from the days when they forced every schema and table to have the overhead of a snapshot-style approach (although they used different terms for it: their dbms effectively required every table to use a snapshot-type techinque, using the rollback segment (as they called it), which often caused failures because of long-running trans, etc.). Yes, they bragged that "readers didn't block writers" and vice versa, but you paid for that in much added I/O and other overhead.
SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.