Question

My answer

Transcript

Question

Interview question number five:

How do you stop deadlocks from happening in a SQL Server?

Answer

Let’s talk about stopping some deadlocks. Here’s how I would answer this question:

To stop deadlocks in a SQL Server, the first thing I need is information on what deadlocks are happening, what queries are involved with it, what types of locks those queries own, and what types of locks the queries have requested.

The best way to get this information is by collecting a deadlock graph. I like to collect that using an Extended Events trace.

Once I have the deadlock graph, I’ll analyze it. Usually, the first thing I look for is what indexes they’re using with these locks, and I try to check and see if possibly an index change in SQL Server could get some of the queries onto a different track and mean that the deadlock doesn’t have to happen in the future.

Do’s and don’ts with this question

I would not just say you’d solve it with a NOLOCK hint, because reading incorrect data is usually not a great answer. If it’s okay for one of the queries to read incorrect data that’s involved in the deadlock, maybe it’s okay to not just run that query, too, right? NOLOCK is just not an avenue I would go down with this query. That may raise concerns that you’ll throw hints on queries when maybe you shouldn’t.

Do mention specific tools

I mentioned that deadlock graph, and that I like to collect it with Extended Events.

Mention experience whenever possible

And I would – if you can and it’s appropriate – mention experiences in the past. It’s a little more powerful to say: “In the past I have solved this by….” That phrasing carries more weight than just saying, “I would solve it with….”

Of course, make sure not to reveal specifics that you shouldn’t about embarrassing things in code in employers past. But knowing that you’ve actually done something and telling them that you’ve done it does carry weight.

It carries weight, even if you’ve never handled a deadlock in a production system. If you use the demo scripts in this course and you practice solving the deadlock there, even saying that –

Saying, “I took an online course where I used sample code to create a repeatable deadlock. And I practiced getting the deadlock graph, analyzing it, and testing out and implementing different fixes to prevent the deadlock.” Even just saying that shows your investment in your learning, that you do hands-on learning, and that you remember it. That can be really, really powerful in an interview situation, even if you don’t have production experience with that skill.