Say No-No to NOLOCK

Do you use the NOLOCK (or the equivalent READUNCOMMITTED) table hint in production code? Let me try to convince you to say No-No to NOLOCK.

When you use the NOLOCK table hint with a SELECT statement, your query does not apply Shared Locks, and does not respect Exclusive Locks. This means that if there is another transaction modifying data, your NOLOCK query can view that data before the transaction is committed or rolled back. Although that sounds like it’s good for performance, to quote Microsoft, “This may generate errors for your transaction, present users with data that was never committed, or cause users to see records twice (or not at all).” Bad data fast is usually not a good thing.

Can you get better performance by using NOLOCK? Yes, it can increase concurrency. If there are no other transactions, then your NOLOCK query isn’t any faster (I doubt you could ever measure the speed improvement due to not applying the Shared Locks). If there are other transactions modifying data, they don’t have to wait for your NOLOCK query to complete, and your NOLOCK query doesn’t have to wait for transactions that are modifying data to complete. But when is the likelihood of your NOLOCK query returning data that was never committed, or duplicate records, or missing records? When there are other transactions that are modifying data.

The situation that can give you better performance is the same situation with increased risk of corrupt results.

Say no-no to NOLOCK.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.