Two Of My Favorite NOLOCK Demos from Paul White and Aaron Bertrand

Two Of My Favorite NOLOCK Demos from Paul White and Aaron Bertrand

---

This week, I’ve got a coupon that offers an even steeper discount on my training than the one at the bottom of the post.

It brings the total cost on 24 hours of SQL Server performance tuning content down to just about $100 USD even. Good luck finding that price on those other Black Friday sales.

It’s good from December 23rd-27th, or until I remember to turn it off. Hurry along now.

Thanks for watching!

Video Summary

In this video, I delve into the world of isolation levels in SQL Server, particularly focusing on the pitfalls of using `READ UNCOMMITTED` and `NOLOCK` hints. Wearing a new Adidas trefoil shirt, I wanted to take a moment to thank two influential figures in the SQL Server community—Paul White and Aaron Bertrand—for their invaluable contributions that have shaped my learning journey over the years. To keep things fun and engaging, I also share how you can support me by becoming a member of this channel for just $4 a month, contributing directly to my Adidas t-shirt fund. If you’re not into t-shirts, feel free to subscribe or leave comments—every little bit helps, even if it’s just the satisfaction of seeing numbers on a screen increase! Whether you need SQL Server consulting or high-quality training at an unbeatable price, I’m here to help. So, let’s dive into some practical demos that illustrate the challenges and risks associated with `NOLOCK` hints, showcasing how they can lead to data inconsistencies and errors.

Full Transcript

Erik Darling here with Darling Data. Wearing a slightly different Adidas shirt than my normal three-stripe, I got the trefoil, I believe it’s called, design going today. Feeling pretty spiffy, to be honest with you, very spiffy. And today’s video, I want to give kudos to a couple people in the SQL Server community who have really helped me to get the data. help me a lot directly and indirectly in my SQL Server learning over the years. Of course, that’s Paul White and Aaron Bertrand, you know, prolific answers on the stack sites, prolific bloggers, at one point, prolific presenters. But the way that I want to do it is because I’ve been talking a lot about isolation levels. And even in one of my isolation levels videos, I talk about, you know, the problems that you are into with read committed or nolock hints, which are synonyms for each other. And one of the things that I talked about in there, which I didn’t have a demo for at the time was when you have a query that hits an error because of read uncommitted nolock because of data movement. So I have a demo from Paul White that shows that and then I have a very cool demo that shows missing and skipped and double counted rows because of nolock hints in tables. But before we get into that stuff, we of course need to talk about how you can buy me more new Adidas t shirts. For four bucks a month, you can sign up and become a member of this channel and contribute to my Adidas t shirt fund. I can’t promise that they’re all going to have different logo designs on them. But they will all be black and white. So at least we’ll have some consistency.

There and me and my friend bats will continue to match. So if you if you put like I wonder, I think if I just put this close enough to the camera and just sort of superimpose his face on mine, it’s probably a close enough match, at least as far as I can tell. If you don’t want to contribute to my Adidas t shirt fund, if you’re just feeling especially scroogey and stingy at this time of the holiday season, you can like you can comment, you can subscribe. And well, I won’t get any new t shirts from that. I will live with the immense gratification that comes from numbers on a screen going up when I hit refresh. So there’s that. If you need help with SQL Server, perhaps you’re saving that $4 a month. Because you’re like, I’m going to hire Erik Darling as a consultant someday. I’m pretty good at all these things. In fact, beer gut magazine says that I am the best in the world at all these things. So if you if you need help with a SQL Server that is giving you problems, you should stop talking to other people because they’re a waste of your time. If you need some very high quality, very low cost training, you can get 24 hours of my SQL Server performance tuning training for about 150 USD.

You can go to the link up there and you can put in the discount code over there or you can click on the link in the video description and find the same thing all in one place. It’s amazing the way technology works, isn’t it? Again, upcoming events, nothing for the rest of this year, maybe next year. If there’s an event that you care about and you want me to come to, let me know what it is. I’m not psychic, and I’m not constantly just out in the world looking for events to go to. I am very selective in what I apply to.

So if there’s something good that I don’t know about, well, gosh darn it, let me know. And with all that out of the way, let’s talk about some of these null locking problems. So the first demo that I’m going to show you is Paul White’s demo.

We’re going to create a simple table. It’s got no indexes. It just has one column in it. It doesn’t have to look like this in order for you to hit this problem. This is just a very easy and convenient way to do it.

In the first session, we are going to have this query running. And gosh, this is a very, very clever query. Because not only are we setting the transaction isolation level to read uncommitted up here, but despite of that, we are starting a transaction with a name, a name transaction.

It’s amazing. And then, well, this, I don’t know, perhaps slightly less clever one equals one loop runs. Over in this window, we are going to do this.

And we are going to say, while a transaction with this name exists, do this and insert some data and delete some data. All right? So pretty simple stuff there.

Okay? So we’re going to come back over here and we’re going to start this loop off. And this thing is just going to run and do a thing and assign a thing to a variable. And then as that’s running, we’re going to run this portion of the code.

And I want you to keep an eye on the window on the tab right next to this one that says executing. Because it’s going to stop executing real quick. As soon as I do that.

Right? That was a very, very quick cutoff. And, of course, over here we have this lovely message. Could not continue scan with no lock due to data movement. Again, very simple but effective demo.

This isn’t exactly what has to happen in order for you to have problems with no lock queries hitting these errors. It’s just a good example of how you can actually hit it. It’s bad.

It’s not good for you. It’s painful. Good luck figuring this out. Just looking at the error log. All right. Demo number two. Aaron Bertrand. Great fella.

Rumored to be Canadian. But we’ll let that slide. And this comes from a blog post that Aaron wrote. So I should have the link for it somewhere. If not, just look for Aaron Bertrand.

No lock. Not only will you find this post, you’ll find lots of other great posts about no lock. So in this query, Aaron also has a loop that runs and does stuff. Right?

It’s this thing. And he also has an update that runs. And it just says go 100 times. So I’m going to kick this off because this runs for a bit longer than the other thing does. And if we run this, we’ll see double counted rows eight skipped rows seven.

Right? Already we have hit problems. And we can keep running this until that go sort of exhausts itself. It does go 100 times.

This time we have one double counted row and seven skipped rows. Then we can essentially just keep doing this. And now we have, oh, two double counted rows and seven skipped rows. So this just gets more and more interesting the more you do it.

I guess seven and eight is a pretty common scenario for this thing having problems. Seven and eight just doing its thing. Oh boy.

This one looks a little bit different too. So this one we skipped a bunch of rows and then, wow, we skipped a lot of rows and then ended up double counting some rows. So you can see that no lock, I mean, you know, aside from the demo that I showed you in my isolation level series where there was an update and one of the tables referenced in the update had a no lock hint.

And we ended up putting bad data into a table. No lock hints can, of course, cause all sorts of problems just for your regular select queries, whether it’s errors because no lock cannot continue to scan due to data movement, never a good time, or you just start returning incorrect results of things.

Because you are no locking and seeing all sorts of things happening, flying like monkeys all around your query. It’s not a good time.

All right. So as always, stay safe out there. Don’t use no lock hints. Please use optimistic isolation levels. Do not harbor under the misgivings of others that optimistic isolation levels cause dirty reads and other unfortunate things because that’s just not true.

They prevent these problems. They prevent lots of other problems. They prevent locking and blocking and deadlocking problems. They’re really wonderful. You should try them out sometime.

Anyway, just a quick video today. Thank you for watching. I hope you enjoyed yourselves. I hope you learned something. And, well, I don’t know. Depending on when this video publishes, I hope you have a great day and or weekend and or life.

May our roads someday cross again. All right. Goodbye. Bye.

Going Further

---

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.