In layman's information about deadlock issues in SQL Server

In layman's information about deadlock issues in SQL Server Blog 2013-02-12 13:44 share to: I want to throw up the groove

The essence of deadlock is a stalemate, which is caused by the contention of multiple subjects for resources. Understanding deadlocks first requires an understanding of the relevant concepts involved in deadlocks.

　　Some basic knowledge

A better way to understand deadlocks in SQL Server is to understand deadlocks from larger faces by analogy. For example, a classic example is the requisition of a car (subject) for a road (Resource), as shown in 1.

Figure 1: Intuitive understanding of deadlocks

In the example in Figure 1, each team has a road, but they all need the other one to occupy another road, so blocking each other, no one can move forward, resulting in a deadlock. As can be seen from this simple example, there are four necessary conditions for a deadlock to occur, as follows:

　　1) Mutually exclusive condition:

The subject is exclusive to the resource, and in Figure 1 each car lane can only run a fleet of cars and not run the second team.

　　2) Request and wait conditions:

means that the principal has maintained at least one resource, but has proposed a new resource request, and the resource has been occupied by other principals, at this time the request body is blocked, but the other resources that they have obtained remain. In Figure 1, each car already occupies one lane and wants to get another lane occupied by other convoys, causing obstruction.

　　3) Conditions of non-deprivation

Refers to the resources that the subject has obtained cannot be released until the target has been completed. In Figure 1, the goal is that the car can pass through the driveway, and no deprivation means that the motorcade does not give up its occupied driveway until this goal is fulfilled.

　　4) Loop Wait condition

In the event of a deadlock, there must be a subject-a circular chain of resources, that is, the P0 of the principal set {p0,p1,p2,,pn} is waiting for a resource to be occupied by a P1; P1 is waiting for P2 to occupy the resources, ..., PN is waiting for resources that have been consumed by P0. As can be seen in Figure 1, the four-lane and four-team cars are just signs of the loop waiting conditions, the convoy 1 would like to obtain a fleet of 2 occupy the driveway, the team 2 would like to obtain a fleet 3 occupy the driveway, the team 3 would like to obtain a fleet of 4 occupy the driveway, Convoy 4 in turn, would like to obtain a

　　Definition of deadlock in process

Let's go back to the world of computers by narrowing down the range of deadlocks. In computers, this abstract word of the subject is replaced by more specific processes, and resources are reduced to the resources used by the computer. In a computer, deadlocks are caused by blocking. So in the beginning, I would like to briefly introduce some of the status of the process, if interested, you can also see my previous article: Http://www.cnblogs.com/CareySon/archive/2012/05/04/ProcessAndThread.html.

Simply put, the process is the smallest unit of organization resources, the multi-channel operating system allows concurrent, each process is like the car shown in Figure 1, need to go forward, in the process of moving forward, the need for various resources and CPU, Figure 2 is not to consider the creation of destruction and other states, a simple overview of the process of several states.

Figure 2: Several states of a process

Many resources can be shared, such as memory. But for resources such as printers, it needs to be exclusive. A few of the states in Figure 2 simply understand that when the process does not have the required resources, such as waiting for the IO, waiting for the printer, then it is blocking state. When the process obtains these resources, it can become ready and becomes the execution state when the process in the ready state gets the CPU. In the process of execution, the CPU is stripped and continues to become ready, or when additional resources are needed, it continues to become blocked. to the reciprocating.

In the operating system, some resources can be an inalienable resource, such as a printer, and when a printer is occupied by one process, another process is blocked. There is also a class of resources to focus on, such resources are temporary resources, such as the signal volume generated by the process, messages, messages within the buffer, multiple processes or threads access to such resources more likely to cause deadlocks. The deadlock generated in SQL Server is actually caused by this kind of resource.

When two or more processes have the current resource and require additional resources, a deadlock occurs when the four conditions of the deadlock described above are met.

　　Definition of deadlock in SQL Server

In SQL Server, blocking more is caused by the isolation between implementing concurrency. In order to make the effect of the operation of the concurrent connection reach a certain expectation, the resource is artificially locked (the lock essence can be regarded as a sign bit). When a connection operates on a particular resource, another connection is blocked for the same resource at the same time (of course, it is related to the compatibility between locks, and the more in-depth discussion about locks is beyond the scope of this article, and I'll look at another article on this: T-SQL query Advanced-Understanding SQL Locks in the server), blocking is a necessary condition for deadlock generation.

Below, we look at deadlocks in a simple example.

First of all, to have a deadlock, be sure to meet the previous mention of the deadlock occurred in the four necessary conditions, figure 3 can be clearly seen how these two connections (SPID52 and SPID55) How to meet these four conditions.

Figure 3. Example of a deadlock

　　Lock Monitor

As you can see in the deadlock in Figure 3, SQL Server does not deadlock the deadlock, but instead periodically detects it through a thread called Lock monitor (by default, 5 seconds). When a deadlock is found, one of the SPID's resources is stripped away to allow the other SPID to execute, specifically depriving the SPID of the following two factors:

1. The priority of the deadlock.

2. In the case of the same deadlock priority, a transaction with little overhead will be stripped, depending on the cost

Below, or according to the example in Figure 3, we set the deadlock priority so that the left transaction is stripped back, and 4 is shown.

Figure 4: After setting the deadlock priority, the low-priority SPID is stripped

　　Detection of deadlocks in SQL Server

First of all, in a multi-concurrency environment, deadlock is unavoidable, can only be as far as possible through a reasonable database design, good indexes, appropriate query statements and isolation level to minimize. Therefore, the purpose of detecting deadlocks is to know where deadlocks can occur, and to optimize the query/index/isolation level to reduce the likelihood of deadlocks by analyzing the detected deadlocks.

There are two ways to look at deadlocks, one is through the trace on the server side, and the other through SQL Profiler, first let's look at the trace to catch a deadlock.

　　Look at the deadlock through trace.

When a deadlock occurs, the deadlock information can be uploaded to the log via the server trace. In the era of SQL Server 2000, only trace flag 1204来 can be turned on, because trace flag 1204 does not provide an XML deadlock graph, which is replaced by trace flag 1222 in SQL Server 2005 and later.

To turn on trace flag 1222 for all sessions on the server. Can be passed as shown in code 1.

DBCC TRACEON (1222,-1)

Code 1. Turn on 1222 This trace Flag for all session

In addition to code 1, you can also –t1222 the add-on parameter before starting the SQL Server instance. This is no longer a detail.

At this point, when a deadlock occurs, you can see the relevant record from the log, as shown in 5.

Figure 5: Post-deadlock record

Check the look dead lock through the profiler.

Another method is to open the profiler to capture, the profiler captures the deadlock information content is more intuitive, profiler settings 6.

Figure 6. Setting of lock diagram in Profiler

The captured deadlock is shown in Figure 7.

Figure 7: Deadlock diagram

With this deadlock diagram, you can see more visually the bodies and resources that are generated by the deadlock, and when you move the mouse over the body, you can also display the statement that caused the deadlock. The deadlock victim process will be hit by x number.

The deadlock diagram above can also see the resources that caused the deadlock.

Some scenarios in SQL Server that generate deadlocks

　　Deadlock generated by bookmark lookup

This type of deadlock is caused by a deadlock in bookmark lookup and update data. Simply put, because the UPDATE statement generates an X lock on the base table, and then needs to update the index on the table, and the index on the table is just being looked up by another connection, and the S lock is added, and then the bookmark lookup goes to the base table with the X-lock data for bookmark lookup, which forms a deadlock, This concept can be seen from Figure 8.

Figure 8: Deadlock created by Bookmark Lookup

This deadlock can reduce the probability of this type of deadlock occurring by reducing the bookmark lookup through the Include column.

　　Deadlock generated by a foreign key

This type of deadlock occurs because of a foreign key constraint. When the primary table (that is, the primary key is the table from the Foreign key table) updates the data, you need to view the foreign KEY constraint from the table to determine the foreign key column from the table. An x lock is added to the main table, but this does not prevent the same time, and another SPID adds the modified primary table key to the table, in order to solve this problem, SQL Server uses a range lock when making such an update, which is only available when the isolation level is serialized. So at this point, although the isolation level may be the default committed read, the behavior is serialized. This will most likely lead to deadlocks.

One solution is to add an index to the foreign key column so that the range lock is added to the index instead of the table itself. This reduces the probability of a deadlock occurring.

Deadlock due to improper propulsion sequence

This is also the cause of the deadlock in Figure 3. A deadlock loop is raised when multiple transactions are improperly used in the resource order. The workaround is to use the same order of resources as possible. This is one of the most common cases of deadlock problems.

　　How to reduce deadlocks

The above briefly describes some of the scenarios in SQL Server that generate deadlocks. Let's look at how to reduce deadlocks from a broader perspective.

In the operating system, the principle of concurrent process reduction of deadlocks can also be applied to SQL Server. In the operating system, the method for handling deadlocks is as follows:

　　1) Prevent deadlocks.

This is a simpler and more intuitive approach to proactive prevention. The method is to prevent deadlocks by setting certain constraints to destroy one or more of the four necessary conditions that produce deadlocks. The prevention of deadlocks is a more easily implemented method and has been widely used. However, due to the often restrictive conditions imposed, the system resource utilization and system throughput can be reduced.

　　2) Avoid deadlocks.

This method is also a pre-prevention strategy, but it does not have to take a variety of restrictive measures to destroy the four necessary conditions for deadlocks, but in the dynamic allocation process of resources, in a way to prevent the system from entering the unsafe state, so as to avoid the deadlock.

　　3) Detection of deadlocks.

This method does not need to take any restrictive measures beforehand, nor does it have to check if the system has entered the unsafe zone, which allows the system to deadlock during operation. However, the detection mechanism set up by the system can detect the occurrence of the deadlock in time, and determine the process and resources related to the deadlock, then take appropriate measures to remove the deadlock from the system.

　　4) Unlock the deadlock.

This is a measure to match the detection of deadlocks. When a deadlock has been detected in the system, the process must be freed from the deadlock state. A common practice is to undo or suspend some processes in order to reclaim some resources, and then assign those resources to a process that is already in a blocked state and make it ready to continue running. Deadlock detection and lifting measures, it is possible to make the system to achieve better resource utilization and throughput, but the implementation of the most difficult.

By the way above 4 handles deadlocks, where detecting deadlocks and unlocking deadlocks is what lock monitor does, as a DBA or database developer, dealing with deadlocks should be put in prevention and avoid deadlocks.

　　Prevent deadlocks

Preventing deadlocks is destroying one or more of the four necessary conditions so that they do not form a deadlock. There are several ways

　　1) break mutually exclusive conditions

There are strict restrictions on breaking mutex conditions, and in SQL Server, if dirty reads are allowed on the business logic, you can change the isolation level to read UNCOMMITTED or use index hints. This allows the read to not add S lock, thus avoiding the other query with the S lock incompatible with the lock mutex, and thus reduce the probability of deadlock occurrence.

　　2) Destroy requests and wait conditions

This is not disruptive because the transaction is atomic, because the solution is to minimize the length of the transaction, and the faster the transaction executes the better. This also reduces the probability that a deadlock will occur.

　　3) Destruction of non-deprivation conditions

Because of the atomicity and consistency of the transaction, the conditions of deprivation are equally non-destructive. But we can consider it by increasing resources and reducing resource consumption by two angles.

Add resources: For example, by creating a nonclustered index, which makes additional resources available, queries often stop asking for lock base tables, instead of locking the nonclustered indexes, which is better if the index can "overwrite (Cover)" queries. Therefore, the index include column does not only reduce bookmark lookups to improve performance, but also reduces deadlocks. Adding resources can also be done through row versioning after SQL Server 2005, but this is not recommended and is no longer discussed in detail here.

Reduce Resource Usage: for example, when querying, you can use select Col1,col2 this way, do not use SELECT *. This may lead to unnecessary bookmark lookups

　Avoid deadlocks

The avoidance of deadlock is limited by the resources, so that the main contention resources do not form a loop. For example, the typical banker algorithm, in the case of limited resources, in the case of not causing the cash flow break, as much as possible in a certain order to allocate resources.

So the key to avoiding deadlocks is the "order". In SQL Server, try to keep the query consistent with the order in which the resources are used. than 3 is a typical deadlock that is caused by requesting resources in order. Suppose the order of Figure 3 is changed to the order shown in Figure 9, which is not a deadlock, in turn, the deadlock will become a wait.

Figure 9. In order, deadlock to wait

　　Processing of deadlocks in SQL Server

Now that the deadlock is unavoidable, there is a mechanism for dealing with deadlocks. Can imagine if your program is an e-commerce site, due to deadlock caused the user's generated order is rollback ...

So the processing of deadlocks can be done on two levels in SQL Server

　　Processing deadlocks at the SQL Server level

The first thing to know is that the error code of the deadlock in SQL Server is 1205, because the deadlock is caused by blocking, and the blocking time is often not long, the index can be retried several times to deal with the deadlock, the typical code as shown in code 2.

--Retry Count DECLARE @retry INT SET @retry = 3 while (@retry > 0) BEGIN BEGIN TRY --Here is the business code --The transaction succeeds and the retry count is changed to SET @retry = 0 END TRY BEGIN CATCH --if it is a deadlock, retry IF (error_number () = 1205) SET @retry = @retry ELSE BEGIN --If it is another error, log to the logs, etc... END END CATCH END

Code 2: Processing deadlocks at the SQL Server level

Handling deadlocks at the program level

And the way the deadlock is handled in SQL Server is the same as the error code, and the following is how C # handles deadlocks, as shown in code 3.

int retry = 3; while (Retry > 0) { Try { Code to execute the SQL statement Change the number of retries to 0 retry = 0; } catch (SqlException e) { If it's a deadlock, try again after 0.5S. if (e.number==1205) { System.Threading.Thread.Sleep (500); retry--; } Other errors .... Else { Throw } } }

Code Listing 3. Dead-Lock processing C # code

　　Summarize

This article describes the concept of deadlocks, the four necessary conditions for generating deadlocks, how deadlocks are handled, and how to detect and handle deadlocks in SQL Server. Deadlocks are caused by blocking, and it is necessary to understand the basic concepts of this part of the deadlock.

In layman's information about deadlock issues in SQL Server