An SQL deadlock is a situation that occurs when two processes are competing for access to a resource exclusively. None of the processes gains access to that particular resource because the other process already has access and is preventing it.
This leads to a standoff because neither of the two processes can proceed with their request. The only way for one of these processes to complete its request is by terminating the other process.
Fortunately, SQL Server can automatically detect deadlock situations and terminate one of the processes, referred to as the victim, for the other process to complete execution. It is important to note that deadlocks not only occur with locks but also with worker threads, resources, Multiple Active Result Sets (MARS), and memory.
How to Identify a Deadlock
As discussed above, SQL Server automatically detects deadlocks and terminates one of the processes competing for resources. The terminated process is known as the victim. If your process is chosen as the victim, you will get an error message.
The error message gives you the ID of your process and tells you that the process was deadlocked with another process. It also tells you that the process has been terminated but you can run it again.
The competing process will be allowed to continue. Chances are that the other user might not even know that they had faced a deadlock situation. There are different types of deadlocks. They include;
Conversion Locks Deadlocks
Conversion locks deadlocks take place when there is a thread trying to change the exclusive type of a lock to another type. However, the thread is not able to proceed because there is another thread holding a lock on that particular resource.
They are classified into three, namely, Update with Intent Exclusive (UIX), Share with Intent Exclusive (SIX), and Share with Intent Update (SIU).
Cycle Locks Deadlocks
Cycle locks take place when one process wants exclusive access to a resource that is held by another process while at the same time the other process wants access to the resource held by the first process.
For example, process X has put a lock on resource A. However, it is waiting to gain exclusive access to resource B which is being held by process Y. At the same time, process Y wants access to resource A. A cycle lock deadlock happens.
How Does the SQL Server Deal with Deadlocks?
The SQL Server comes with a lock manager that is tasked with searching for deadlocks. It looks for deadlocks in the server every five seconds. To identify deadlocks, it goes through all waiting processes or locks to see if there are cycles that could indicate a deadlock.
If a deadlock exists, it decides which of the two competing processes will be the victim. The victim is then terminated and an error message is sent back to the client. The client is the owner of the connection.
By doing this, SQL Server ensures that all resources are released and the other processes can complete without any further delays. In case there are more deadlocks than anticipated, the server changes the frequency of searching for deadlocks to ensure that they are all resolved quickly.
How to Solve Deadlocks
You can also take the initiative to identify and solve deadlocks.
There are three ways of doing this;
Traceflag 1222
Traceflag 1222 and 1204 are the oldest ways through which database administrators could identify and resolve SQL deadlocks. They used this method to get the deadlock graph and ensure that it is captured in their error logs.
However, this method is not very effective. In case a deadlock occurs when traceflag 1222 is not enabled, you have to enable it and then wait to see if the deadlock will occur again.
Unfortunately, SQL Server provides a lot of information in the error log, meaning that database administrators using this method have to do a lot of work manually. This method is recommended only for those who do not have any other option for resolving deadlocks.
Extended Events
SQL Server 2008 and later versions come with an extended events session whose sole purpose is to obtain the deadlock graph automatically.
Once you get a 1205 error, which is the deadlock error discussed above, you can retrieve the deadlock graph to identify and solve the deadlock. Even though this method is better than the one discussed above, database administrators still have to go through XML graphs.
SQL Monitor
This is the best way of monitoring and solving deadlocks in SQL Server. Whenever there is a deadlock, the SQL monitor detects it automatically. It then raises a message alert that is sent to the database administrator’s email address.
With these details, the database administrator can understand what caused the deadlock without having to go through XML graphs manually. In addition, it gets easier for them to resolve deadlocks.
In conclusion, apart from deadlocks, DBA’s should look out for computer worms such as the SQL Slammer which exploited a security hole in Microsoft SQL Server in 2003 as a distribution strategy.
Deadlocks can affect the entire operations of an application. It is, therefore, important for database administrators to make sure that deadlocks are not only resolved but also prevented.
