SQL Server deadlock

Introduction

The essence of a deadlock is a deadlock that is caused by the contention of multiple subjects for resources. To understand a deadlock, you must first understand the concepts involved in the deadlock.

 

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 1.

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.

Figure 2. Several statuses of processes

 

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.

Figure 3. deadlock example

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, we set the deadlock priority so that the transaction on the left is denied for rollback, as shown in figure 4.

Figure 4. The spid with a lower priority is denied after the deadlock priority is set

 

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.

 

Check the deadlock through trace

When a deadlock occurs, the deadlock information can be uploaded to the log through the trace on the server. In the SQL Server 2000 era, trace flag 1204 can only be enabled. Because trace flag 1204 does not provide XML deadlock graphs, it is replaced by trace flag 2005 in SQL Server 1222 and later versions.

To enable trace flag 1222 for all sessions on the server. See Code 1.

DBCC TRACEON(1222,-1)

Code 1. Enable trace flag 1222 for all sessions

In addition to code 1, you can add the startup parameter-t1222 before starting the SQL server instance. I will not go into detail here.

In this case, when a deadlock occurs, you can see the relevant records from the log, as shown in Figure 5.

Figure 5. Records after deadlock

 

View deadlocks through profiler

Another method is to enable profiler to capture the deadlock information captured by Profiler, which is more intuitive, as shown in profiler setting 6.

Figure 6. settings of the deadlock graph in profiler

 

Figure 7 shows the deadlock.

Figure 7. deadlock Diagram

 

The deadlock graph shows the subjects and resources generated by the deadlock more intuitively. When you move the cursor over the subject, the statements that cause the deadlock can also be displayed. The victim process of the deadlock will be numbered X.

The deadlock figure above also shows the resource that causes the deadlock.

 

SQL Server lifecycle locks

Deadlock caused by bookmarked search

The reason for this type of deadlock is the deadlock between bookmarking and data update. Simply put, the update statement generates an X lock for the basic table, and then the index on the table needs to be updated. The index on the table is searched by another connection and the S lock is applied, in this case, a bookmarksearch is generated, and the data with the X lock is added to the basic table for the bookmarksearch. A deadlock is formed. This concept can be seen in figure 8.

Figure 8. deadlock caused by bookmarked search

This type of deadlock can be reduced by using the include column, thus reducing the probability of this type of deadlock.

 

Deadlock caused by foreign keys

The reason for this type of deadlock is the foreign key constraint. When the primary table (that is, the table where the primary key is from the foreign key) updates data, you need to view the slave table to ensure that the foreign key columns of the slave Table Meet the foreign key constraints. At this time, the X lock will be applied to the master table, but this does not prevent the same time. Another spid will add the modified primary key to the slave table. To solve this problem, SQL Server uses the range lock when performing such updates. This lock is available only when the isolation level is serialized. Therefore, although the isolation level may be the default committed read, but the behavior is serialized. This may lead to deadlocks.

One solution is to add an index to the foreign key column so that the range lock is applied to the index, rather than the table itself. This reduces the probability of deadlock.

Deadlocks caused by improper promotion order

This is also the cause of the deadlock in figure 3. A deadlock loop is formed when multiple transactions use resources improperly. The solution is to use resources in the same order as possible. This is also the most serious case of deadlocks.

How to reduce deadlocks

The above briefly describes some cases of SQL Server's Life and Death locks. Next we will look at how to reduce deadlocks from a broader perspective.

In the operating system, the principle of concurrent process deadlock reduction can also be applied to SQL Server. The operating system can handle deadlocks as follows:

1)Prevent deadlocks.

This is a relatively simple and intuitive method of pre-prevention. The method is to set certain conditions to destroy one or more of the four conditions necessary to generate a deadlock to prevent deadlock. Deadlock Prevention is an easy-to-implement method and has been widely used. However, due to the strict restrictions imposed, system resource utilization and system throughput may be reduced.

2)Avoid deadlocks.

This method is also a pre-prevention policy, but it does not need to take various restrictions in advance to damage the four necessary conditions for deadlock, but in the process of dynamic resource allocation, some method is used to prevent the system from entering an insecure state, so as to avoid deadlocks.

3)Detects deadlocks.

This method does not have to take any restrictive measures in advance, nor does it have to check whether the system has entered the unsafe zone. This method allows the system to issue a life-and-death lock during operation. However, through the detection mechanism set up by the system, the deadlock can be detected in a timely manner, and the process and resources related to the deadlock can be accurately identified, and then appropriate measures can be taken, clear deadlocks from the system.

4)Remove the deadlock.

This is a complementary measure to the Deadlock Detection. When a dead or dead lock is detected in the system, the process must be freed from the deadlock state. A common implementation method is to undo or suspend some processes to recycle some resources, and then allocate these resources to the blocked processes so that they are ready to continue running. Deadlock Detection and removal measures may make the system obtain better resource utilization and throughput, but the implementation is also the most difficult.

 

According to the deadlock handling method in the above 4, it is about lock monitor to detect and remove deadlocks. As a DBA or database developer, it is necessary to prevent and avoid deadlocks when handling deadlocks.

 

Deadlock Prevention

Deadlock Prevention means to destroy one or several of the four necessary conditions so that they cannot form a deadlock. There are several methods

1) Damage mutex Conditions

Violation of mutex conditions has strict restrictions. In SQL Server, if the business logic permits dirty read, you can change the isolation level to uncommitted read or use the index prompt. In this way, the S lock is not used for reading, so as to avoid the mutual exclusion between the S lock incompatible with other queries, and thus reduce the probability of deadlock.

2) request and wait conditions are damaged

This is inevitable because the transaction is atomic, because the solution is to minimize the length of the transaction, the faster the transaction execution, the better. This can also reduce the probability of deadlocks.

3) damage the non-deprivation conditions

Given the atomicity and consistency of transactions, non-deprivation conditions cannot be damaged. However, we can consider adding resources and reducing resource occupation.

Add resources: for example, if you create a non-clustered index and have additional resources, you will no longer need to lock the basic table and then lock the non-clustered index, if the index can be "covered", it is better. Therefore, the index include column not only reduces the number of bookmarks to improve performance, but also reduces deadlocks. You can use row version control after SQL Server 2005 to add resources. However, this method is not recommended and will not be discussed in detail here.

Reduce resource usage: for example, you can use select col1 or col2 to query data, so do not use select *. This may lead to unnecessary bookmarked searches.

 

Avoid deadlocks

To avoid deadlocks, the subject cannot compete for resources under limited resources. For example, the typical Banker algorithm is to allocate as many resources as possible in a certain order without causing a cash flow break when resources are limited.

The key to avoiding deadlocks is "sequence ". In SQL Server, make sure that the query order of resources is consistent. 3 is a typical deadlock caused by not requesting resources in order. Assume that the order in Figure 3 is changed to the order shown in Figure 9, which is not a deadlock. Instead, the deadlock changes to waiting.

Figure 9. In order, the deadlock is converted to waiting

How to handle deadlocks in SQL Server

Since deadlocks cannot be avoided, there must be a mechanism for handling deadlocks. Imagine that if your program is an e-commerce website, the order generated by the user will be rolled back due to a deadlock...

Therefore, the process of deadlock can be performed on SQL server at two levels.

 

Handle deadlocks at the SQL server level

First, you must know that the deadlock error code in SQL Server is 1205, because the deadlock is caused by blocking, and the blocking time is often not long, indexes can be retried several times to handle deadlocks. The typical code is shown in Code 2.

-- Number of retries declare @ retry intset @ retry = 3 while (@ retry> 0) Begin begin try -- here is the Business Code -- transaction succeeded, change the number of retries to set @ retry = 0 end try begin catch -- if a deadlock occurs, retry if (error_number () = 1205) set @ retry = @ retry else begin -- if there are other errors, logs will be recorded .. end end catch end

Code 2. Handle deadlocks at the SQL server layer

 

Process deadlocks at the program layer

Similar to the way in SQL Server to handle deadlocks, it is also determined by the error code. below is the way C # handles deadlocks, as shown in code 3.

Int retry = 3; while (retry> 0) {try {// code for executing the SQL statement // change the number of retries to 0 retry = 0;} catch (sqlexception E) {// If a deadlock occurs, retry if (E. number == 1205) {system. threading. thread. sleep (500); retry --;} // other errors .... else {Throw ;}}}

Code 3. C # code for deadlock handling

 

Summary

This article describes the concept of deadlock, the four necessary conditions for deadlock generation, the handling method of deadlock and how to detect and avoid and handle deadlocks in SQL Server. Deadlock is caused by blocking. Understanding this basic concept is essential for troubleshooting deadlocks.