Software Vendor Mistakes With SQL Server: Mistaking NOLOCK For Concurrency

Posted on by Erik Darling

Corner Boppin’

I totally understand the Cult Of NOLOCK. Blocking sucks. Fixing it is annoying. Read Committed is a garbage isolation level.

Then along comes some beleaguered colleague who whispers the magic incantation into your ear, and suddenly those problems go away. A week later you have a keyboard shortcut that inserts WITH (NOLOCK) programmed into SSMS and your muscle memory.

You’ve probably already read a thousand and one blog posts about what a bad idea NOLOCK is, too. But you’ve just never had a problem with it, whether it’s dirty reads or errors.

And besides, no one actually knows if data is correct anyway. Have you seen data? Quite a mess. If it looks weird, just run it again.

You read something scary about optimistic isolation levels twenty years ago. Why change now?

Try Harder

Let’s look at where all your NOLOCK-ing won’t save you.

CREATE TABLE
    dbo.beavis
(
    id int PRIMARY KEY, 
    heh datetime
);

CREATE TABLE
    dbo.butthead
(
    id int PRIMARY KEY, 
    huh datetime
);

INSERT 
    dbo.beavis
    (id, heh)
VALUES
    (1, GETDATE());

INSERT
    dbo.butthead
    (id, huh)
SELECT
    b.id, 
    b.heh
FROM dbo.beavis AS b;

Here’s the most common deadlock scenario I see:

/*Player one*/
BEGIN TRAN

    SET TRANSACTION ISOLATION LEVEL
        READ UNCOMMITTED;
    
    UPDATE b
        SET b.heh = GETDATE()
    FROM dbo.beavis AS b WITH(NOLOCK);
    
    /*Stop running here until you run 
      the other session code, then come 
      back and run the next update*/
    UPDATE b
        SET b.huh = GETDATE()
    FROM dbo.butthead AS b WITH(NOLOCK);

ROLLBACK;

And then:

/*Session two*/
BEGIN TRAN

    SET TRANSACTION ISOLATION LEVEL
        READ UNCOMMITTED;
    
    UPDATE b
        SET b.huh = GETDATE()
    FROM dbo.butthead AS b WITH(NOLOCK);
    
    UPDATE b
        SET b.heh = GETDATE()
    FROM dbo.beavis AS b WITH(NOLOCK);
    /*Stop running here*/

ROLLBACK;

Cautious Now

If you go look at the deadlocks that get produced by these queries, you’ll see something that looks like this:

SQL Server Locking Deadlocks
pies

Despite the isolation level being read uncommitted and us festooning NOLOCK hints all about the place, we still end up with deadlocks.

Using these hints doesn’t always help with concurrency issues, and this goes for many other situations where locking and blocking has to occur.

At best, your select queries will be able to read dirty data rather than get blocked. I’m way more in favor of using an optimistic isolation level, like Read Committed Snapshot Isolation. All NOLOCK really means is that your query doesn’t respect locks taken by other queries.

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.

Related Posts