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