Transcript

Welcome to the Dirty Secrets of NOLOCK. I’m Kendra Little from SQLWorkbooks.com.

Today we’ll be talking about: what is NOLOCK, and what are the dirty secrets, as well as some other aspects to it. What does NOLOCK mean and when do we use it?

NOLOCK is a table hint in SQL Server

The word NOLOCK itself is a shorthand code, because we have this table hint, people often refer to this as using NOLOCK. What we’re saying when we use NOLOCK is that we are using an isolation level called READ UNCOMMITTED in SQL Server. If you use WITH NOLOCK as a hint in your TSQL, you specify it as a table hint.

If your query has multiple tables you can use the NOLOCK hint to say: I want READ UNCOMMITED against this table, but I’m not going to hint the other table.

So you can specify it at a table level, what isolation level you want to use.

There’s also a table hint you can use that’s the same thing called the READUNCOMMITED hint

NOLOCK is much catchier and much easier to type so people almost always use the NOLOCK variant.

You may also set the isolation level for your entire session

You have the option, also, if you want to use READ UNCOMMITTED against every table in your session, you can run a statement: SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED.

That means I want my default isolation level for this session to be READ UNCOMMITTED and use dirty reads unless I hint otherwise in one of my queries. So there are a couple different ways we can use NOLOCK. The “WITH NOLOCK” hint you’ll see in some of our demos, that is the most frequent.

We’re going to talk about those dirty secrets of NOLOCK

The things that you need to be aware of if you use NOLOCK, and also: what are the potential uses of NOLOCK? I use NOLOCK sometimes. There are some uses, but these are very specific cases, and you want to really think about it before you use NOLOCK.

When it’s not appropriate to use NOLOCK, what are my alternatives? Because people use NOLOCK for a reason, this hint– although it has these dirty secrets, it finds its way into a lot of production application code, because of real problems that people are struggling with.

Most of our time today is going to be spent in a demo, so let’s take a look at a demo of those dirty secrets. And for those of you who aren’t fans of the poop emoji, the good news is that I haven’t found a way to use the poop emoji in Management Studio. So you won’t have to see it through the majority of the presentation.

💩