Deadlocks occur when two or more processes or transactions block each other from progressing because they have locked a database resource that another transaction needs. In SQL Server, deadlocks are handled by terminating and rolling back transactions that started after the first transaction. As a DBA, I have frequently encountered performance problems, often caused by poorly written code or the absence of indexes, leading to what we commonly refer to as “Deadlocks.” These deadlocks can significantly impact user experience, and their frequency can exacerbate the consequences.This article is the first in a series focusing on deadlock issues and reporting. By the end of this article, you will understand how deadlocks can occur and how we can obtain detailed information about them to diagnose and take appropriate actions. These actions can reduce the occurrence frequency or even eliminate deadlock situations altogether. In the following sections, we will delve into the concept of deadlocks and their occurrence through a practical T-SQL example. Additionally, we will explore SQL Server’s tools designed to detect and provide the necessary information to identify and resolve these deadlocks, although finding a solution may prove more challenging than anticipated.

 

  • To understand deadlocks, it’s helpful to first understand “blocking.” Blocking occurs when one user holds a lock on a resource, and another user wants to access that same resource but must wait until the lock is released. In this case, the waiting user is blocked by the user holding the lock. This can be visualized as a “tree” of blocked sessions, where one session is blocking another, and that blocked session might be blocking yet another, and so on.
  • In a deadlock situation, there is no identifiable “head blocker” because all the involved sessions are blocking each other. Unlike blocking, where one session is responsible for the blockage, a deadlock involves multiple sessions in a circular chain of dependencies. Fortunately, most database management systems, like SQL Server, have a mechanism to detect deadlocks. The system’s deadlock monitor periodically checks for deadlock situations and selects one of the sessions involved as the “victim” to be terminated.
  • This termination releases the locks held by the victim, allowing the other sessions to proceed. However, the terminated session will receive an error and must be rerun. The deadlock monitor prioritizes selecting the least expensive transaction (in terms of resource consumption) as the deadlock victim. Overall, deadlocks are situations to be avoided in database systems as they can lead to significant disruptions and issues in processing transactions. Proper management of locks and transactions is crucial to prevent deadlocks from occurring.

How to check Deadlocks in SQL Server?

  • SQL Server has a Lock Monitor thread that regularly checks for deadlocks in the database. By default, it runs every 5 seconds. If a deadlock is found, the detection interval can be reduced to as low as 100 milliseconds, depending on how frequently deadlocks occur. If no deadlocks are detected, the interval returns to 5 seconds.
  • When a deadlock is detected, SQL Server takes action to resolve it. It chooses one of the involved processes or transactions as the “deadlock victim.” This chosen transaction is then rolled back, resulting in an error (error code 1205) returned to the application. The rollback releases all the locks held by the transaction, allowing other transactions to proceed without being blocked.
  • In SQL Server, users can also influence which transaction becomes the deadlock victim. This can be done by setting the deadlock priority using the “SET DEADLOCK_PRIORITY” statement. By specifying the priority for transactions, the one with the lowest deadlock priority will be selected as the deadlock victim in case of a deadlock situation.

 

Recovery from Deadlock: How to Handle Deadlocks

Manual Intervention:When a deadlock is detected, one option is to inform a person (operator) and let them handle the situation manually. This means the person will decide what to do to resolve the deadlock. However, this approach can be time-consuming and might not work well in large systems.

Automatic Recovery:

Another way to deal with deadlocks is to enable the system to handle them automatically. There are two main strategies for automatic recovery.

a) Process Termination:

Abort all deadlocked processes: This breaks the deadlock cycle, but it can be costly because the processes that were aborted may have done a lot of work, leading to partial data loss that needs to be recomputed later. Abort one process at a time: Instead of aborting all deadlocked processes at once, this strategy involves selectively aborting one process at a time until the deadlock is resolved. However, this approach incurs overhead as a deadlock-detection algorithm needs to be invoked after each process termination to check if any processes are still deadlocked. Factors considered for choosing the termination order include the process’s priority, completion time and progress made so far, resources consumed and required by the process, number of processes to be terminated, and the process type (interactive or batch).

b) Resource Preemption:

Selecting a victim: Resource preemption means choosing which resources and processes to preempt to break the deadlock. The goal is to minimize the overall cost of recovery. Factors considered for victim selection may include the number of resources held by a deadlocked process and the amount of time the process has consumed.
Rollback: If a resource is taken away from a process, the process can’t continue as it lacks that resource. So, rolling back the process to a safe state and restarting it is a common approach. Determining a safe state can be challenging, leading to total rollback, where the process is aborted and restarted from scratch.
Starvation prevention: To prevent resource starvation, it’s important to avoid always choosing the same process as the victim. If victim selection is solely based on cost factors, one process might repeatedly lose its resources and never finish its task. Limiting the number of times a process can be chosen as a victim, including the number of rollbacks in the cost factor, helps address this issue.

Deadlock ignorance:

If a deadlock is very rare, some systems adopt a “let it happen” approach and reboot the system. This means the system doesn’t actively try to resolve the deadlock. This method is called the “ostrich algorithm” for deadlock ignorance.
In Deadlock Ignorance, the performance is better than the previous two methods, but there can be a risk of data correctness.

Safe State: A safe state is a state in which no deadlock exists. It can be achieved under two conditions:
If a process needs a resource that is currently unavailable, it can wait until the resource is released by another process that already has it. If such a sequence of waiting exists, the state is safe.

 

Know more about Deadlock click hereĀ 

Leave a Reply

Your email address will not be published. Required fields are marked *