Using NOLOCK? Here’s How You’ll Get the Wrong Query Results.

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:

We’re just setting everyone’s web page to ours. Then in a separate window, while that update is running, run this:

The results? A picture is worth a thousand words:

WITH (NOLOCK, NOACCURACY)

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:

  1. You can see rows twice
  2. You can skip rows altogether
  3. You can see data that was never committed
  4. 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.

Previous Post
Building SQL ConstantCare®: Separating Out the Videos
Next Post
Skewing Parallelism For Fun And Profit

3 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.

    Reply
  • Tim Cartwright
    October 26, 2018 12:29 pm

    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

    Reply
    • 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.

      Reply

Leave a Reply

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

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}