Sent: Friday, January 20, 2012 10:30 AM
To: William Assaf
Subject: dba has a question
To: William Assaf
Subject: dba has a question
William,
Can you send me references to support my contention that NOLOCK is the epitome of evil?
That is, that it can return invalid and/or unrelated data.
He is still suggesting that folks use NOLOCK (rather than READPAST).
We have to work around other poorly written queries, so some of these are inevitable.
Thanks,
[my colleague's name redacted to protect the innocent]
--------------------------------------------------------------------------------------------
The basic danger is that with NOLOCK, the data can change out from underneath the query while it is in progress. This doesn't require the query to run for a long time, it could happen even on simple queries.
He should know that SQL doesn't read data out of a database like an excel spreadsheet, it reads a series of addresses all across a hard disk. Without even a shared read lock, the data in those addresses can change out from underneath a query using NOLOCK or READ UNCOMMITTED (same thing, different keywords.)
The more appropriate and sometimes-used term for this isolation level is CHAOS. You could (and there are labs out there to prove this) end up with blank data, malformed bytes, or worst - wrong data. The likelyhood is low admittedly, but the potential for data corruption is misreporting is not something that should be invited.
I would be strongly against using this isolation level on any query that could be attached or provide source data from an update/insert/delete statement, which would commit the bad data. Most select statements are the basis for some update, unfortunately.
Perhaps on a query that is used frequently on a constantly-refreshing dashboard would be a situation in which NOLOCK would be appropriate. But then, there are better, safer alternatives like SNAPSHOT isolation mode, or better architectural decisions to make data more available for read-heavy queries.
Good examples and info here:
-William
