Transcript

There are still some potential uses of NOLOCK

The first use is– maybe it is okay for the data to be hogwash sometimes.

I have absolutely had situations where some customers or my employer said, when it comes to this particular data, we don’t care if it’s wrong. We want something to be there but it’s okay if it’s hogwash. It’s okay if it’s balderdash. It’s okay if it’s dribble.

And in that case, I do want someone very important and high level to write down: yes, it is okay that it’s wrong. Because if that just happens in a hallway conversation, not everybody may have the same memory of that conversation.

Let’s ask our SQL Server, hey SQL Server does the SQL Serer team use NOLOCK?

What I do is just look at my execution plan cache for queries that have query text like nolock or like uncommitted. This isn’t going to find all the queries who are using read uncommitted, just the ones that are in my cache, but this is a dedicated test instance that has recently started up and nothing other than my demo has been running against it. So we do see my demo code first, but we see a lot of other queries below this.

There are 293 rows in this dataset. Let’s just click on a couple.

This query looks like, hey, I am doing something with extended events. I’m selecting the target data from extended event related dynamic management views. Okay, so in some of the internal workings of Management Studio, we have cases where Management Studio and here’s its looking at, we’ve got a lot of different, this may be from sp_WhoIsActive, this second query, I’m not really actually positvie on that, which one that’s from.

We’ve got more that look like they’re from Management Studio here, but if we think about what Management Studio does, I don’t mean to say that it’s garbage I actually really enjoy Management Studio, I’m not saying that it’s full of hogwash. But if you’re writing the application Management Studio, you have to display information from dynamic management views, that may need to be updated by changes. People are creating tables, dropping tables. So if I’m showing a list of tables, what kind of locks do I want to take out on that data? Is it more important to show the right data, or is it more important to avoid blocking in that situation?

So the data that I see in there, there may be a hogwash, there may be a table in my list of tables that somehow I got it at a moment where the data hadn’t committed, and that table disappears, but that is a choice they had to make.

It is a very serious choice, but it, you know, it is a choice.

There are these difficult choices in the world and I don’t want to say that there aren’t.

There are alternatives we can use that I’m going to talk about. But we have to make sure that the alternatives work in our situation.

So Management Studio has had to make some compromises and use read uncommitted here. Right, I’ve got quite a few queries that have had to do that.

Another thing to take away from this is if you’re concerned about NOLOCK being used in your applications, because it is critical that data be right, and you just look at, okay, in my plan cache, are things using NOLOCK? As long as people are using Management Studio, you are going to see some things using NOLOCK even if it isn’t your code. We can’t just check, hey, is nobody using NOLOCK based on the hints because the tools that we use and the monitoring tools that we write ourselves will often use read uncommitted.

If I am writing a monitoring query against a SQL Server, I will often consider using NOLOCK on a query that is looking into the state of things on the SQL Server because I don’t want to block applications

I accept that okay, if I use this hint, and the data that comes back to me may be wrong, it may be inconsistent, but it is very, when it’s very important that I don’t cause blocking, or that I lessen my blocking impact, it may be the best route for me to take in those administrative queries.

So although I’m saying that NOLOCK can produce data that is garbage, I am saying, yes, I sometimes need to use it when performing administrative queries. It is most critical to think about when we’re talking about application data that is returned to our customers and our users. Using it on your DBA style queries, I would not be as concerned about that.