Dirty Reads, Permanent Damage

This is not another "NOLOCK can show you dirty data" rant. There are plenty of posts about that, usually explaining how the hint is a misnomer because it doesn't really mean "no" locks, how dirty or phantom data can sneak into a report and render it inaccurate, and why RCSI is often a much better solution to reader/writer conflicts.

No, I've largely given up trying to swim against the current and fight draconian policies that have taught everyone, far and wide, the commandment:

This kind of blanket policy is gross, of course. But I'm much more concerned about a pattern I've seen a lot lately: using a read taken under NOLOCK to decide what to update, insert, delete, or permanently skip.

I think the inertia of blindly adding NOLOCK to every table reference in the entire codebase has inevitably spilled over into places where you certainly don't want that hint! Do you want a row to change based on something you observed under read uncommitted? (Which literally means you might commit a change based on reading a prior change that was never committed. At least when you say "read uncommitted" out loud, there's a much more honest and cautionary implication of what it means than "nolock," which sounds fantastic when you don't know better.)

Even if the hint got there by accident, when you use NOLOCK to ultimately drive writes, you have an unstable and unreliable decision set feeding durable changes. As we already know, the read can:

• include a row that never commits;
• omit a row that later commits;
• encounter the same row twice;
• miss a row because it moved during the scan;
• observe a combination of column values that never existed as a committed state; or,
• become stale before the eventual write occurs (a race NOLOCK certainly doesn't solve).

In all of these cases, the resulting write transforms that transient observation into persistent state. I think of it as turning a hallucination into a fact. Imagine dreaming that your boss approved your vacation, then waking up and immediately booking a non-refundable trip without checking whether the approval actually happened. You might come out of it fine, but you might not!

Ways an unstable read can poison a write

There are three categories of outcomes that can happen when using NOLOCK to drive a write:

• Wrong membership decisions
  • Update rows that should not qualify.
  • Skip rows that should qualify.
  • Cursor over the wrong population.
• Wrong values
  • Copy uncommitted or inconsistent values into durable columns.
  • Update from an arbitrary or unexpected matching source row.
• Wrong absence decisions
  • Skip inserts because of rows that roll back.
  • Insert duplicates because absence was not protected.
  • Let concurrent sessions both act on the same missing key.

Some specific patterns I see:

Dirty source drives an update

UPDATE t
   SET ...
FROM dbo.Target AS t
INNER JOIN dbo.Source AS s WITH (NOLOCK)
  ON ...

Here, Source determines:

• which target rows qualify;
• which values are assigned; and,
• potentially how many source rows match each target row.

The obvious failure is updating Target based on a change in Source that later rolls back. But you also have non-determinism if multiple source rows match one target row. NOLOCK makes an already questionable update even less trustworthy. Here, the target is protected, but the source of the decision is potentially unreliable.

Dirty cursor population followed by optimistic updates

DECLARE c CURSOR FOR
SELECT KeyColumn, OldValue
FROM dbo.Source WITH (NOLOCK)
WHERE ...;

Then later:

UPDATE dbo.Source
SET ...
WHERE KeyColumn = @Key
  AND OldValue = @OldValue;

This may be the most interesting one because the danger is spread out over time. The optimistic predicate may prevent some lost updates, but you still can't trust the cursor's original membership. The cursor can still:

• include a key from an uncommitted row;
• omit a committed row;
• collect duplicate keys;
• collect keys whose eligibility was only temporarily true; or,
• process a partial or internally inconsistent source state.

And if the later update only checks selected columns, it may update a row even though some untested part of the business condition changed. I've tried to stress to people when using a cursor or materializing entities to process that they repeat all of the predicates that drove that set in the first place. Not doing so means you can update rows that no longer meet all of the criteria, or update rows unnecessarily because they were already updated by another process.

Dirty absence checks feeding inserts

INSERT dbo.Target (...)
SELECT ...
FROM dbo.Source AS s
WHERE NOT EXISTS
(
    SELECT 1
    FROM dbo.Target AS t WITH (NOLOCK)
    WHERE t.KeyColumn = s.KeyColumn
);

This example has two almost opposite failure modes:

1. False presence: The dirty read sees an uncommitted row in Target, so this session skips the insert. The other transaction rolls back. Now the row exists nowhere.
2. False absence: The dirty read fails to see a row that exists or is being inserted, so this session also inserts it. You then get either a key violation, hopefully, or you insert a duplicate if no constraint protects the data.

Even without NOLOCK, NOT EXISTS alone is not sufficient concurrency control for "insert only if absent" under READ COMMITTED, RCSI, REPEATABLE READ, or SNAPSHOT isolation. None of these guarantees that the observed absence remains true when you act on it. The data model should usually enforce uniqueness with a unique constraint, and the statement/transaction must handle the resulting race appropriately (see this post on UPSERT patterns and refill your coffee mug for this series on isolation levels).

MERGE, NOLOCK, and missing range protection

MERGE dbo.Target AS t
USING
(
  SELECT KeyColumn, NewValue
    FROM dbo.Source WITH (NOLOCK)
) AS s
ON t.KeyColumn = s.KeyColumn
WHEN MATCHED     THEN UPDATE SET t.Value = s.NewValue
WHEN NOT MATCHED THEN INSERT (KeyColumn, Value)
    VALUES (s.KeyColumn, s.NewValue);

There are two separate problems to consider with a MERGE statement like this (and more potential problems with MERGE in general). First, a dirty source or auxiliary NOT EXISTS check can produce the same incorrect membership decisions as the examples above. Second, even a clean read doesn't protect an absent target key from being simultaneously inserted by someone else.

When MERGE uses the absence of a row to justify an insert, ask yourself what prevents another session from changing that answer before the insert occurs. Often that means serializable semantics on the target lookup, which we typically request using HOLDLOCK. And this is why you will find many of us in the community emphasizing the importance of this hint.

HOLDLOCK isn't a magic incantation that makes every MERGE safe, of course. And a unique constraint should still be the final authority preventing unexpected duplicates. The important point here is that NOLOCK moves you in precisely the wrong direction, as you're using weaker evidence to make a durable decision.

Placebo NOLOCK on update target

UPDATE t
   SET ...
FROM dbo.Target AS t WITH (NOLOCK)
WHERE ...

This is way more common than you might think. But it is different than the others: the hint has zero effect on the target of an UPDATE or DELETE. Microsoft claims that this syntax will be removed in a future release, but they don't really do that anymore. Still, I wouldn't leave it around. The author probably added it to "prevent blocking," but it can't do that here, and its presence only makes people think it's some kind of performance boost.

Someone on LinkedIn recently tried to convince me that this approach eliminated a blocking problem caused by a big update. They said, "all the blocking disappeared." I don't believe them. The scenario they describe may be real, but adding NOLOCK isn't what solved the problem; certainly, some other contributing factor changed between executions.

Maybe a wide update* was favored when the original plan was compiled, then a fresh compilation after the query text changed picked up newer statistics and produced a different plan that happened to cause less blocking. Or maybe the amount of data to update was vastly different, or a background process was no longer participating in the blocking chain, or an index that was expensive to update had been dropped, or any of dozens of other possible explanations.

* Paul White talks about wide updates here and here.

Don't act on approvals that are only in your head

I'd love to just say remove NOLOCK everywhere, or at least on every single update-driving query you can find, and call it a day. But my parting advice depends on what you need to guarantee:

• Need a consistent but non-blocking source read? Consider RCSI for statement-level consistency or snapshot isolation when the decision spans multiple statements.
• Need to claim work once? Use an atomic update/claim pattern, often with UPDLOCK and carefully chosen READPAST.
• Need "insert if absent"? Enforce uniqueness, then either protect the absence with appropriate key range locking, or be prepared to handle a duplicate key race.
• Need optimistic updates? Capture and validate a rowversion or the relevant original values, but populate the work list from a trustworthy read.
• Need to update from a join? Ensure one deterministic source row per target and use an appropriate isolation model.

If a dashboard briefly reports 672 orders when there were really 671, sure, that's bad. Maybe someone notices, maybe they don't. Maybe the next refresh corrects it, or maybe it gives you a different wrong answer.

The bigger danger is if that same unreliable observation determines which order gets updated, whether a payment gets recorded, a row gets deleted, a salesperson gets their bonus, or an insert should be skipped, the consequences can outlive the dirty read that caused them.

Again, I'm not going to tell you that you must eradicate every NOLOCK hint in your system tomorrow; I've already admitted defeat on that crusade. But I would shine a much brighter light on reads that are used to drive durable changes. Before you use one of those rows to update, insert, delete, or decide to do nothing, make sure it represents something that actually happened.

In other words, don't book your vacation until you've checked that your boss really approved it.