When you find yourself using NOLOCK, consider NOSQL instead.
- Your query can see the same rows twice
- Your query can skip rows altogether
- Your query can see data that was never committed
- Your query can fail altogether
If you’re okay with all 4 of those, and you find yourself REGULARLY applying NOLOCK, you probably don’t need the expense of a relational database at all. You’d probably be better off with an eventual consistency platform that trades accurate results for speed.
30 Comments. Leave new
Strong consistency versus eventual consistency: you choose based on your use-case – there is no free lunch option.
Or you can disable page/table lock escalation where you are getting blocked and make sure you have enough memory to handle row only locking. This has worked well for me on large systems where nobody is really trying to change the same row twice but there are bursts of reads or large inserts causing SQL to want to escalate things. I have not written a NOLOCK in a long long time.
I strongly, strongly recommend against that approach. When you move on to another company, that is a nightmarish hellscape for someone else to inherit.
Hi, what alternative would you suggest?
We struggle with lock escalations too. A table with >50 mil rows gets updates that affect between 1k and 20k rows in some operations while normal single record inserts/updates go on in the background with no overlap. When it gets above the magic threshold, these other statements get blocked. We tried the allow_table_escalation false in the table definition but the engine treats this more as a hint then a strict rule and ignores it. The vendor already minimized the time the transactions are running. Splitting the table would be an major redesign.
The 90Bytes * 20k rows are a small memory price to pay for happy users. Updates on the whole table are only done in vendor version updates during scheduled maintenance.
Any advice appreciated
I forgot: we use RCSI, so it’s only writer-writer-blockings.
Great question! We discuss it in detail in the blocking module of my Mastering Server Tuning class.
But is it 90 bytes * 20k rows? What if each of those rows are on a different page? What if there are multiple indexes on the columns you’re updating? Here’s 2 things I try to consider when updating large numbers of rows:
1. Update them in batches. Don’t just write an update statement, build a script which identifies the first say 1000 rows to be updated, which runs in a loop. Updating smaller numbers of rows at a time will reduce locking issues.
2. Where possible, update these batches in the order in which they appear in the clustered index. This reduces the number of pages which need to be written for each update, which not only makes it run faster, but also reduces locking issues.
IIRC those 90 or 96 bytes per locked row is the memory overhead for the engines internal locking administration itself on top of the data pages. Those have to be in memory anyway.
1. The batching in 1000 chunks does not help if i cannot commit after each chunk. When updating the 20k rows is part of a bigger transaction chunking does not help. We would need to redesign and add some explicit undo logic instead of just rollback for the already commited chunks, if something goes wrong later.
2. Performance and memory are not the problem here (20k rows are peanuts) it’s the lock escalations which prevents more than one of these processes in parallel.
MS is very bad at adjusting things for inflation. I’m sure that about 4000 row limit was fine when they introduced it decades ago when memory was an issue in 32 bit world, but things have changed. Same with CTFP
@Brent: Thank you. I think my coworker still has access to those from the big GDPR sale last year.
It’s so funny, the consultant/trainer/blogger in me is screaming, “No, this design pattern is terrible for SQL Server! This is the worst case scenario! If you’re going to work in batches, in parallel, then you need to keep it under 3-4-5,000 rows! Don’t work in transactions! This isn’t how you load data!”
But the business person in me hears you about how 20k rows really are peanuts.
Check this out: https://github.com/ktaranov/sqlserver-kit/blob/master/SQL%20Server%20Trace%20Flag.md#1224
Thanks alot. I have no experience with trace flags, but I’m desperate enough to try.
We only need it for that one table, server memory is 20x the size of that table and memory is not the bottleneck (so the 40% rule will likely never hit) and we can limit the TF scope to the sessions doing these 20k updates. The blast radius of that change should be minimal. Great!
I constantly look at NoSQL and think, man there are lots of cool things i can do…….then I have to make a single join in the POC and bash my head into the desk so next time I’ll remember the pain.
They have their place, I just haven’t hit a use case where the pain of running multiple DB products is leas than the pain of trying to store small amounts of barely relational data in a RDBMS.
Agree. I don’t understand why Brent made this suggestions and don’t feel he laid out a proper case. Even *if* you are okay with eventual consistency, that has absolutely nothing to do with whether your data is relational (or more accurately, whether or not you actually need to run relational queries)… which is the reason you would or would not choose a NoSQL database.
What say you Brent? Am I off base here?
Brandon & Jay – my guess – and this is just a guess – is that you heard the term “NoSQL” when it first came out, took a brief look, and haven’t looked since. These days, NoSQL platforms do indeed support things like joins. Check ’em out! Tools are always evolving – if you don’t continue to evolve with ’em, your skills can get rusty.
I work mostly with financial / order data that is inherently relational.
There are a few places on other consulting gigs I have used NoSQL and actually do like it. Just that LOB order / inventory tracking systems just never seem to need NoSQL.
Your guess is reasonably accurate. What really turned me off to them (for relational data) was seeing several stories online of people who thought they would be great, loved it long enough to get a project into production, but then realized it was the wrong choice as the application continued to evolve… and then had to rip it out and replace with a SQL database.
It’s interesting to me that you’re suggesting NoSql databases are “eventually consistent”. I definitely didn’t know that. Guess I need to review what I know!
RCSI with a healthy TempDB might be a good substitute for NOLOCK
Would you NOLOCK your house when you go to bed at night?
Using NOLOCK on queries where accuracy is required is equivalent to driving on the highway without a seatbelt. You may, or may not, be okay.
I wish there was a trace flag to disable the ‘no lock’ feature as with optimistic locking and the correct indexes I am hopeful the problem the developer used the ‘feature’ for won’t exist.
Is there a way to disable the ‘no lock’ in a query an let the query run?
It would be nice if there was a stream editor we can put in front of the query engine to fix the developers code 🙂
A MS SQL Consultant once told me that “NOLOCK is the crack cocaine of SQL Server. When you start using, everything is wonderful, until you realise that you’re addicted to it.”
Some thoughts…
Do the vast majority of people who use NOLOCK do so without issue? How much pain has NOLOCK *actually* caused in the real world and how often?
Would you eat raw sushi in Japan? They report over a thousand cases of illness from it a year, and I don’t think anyone there would answer yes if you simply asked “would you like to eat food that might contain parasites?”, but yet they continue to eat sushi. And I would bet many who’ve gotten sick from it still continue to eat it.
But unlike sushi, NOSQL actually solves a real problem. So it’s like, would you trade daily constipation for the “possibility” of maybe getting a treatable case of parasites once or twice a year? As terrible as parasites sound, I actually think daily constipation would be worse, lol.
And I understand you can change the analogy and compare to a situation that exacerbates the evils of NOLOCK so it would be like having parasites every day… or even having a non treatable type of parasite. But that goes back to my original question: how much pain has it *actually* caused and how often?
Of course I wouldn’t use NOLOCK in code that automated surgical equipment or an F-15 in flight depends on, but if an average developer on an average project can use NOLOCK to postpone the cost of having to pay Brent Ozar to come fix your servers for 2 or 3 years… hey that might be worth it, right?
I personally haven’t felt a desire to use NOSQL in a long time, and that’s probably because the more experienced I become the less use I have for it as I have better solutions. I still wouldn’t hesitate to encourage less experienced me to keep it in his tool bag though.
Argh… NOSQL = NOLOCK, lol
Brandon – how would you measure whether it caused a problem?
I’ll give you a real-life example: I was consulting at a client where they were double-paying some of their bills, and they didn’t understand why. It turned out someone had recently added NOLOCK to the query that listed which checks needed to be paid – so they were seeing rows twice randomly from time to time.
So you tell me: if your company randomly paid some of their bills twice, and the vendors on the other side kept the money – would you know? And how would you know how much that cost you?
I’ll wait.
Was I so extreme when I said “Of course I wouldn’t use NOLOCK in code that automated surgical equipment or an F-15 in flight depends on.” that I failed to get the general point across?
And how does your anecdotal experience play in with the totality of everyone’s experience? Are you going to stop driving because of how many people die per year from car accidents? No, because the benefit far outweighs the risk… (even though the risk is DEATH! Far worse than double paying a vendor!) as is the case for the vast, vast, vast majority of places where NOLOCK is being used.
If you think paying checks is the same as automated surgical equipment, then I think we’ll need to agree to disagree. Thanks for stopping by.
You are extremely intelligent. Too intelligent to think I meant that (and you know I didn’t say that). But I guess you’re not above suggesting it anyway? Come on, Brent. I’m totally happy to agree to disagree, but twisting people’s words isn’t very cool. If this isn’t a place for open discussion, but more of “you agree with me or I’ll use logical fallacies against you” then I’m happy to keep my thoughts to myself in the future. Peace out.
I’m totally bluffing, by the way. I’ll keep posting my thoughts whether you like it or not. 😉
But seriously, twisting people’s words is not cool.
Also, thanks for all you do. You’re a good man.
Brandon, if you think what I’m doing is twisting your words, then further discussion from my side won’t be productive.
I appreciate the thanks, but I’ll stop discussing this particular issue with you for now. Cheers.
Just use Microservices 😉
Tim, would you mind pointing me to any resources you might know of that would relate to this topic? I don’t know a lot about microservices, but I would have thought they would all still have to access the same underlying database and, therefore, have the same issues when it comes to locking.
Maybe it’s simply the architecture that microservices encourage (which could be done without microservices) that provides the benefit to which you’re referring, in which case your comment still makes sense… but if there is anything more to it than that please do share! 🙂