Question

My answer

Transcript

Question

Interview question number two:

When should you use NOLOCK on a query?

Answer

This question, I think, is a tough question to answer, but I think it’s really a fun one.

You might take a different style at answering this question depending on the kind of job that you’re interviewing for.

If you’re interviewing for a job that does production support, like a DBA job or a DevOps job…

You might talk more about production support style activities during this, and you might say something like this:

NOLOCK hints mean that your query can read data that hasn’t been committed yet in the SQL Server. Maybe it’ll never actually be committed, so you might get incorrect results. That means it’s not really a good fit for most production code.

But let’s say I’m doing more of a DBA task and I’m investigating an issue. If I don’t need the data to be absolutely correct, and I want to minimize the chances that I’m going to do blocking, a NOLOCK hint might be okay for that kind of investigation query.

But of course, you’ve got to be really careful and make sure that it’s okay to query that database, and that it’s okay if the results may be wrong.

If you’re interviewing instead for more of a database developer style job…

Where it’s more about not doing production support but writing code ahead of time, or if that’s part of the job, you might want to talk about when NOLOCK could be programmatically beneficial for that.

So here’s a different answer for that. NOLOCK hints can sometimes – if you’re scanning a large object – SQL Server can use the NOLOCK hint to do what’s called an allocation order scan, and in some cases, that can have a performance benefit.

Now there’s a big caveat though. If data is changing when you’re doing this kind of scan, you could get wrong data back. You’ve got to make sure if you use this that data is never changing when the query runs. A big thing to think about there.

Alright, so different answers about a possible use.

Both answers talk about trade-offs

This is really a pros and cons style question. Ideally, you do want your answer to show that you know about the negatives, and you know about the trade offs, you don’t want to just ignore those, but if there are some edge cases for using this then you want to talk about them.

People may ask this kind of question to see if you know about the details of some topics.

A lot of people on a question like this will just say you should never use it, or it’s no big deal. In the real world, even though NOLOCK isn’t good for production code, there are some little edge cases where it can come in handy to know about it, but you do always just have to know that balancing act.

Remember, also, that you don’t know what’s in the codebase usually for the folks that are doing the interview for you

And if you come off as super judgmental about coding patterns that can be bad, you may look like somebody who wouldn’t really help them, but who might just cause conflicts with the developers.

It’s fine if you say what’s bad about a coding pattern, but you don’t want to make it sound like “oh that’s really dumb!”, essentially.

It’s better to know the pros and cons and weigh those.

This can also be a good opportunity to ask a question about why they are asking

Explain a balanced answer, and then it’s fine to follow up with a question like: do you use NOLOCK in your codebase a lot?

You may not get an answer from them, they may just move on to the next question, but you may also get really interesting information about problems they have had and challenges they faced– because there usually is something behind them asking that question.