Slapping WITH (NOLOCK) on your query seems to make it go faster – but what’s the drawback? Let’s take a look.
We’ll start with the free StackOverflow.com public database – any one of them will do, even the 10GB mini one – and run this query:
|
1 2 |
UPDATE dbo.Users SET WebsiteUrl = 'https://www.BrentOzar.com/'; |
We’re just setting everyone’s web page to ours. Then in a separate window, while that update is running, run this:
|
1 2 |
SELECT COUNT(*) FROM dbo.Users WITH (NOLOCK); GO 20 |
The results? A picture is worth a thousand words:
Sure, we’re running an update on the Users table, but we’re not actually changing how many users are in the database. However, because of the way NOLOCK works internally, we keep getting different user counts every time we run the query!
That’s…that’s not good. But it’s exactly as designed. When you use dirty reads, also known as READ UNCOMMITTED isolation level, your query can produce incorrect results a few different ways:
- You can see rows twice
- You can skip rows altogether
- You can see data that was never committed
- Your query can outright fail with an error, “could not continue scan with nolock due to data movement”
Fortunately, there are plenty of easy fixes like:
- Create an index on the table (any single-field index would have worked fine in this particular example, giving SQL Server a narrower copy of the table to scan)
- Use a more appropriate isolation level – like, say, Read Committed Snapshot Isolation
- Remove the NOLOCK hint from the query – although you can end up with blocking, so you have to resort to tuning indexes & queries
Oh, and if you try this demo yourself, be aware that it’ll only work the first time. If you want to rerun it, you’ll have to use progressively wider values for WebsiteUrl. If you’ve watched How to Think Like the Engine, I bet you’ll understand why.
9 Comments. Leave new
Even without the WITH (NOLOCK) hint, counts on tables with concurrent updates can give wrong results. As you say, “Use a more appropriate isolation level – like, say, Read Committed Snapshot Isolation” . People often mistakenly think that READ COMMITTED is enough.
I rewrote your scripts slightly for use in a demo as they are a good demo of nolock badness. Here they are if you are interested:
— SCRIPT 1
— https://www.brentozar.com/archive/2018/10/using-nolock-heres-how-youll-get-the-wrong-query-results/
USE master;
SET NOCOUNT ON
GO
IF OBJECT_ID(‘tempdb..##URLs’) IS NOT NULL BEGIN DROP TABLE ##URLs; END
CREATE TABLE ##URLs (
RowID int PRIMARY KEY IDENTITY,
URL varchar(8000) NOT NULL,
);
INSERT ##URLs (URL)
SELECT TOP (100000) ‘http://foo.com/’
FROM sys.columns c
CROSS APPLY sys.columns c2
— pause and go start the other script
WAITFOR DELAY ’00:00:05′
UPDATE ##URLs SET URL = ‘http://www.AMuchLongerDomainName.com/path/’
— SCRIPT 2
USE master;
SET NOCOUNT ON
GO
SET NOCOUNT ON;
DECLARE @cnt INT = 0,
@cntNotCorrect BIT = 0
WHILE 1 = 1 BEGIN
SELECT @cnt = COUNT(*) FROM ##URLs WITH (NOLOCK)
— if the count is not equal to 100k turn on the count not correct var
IF @cnt 100000 SET @cntNotCorrect = 1
— only print out the values if the counts are off
IF @cntNotCorrect = 1 BEGIN
— if the counts WERE off, but are back to 100k then break out
IF @cnt = 100000 BREAK;
RAISERROR(‘COUNT: %d’, 10, 1, @cnt) WITH NOWAIT
END
END
RAISERROR(‘DONE’, 10, 1) WITH NOWAIT
IF OBJECT_ID(‘tempdb..##URLs’) IS NOT NULL BEGIN DROP TABLE ##URLs; END
Cool, thanks! I’m going to stick with mine just because it’s simpler – I like to have entire demo code easy to see onscreen to follow along while I’m explaining – but I understand why you did it this way.
Has this issue been fixed? I have tried your example repeatedly, and have yet to get different numbers from the second query. I have tried it in every compatibility level from 2008 to 2019, I do not have RCSI turned on, and even tried it on StackOverflow2010 as well as the full sized database (2020-06).
No, the issue hasn’t been fixed. My guess – and this is just a guess – is that you didn’t read the entire post carefully, like the part about the demo only working once, or you’ve got nonclustered indexes on the table.
NOLOCK can indeed totally produce bogus results when you have indexes, or when you run the same query repeatedly, but this specific demo was set up with those constraints.
DROPINDEXES did the trick… I was just using a restored copy of the database, which already had NC indexes on the table. Thanks!
Thanks for sharing this article! I’ts son important to understand this behavior because many people (including me) think that use WITH NOLOCK hints in the query is more faster than without using it.
Hi all,
although my friend has watched “how to think like the SQL engine” several times (and now plowing through other fundamentals to mastering classes), my friend is not sure he understands why will the demo work only on the first attempt. None of the phenomenas caused by NOLOCK (Dirty Reads, Non-repetable reads, Phanthom reads) fit here in its entirety.
Could somebody HINT my friend in the right direction, please?
Thanks
Great question. I don’t cover the internals of it in my classes because there isn’t really anything you can *do* about it, and I try to focus my training only on things that you can really take action on. However, if you’d like to learn more, start here: https://sqlperformance.com/2015/01/t-sql-queries/allocation-order-scans