I'm using NOLOCK; is that bad?
Yes. Sure, there are edge cases where accuracy is not a priority, but they aren't that common. When you're looking for ballpark estimates or grabbing a random row, go nuts. But please don't defend, propose, or implement a policy like thou shalt use NOLOCK on every table reference ever. If you don't think those policies exist, take a poll; good luck falling asleep now.
Anyway, here are some resources about NOLOCK, with a lot more detail than "thar be dragons"…
- Bad habits : Putting NOLOCK everywhere
I talk about why it's such a bad idea to have the "NOLOCK on every table reference" policy.
- NOLOCK Anomalies, Issues and Inconsistencies
I demonstrate some of the issues with NOLOCK with real examples.
- The Read Uncommitted Isolation Level
Paul White talks about real issues here, too, in a longer series on isolation levels.
- Transaction Phenomena – Part 1: The Dirty Read
Michael J Swart talks about dirty reads here (and read on in the series for other symptoms).
- Avoid using NOLOCK on UPDATE and DELETE statements
In this tip, I talk about why you should definitely avoid NOLOCK on the target of an update or delete statement, which led to…
- Finding Problematic NOLOCK Patterns – Part 1, Part 2, Part 3, Part 4
…this series on using TSqlFragmentVisitor to reliably find the pattern
UPDATE t ... FROM t WITH (NOLOCK).
So what do I do instead?
Typically, the solution to the "readers blocking everyone" problem is to use read committed snapshot isolation (RCSI). This option isn't free, but if you are querying an Availability Group secondary replica, it's the behavior you're getting anyway. Kendra Little, Erik Darling, and Paul White discuss some of the ramifications in the following posts:
- Implementing Snapshot or Read Committed Snapshot Isolation in SQL Server: A Guide (Kendra)
- How to Choose Between RCSI and Snapshot Isolation Levels (Kendra)
- Steps For Getting Rid Of NOLOCK Hints (Erik)
- The SNAPSHOT Isolation Level (Paul)
- Read Committed Snapshot Isolation (Paul)
- Data Modifications under Read Committed Snapshot Isolation (Paul)