Introduction to deadlocks in SQL Server (1/3)

Basic knowledge
To understand the deadlock in SQL Server, a better way is to understand the deadlock from a larger perspective through analogy. For example, a typical example is the requisition of vehicles (subjects) for roads (resources), as shown in Figure 1.

Intuitive understanding of deadlocks

In the example in figure 1, each team of cars occupies one road, but each team of cars needs another road. Therefore, no one can move forward because of mutual congestion, this causes a deadlock. From this simple example, we can see that a deadlock requires four necessary conditions:

1) mutex conditions:

The subject is exclusive to resources. In figure 1, each vehicle track can only run a fleet of cars, but cannot run a second fleet.

2) request and waiting conditions:

This means that the subject has maintained at least one resource, but has made new resource requests, and the resource has been occupied by other subjects. In this case, the request subject is blocked, however, you cannot release other resources that you have obtained. In Figure 1, each fleet of cars already occupies one lane and wants another lane owned by another fleet, causing congestion.

3) non-deprivation conditions

It means that the resources that the subject has obtained cannot be released until the target is completed. In Figure 1, the goal refers to the vehicle passing through the lane, and the non-deprivation refers to the fact that the fleet will not let out the occupied lane before the goal is achieved.

4) loop wait conditions

When a deadlock occurs, there must be a subject-a circular chain of resources, that is, the body set {P0, P1, P2 ,···, p0 in Pn} is waiting for resources occupied by P1; P1 is waiting for resources occupied by P2 ,......, Pn is waiting for resources occupied by P0. As shown in Figure 1, we can see that four lanes and four cars are just waiting for the symbol loop. Team 1 wants to get the lanes occupied by Team 2 and Team 2 wants to get the lanes occupied by Team 3, team 3 hopes to get the lanes occupied by Team 4, and Team 4 in turn hopes to get the lanes occupied by Team 1 to form a loop.

Definitions of deadlocks in processes
Let's narrow down the deadlock range and return to the computer world. In a computer, the abstract word of the subject is replaced by a more specific process, and the resources are reduced to the resources used by the computer. In a computer, deadlocks are caused by blocking. So at the beginning, I would like to briefly introduce several states of the process, if you are interested, you can also refer to my previous article: http://www.cnblogs.com/CareySon/archive/2012/05/04/ProcessAndThread.html.

Simply put, a process is the smallest unit of organizational resources. Concurrent operations are allowed in multiple operating systems. Every process is like a car shown in Figure 1. It needs to move forward, various resources and CPUs are required. Figure 2 provides a brief overview of several states of the process, regardless of the creation or destruction status.

   

. Several statuses of the process

 

Many resources can be shared, such as memory. However, for printers and other resources, they need to be exclusive. Several statuses in figure 2 are blocked when a process does not have the required resources, such as waiting for IO or printer. When a process acquires these resources, it can change to the ready state. When a ready process acquires the CPU again, it changes to the execution state. In the execution process, if the CPU is denied, it will continue to become ready, or when other resources are required, it will continue to become blocked. In this way.

In the operating system, some resources can be uneconomical. For example, when a printer is occupied by one process, another process is blocked. Another type of resources should be emphasized. Such resources are temporary resources, such as semaphores, messages, and messages in the buffer zone, when multiple processes or threads access such resources, deadlock is more likely to occur. The deadlock generated in SQL Server is actually caused by such resources.

When two or more processes have the current resources and need additional resources and meet the four conditions described above, a deadlock occurs.

Definitions of deadlocks in SQL Server
In SQL Server, blocking is more caused by isolation between concurrency. In order to make the impact between the operations made by the concurrent connection reach a certain expectation, the resource is manually locked (the lock can be regarded as a flag in essence ). When a connection operates on a specific resource, operations on the same resource at the same time by the other connection will be blocked (of course, this is related to the compatibility between locks, A more in-depth discussion of the lock is beyond the scope of this article. For this part of the content, see my another article: T-SQL query advanced-understanding the lock in SQL Server ), blocking is a necessary condition for deadlock.

Next, let's look at the deadlock through a simple example.

First of all, to have a deadlock, you must meet the four necessary conditions for the deadlock mentioned above. Figure 3 clearly shows the two connections (SPID52 and SPID55) how to meet these four conditions.

  

Lock Monitor
After the deadlock in figure 3, we can see that SQL Server does not stop the deadlock, but regularly checks the deadlock through a thread called Lock Monitor (the default value is 5 seconds ). When a deadlock is detected, one SPID will be deprived of the resources occupied by the other SPID so that the other SPID can be executed. The following two factors are involved in the deprivation of SPID:

1. Priority of the deadlock.

2. When the deadlock priority is the same, transactions with low overhead will be deprived.

 

As shown in figure 3, the deadlock priority is set to deprive the transaction on the left of rollback, as shown in figure 4.

   

. After the priority of a deadlock is set, SPID with lower priority is denied.

 

SQL Server deadlock detection
First of all, we need to understand that in a multi-concurrency environment, deadlock is inevitable. We can only try to use reasonable database design and good indexing, appropriate query statements and isolation levels should be minimized. Therefore, the purpose of deadlock detection is to know where a deadlock may occur. After analyzing the detected deadlock, optimize the query, index, and isolation levels as much as possible to reduce the possibility of deadlock.

There are two ways to view deadlocks: one is through the Trace on the server side, and the other is through SQL Profiler. First, let's look at the deadlock through Trace.

 

Homepage 1 2 3 Last page