How Bad Can SQL Server Deadlocks Be?

Deadlocks are part and parcel of operating an SQL server, but if you are not that familiar with keeping a database ticking over you might worry that they are a pretty severe issue.

To help you understand more about what deadlocks are, why they occur and how bad they can be for your server, here is an overview of the ins and outs of this problem.

Getting up to speed with deadlocks

Before delving into the meat of the matter, it is worth establishing the basics of deadlocks in an SQL server environment.

In the simplest terms, deadlocks occur when two processes hold an exclusive lock on the same resources, meaning that there is no way for them to progress without one being terminated. The experts at SentryOne have explained this in more detail and you can read the article here for more of an in-depth look at the nuts and bolts of deadlocking.

Taking a balanced view

While they may sound bad, deadlocks are actually very common and should not necessarily be seen as a major issue when they arise, although obviously there are some big exceptions that will require intervention.

The reason that deadlocks are desirable is that they are essential to maintaining data integrity in the case that multiple apps will be making requests of your server concurrently. Without deadlocking, things would quickly descend into chaos.

The key concern that they raise is that the process which is terminated by the software when a conflict arises effectively fails to complete. This means that it needs to be reinitiated to go through, and if the deadlock occurs again then it could get into a vicious cycle, compromising performance.

Understanding the benefits of blocking

One thing that any database administrator needs to establish is that there is a difference between full-blown deadlocks and the far more acceptable blocking which occurs in this context.

Like deadlocking, when server resources are locked by a particular process, no other process can swoop in until this has completed. The distinction is that blocking will not cause any process to be terminated; those that are in line to be allocated a resource will wait their turn patiently, until the block is alleviated. Deadlocks indicate that a locking issue cannot be resolved without a brute force approach, which is obviously not ideal. Thus it is important not to mix up blocking with deadlocks, as this could hamper your maintenance efforts.

Worst case scenarios

So with all of that in mind, just how bad can deadlocks get and is this something you should prioritize? Well, the worst case scenario is that a deadlock between two processes reoccurs repeatedly, with one of the processes constantly being singled out as the victim and terminated by the software before it can complete.

If this happens time after time, even if you have a mechanism in place to reinitiate the victim process after it is terminated, your database will be operating sub-optimally.

Some deadlocks are one-off instances, which can be safely seen as the server working as it should, while those that keep arising need to be dealt with.

Fixing deadlock issues

There are a few ways to address deadlocking on an SQL server, the first of which is to set trace flags and manually plot out the deadlocks which occur so that you can determine which processes, locking priorities and statements should be ameliorated.

You can also harness separate SQL server monitoring software to take a lot of the legwork out of the equation and provide automated detection of deadlocks.

Sometimes the culprit is simply scheduling too many tasks for the same time period; be sure to factor this possibility into your investigations.

Most of all, remember not to panic when deadlocks occur, as you can use the tools at your disposal to deal with them and it is far from a catastrophe for your SQL database. Introduce regular checks for deadlocks as part of your wider server maintenance schedule and your data should be accessible efficiently and securely, wherever you keep it.