No, Accelerated Database Recovery Doesn’t Fix NOLOCK.
I have never seen a T-SQL feature that people love as much as NOLOCK.
I keep thinking I’ve written enough blog posts about it, but a client came up with a new one:
We use SQL Server 2022’s Accelerated Database Recovery, which keeps copies of versions inside the table. Plus, we don’t use transactions – our inserts, updates, and deletes are done one table at a time, and your demos always have transactions in them, so we’re not affected.
That’s not how this works. That’s not how any of this works. To illustrate it, we’ll set up our SQL Server 2025 and Stack Overflow database with the latest and greatest of everything:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
USE StackOverflow; GO ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 170 GO ALTER DATABASE CURRENT SET ACCELERATED_DATABASE_RECOVERY = ON; GO DropIndexes; GO SELECT COUNT(*) FROM dbo.Users WITH (NOLOCK) WHERE DisplayName = N'alex'; GO 20 |
And while those selects run, we’ll run a single update in another session, no transaction:
|
1 2 3 |
UPDATE dbo.Users SET Location = N'NOLOCK City Limits, Nevada' WHERE DisplayName <> N'alex'; |
And the select’s row counts keep changing – hang in there, and I zoom in:
I’m not playing. When your queries use NOLOCK or SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED:
- You can read rows twice
- You can skip rows altogether
- You can see data that was never committed
- Your query can fail with an error (could not continue scan with nolock due to data movement)
If you think your scenario is somehow magically different, check out these other NOLOCK demos before you claim victory.
In the client’s case, someone on the call said, “Your demo drops indexes, but we have indexes, so we’re fine.” No, my demo just drops indexes because I wanna demonstrate the issue as quickly as possible. Depending on what you’re querying, and the kinds of data modifications that are going on, and what indexes exist, you can absolutely still see random results.
Someone else piped up and said, “We’ve never seen an issue, though.” The answer: well, are you monitoring the accuracy of your app’s output? Of course not. You’re relying on users to tell you when the data on the screen is wrong, but they have no reason to suspect that it is. If the report says we sold $1,234,567 of widgets last quarter, then that’s what the user’s going to believe. If they run it again five minutes later and see $1,324,765 of widgets, they’ll assume someone’s been changing/fixing things, and the latter number is the right one. Users are gullible suckers.
That doesn’t mean I always avoid nolock! In situations where I don’t need exact accuracy, nolock is totally fine. You’ll find it in a lot of the First Responder Kit scripts, for example. It’s not like I need transactional accuracy when I’m rendering how many reads your index had.
But if your queries do need accurate results no matter what else is happening in the database, no matter what indexes happen to exist at the time, then check out Read Committed Snapshot Isolation (RCSI) or Snapshot Isolation (SI). Implement those, pull out your nolock hints, and get the accuracy your users expect.
8 Comments. Leave new
But what does any of this have to do with Accelerated Database Recovery?
Accelerated Database Recovery, like RCSI, keeps prior versions of rows around automatically. The client thought that SQL Server would automatically use the prior version of a row would be used if the current version was blocked – because that’s how RCSI works.
However, just like RCSI, if you specifically ask for dirty reads with NOLOCK, you’re gonna get ’em, even if there are non-locked versions of the row available.
I still hear senior DBAs arguring for the use of “NOLOCK” when I have been trying to get rid of NOLOCK code for more than a decade.
You make a great point with NOLOCK is that it’s OK if you don’t need a precise number. This is great when you are trying to count the number of orders currently in the queue so you can determine if the backlog is up or down. A precise count isn’t needed. Of all the TSQL I have written over the past 25+ years, I think I have only written a couple of those types of queries. Like a “goto” in C, I have used it a couple of times in the past 40 years when there was no other alternative. “NOLOCK” should have to be strongly justified or else it should never pass a peer review.
“I still hear senior DBAs arguring for the use of “NOLOCK””.
Well, that *might* be their job title, but they clearly aren’t if they’re saying that.
I mean, put it this way, they think that for a third of a century, Microsoft – a company that hires some of the brightest in the business, know so little about their own product, that they’ve completely missed a simple automagical way of “making all your queries 25% faster” with no risk, and no overhead, no problem, and not made it a default (that you can override if you must) because they don’t know what they’re doing? But some random with a blog has stumbled on it – it’s quoted right left and centre – but they’ve entirely missed all of that, so this is why they’ve apparently forgotten to even recommend this?
Seriously, they actually think that? They believe they know a little secret that the MS SQL Server team are blissfully unaware of, in their own product.
I find that some of the worst TSQL written is written by DBAs. I don’t know how many messes I have to clean up because DBAs do things that would never pass a peer review. Brent makes the point if the number doesn’t have to be exact and almost it’s good enough it’s OK but most everything I do requires the data be accurate. Nothing is more frustrating to try to debug is some ancient query that has been around forever and users complain that the query isn’t returning the correct data.
This illustrates one of my favorite sayings:
“The most dangerous people you will ever encounter are those who do no know what they do now know!”
Thank you for this, again!
Wish I could blast this to every db professional I know. My current, past and likely future co workers seem to think NOLOCK is some magic bullet.
Only when I refer to your post or show them dirty read results do their eyebrows raise, and even then, for God knows what reason they won’t remove them. How did the person or article who said NOLOCK was a good idea stick and these demystifying Brent Ozar posts not??
Just to pile on ?. I wish people understood that just because they didn’t create an explicit transaction doesn’t mean that a transaction didn’t occur.