SSMS Tips and Tricks 4-12: Avoiding deadlocks when working interactively

Several times, I’ve seen situations where a user who’s working interactively in SSMS ends up causing deadlocks and causing issues for an application that’s in use. This is even more likely for users who hold locks for long periods of time, and who work directly with production systems.

At best, they might just cause an application to hang. At worst, they might cause a poorly-designed application to terminate.

Why would a user hold locks for a long time? Many users work in what’s called chained mode, where they automatically start a transaction when they make any sort of update. Some other database engines default to that behavior, but you can choose that as a session option in SQL Server as well.

Another class of user that might do this is the nervous administrator who manually starts a transaction before doing any sort of work, because they’re not confident of the outcome, and want to be able to roll the actions back.

Regardless of why they’re holding locks for long periods of time, the problem is compounded if they start causing deadlocks as well.

One option that’s worth considering is setting the deadlock priority for your session to LOW:

If you do that, you’re saying If a deadlock is going to happen, I’m willing to be the victim. At least then you’ll get your query terminated instead of the application that’s also using the database.

If you wanted to make this the default for all query windows that you open, you can set it in Tools Options:

Another option you might consider is adding it to your default query template, as discussed in a previous tip . Then you get to see it in the query window and decide if you want it or not for the query session.

2025-11-15