Some of y’all, bless your hearts, are really, really, really in love with NOLOCK.
I’ve shown you how you get incorrect results when someone’s updating the rows, and I’ve shown how you get wrong-o results when someone’s updating unrelated rows. It doesn’t matter – there’s always one of you out there who believes NOLOCK is okay in their special situation.
The latest NOLOCK defender left me an angry comment:
I challenge you to show us one example of using nolock on a static database with incorrect results.
No problemo – I assume you mean tables that aren’t getting inserts, updates, or deletes. Well, folks can do index reorganize operations during business hours – it’s a completely online operation, even in Standard Edition – and that hoses NOLOCK too.
I’ll create a table and populate it with 100K rows, but do it in a way that makes it highly fragmented:
DROP TABLE IF EXISTS dbo.HeapsOfTrouble;
CREATE TABLE dbo.HeapsOfTrouble
(Id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
INSERT INTO dbo.HeapsOfTrouble(Stuffing)
SELECT TOP 100000 'x'
FROM sys.all_columns c1
CROSS JOIN sys.all_columns c2;
SET Stuffing = REPLICATE(N'x', 3000);
Then, in one window, I’ll reorganize the index:
ALTER INDEX ALL ON dbo.HeapsOfTrouble REORGANIZE;
While in another window, I’ll run a COUNT(*) repeatedly:
FROM dbo.HeapsOfTrouble WITH (NOLOCK);
And presto: the results change, going up and down as the pages are moved around:
Even if you think no one’s doing index maintenance at your shop, the answer is still clear here: if you want to build safe, reliable code that produces accurate query results at all times, NOLOCK should not be involved.
I get why you use NOLOCK. You read a blog post somewhere once that said, “NOLOCK is faster because it does allocation scans instead of navigating those big hefty index pages.” But you never actually bothered to measure whether that’s a significant problem for you – and you never bothered to ask your users if they need accurate query results.
Sometimes, you really don’t need accurate query results, and in that case, NOLOCK can be fine. For example, one of my clients runs a monitoring query every few seconds just to see if any new carts have been started in their online store. They get the latest row, check to see whether it’s higher than the previous check, and if it is, they’re happy campers. If it isn’t, they don’t mind checking again to see whether they got bad data, and they check a couple of times before they sound an alarm. They don’t need accuracy on exactly how many carts have been started – they just want a rough idea that the latest value is going up. NOLOCK works fine for their needs.
But for the rest of y’all, when you’re ready to do things the right way, I welcome you to my SQL Server training classes.
You should probably take one of them quickly, before your boss reads this post and starts asking you why you were so vigorously defending your constant use of NOLOCK.