Aionys series on deadlocks is a step-by-step guideline for developers specializing in database programming. Find out what they are, why you have them, and what you should do to unlock and, ideally, prevent them.

Let’s start.

MS SQL Deadlocks: What are they to Block the Flow

There is no guideline to implement deadlocks. You see them popping up when least expected. I believe you know how it feels when the deadlines are approaching, too fast. And here you are – stuck with deadlocks, the issue you really don’t have time and energy to investigate and, most important, solve. And the priority is critical. Got a performance issue? You might be as well kissing your peace of mind goodbye. 

Most developers are lucky enough to have heard the buzzword only. And haven’t actually dealt with the issue. 

Things are different at Aionys. Having several projects in development and some of them with huge databases, we have experience in handling them. And we aim to share our challenges and solutions with you. So let’s get to the root of deadlocks starting with what the heck they are.

What are deadlocks?

Deadlock is a big issue of relational databases. It is about several processes blocking one another when necessary resources get locked.

The processes wait infinitely for resources (row, table, etc.) to get unlocked. As a result, a server kills one of them.

Deadlocks not only slow down the system but also bring it to a halt. They most often surface when data gets changed – updated/ inserted/ deleted – and database servers are under high load. The DB size doesn’t matter that much as even the smallest one can get deadlocks. Most devs believe that the more create/update/delete requests, the more deadlocks. 

The project in focus

A recent project where we faced deadlocks is for healthcare. It has a huge database of patient records, appointments, and hospital lodging details. And it also has a long history. We started it three years ago. And over time, it has grown immensely with an increased amount of data handled. 

Initially, the project was delivered to optimize process management at a medical center, including time and attendance systems for staff and patients. Later, new features were added 

• payment processing
• billing and invoicing solutions
• management of doctor offices, and wards, etc.

For the first few releases, when the number of users was limited, the system worked smoothly. The issues appeared when more medical units were added. Interestingly, when doing our testing we didn’t have them.

 Team A first-hand experience

At Aionys, we work with MS SQL. When we first faced the deadlocks at the project, we turned to the team lead developer with over 10 years of experience in developing custom software. Good or bad, he hadn’t had such serious issues. For him, logs were just: deadlocks happen. 

So our development team started digging into the issue. The info we came up with was focused on multiple threads. Miraculously, they did wonders but not with MS SQL server requests. 

Next, we set out on asking around, especially guys with more experience in MS SQL. They recommended taking the following steps

• change the isolation level of the transaction 
• remove huge transactions
• split large transactions into smaller ones. 

Fortunately, 90% of deadlocks were solved. Unfortunately, performance decreased. Meanwhile, requirements advanced with database queries getting more complicated. 

We turned to asynchronization to increase performance. It didn’t take much time to understand that the more async requests, the more side issues. For example, updating/inserting/deleting data caused serious problems for the server. 

It looked like we were running in circles. In the end, we came up with a solution. Our next Unlock MS SQL Deadlocks post will shed light on it.