SQL Server deadlock notes (GO)

The so-called deadlock, mainly because process B to access the resources of process A, and process a for a variety of reasons, do not release the resources they occupy, so the database will always be in a blocking state.

Four Prerequisites:

Prerequisites: mutual exclusion, a resource, at the same point in time, can only be accessed by one process.

Deadlock analysis: When the task TA in the use of resource Ra, resulting in unforeseen problems, which unexpectedly stopped execution, but has not released the RA, so the cause of the deadlock on the resource RA, this is the most fundamental reason for the deadlock, and other reasons are basically based on this.

Prerequisites: resource requests and retention, each process can request access to another resource while using one resource.

Deadlock analysis: Since each process can request access to multiple resources, when the task TA uses resource RA, request access to the resource RB that is being used by the task TB, and at this time, TB to apply the RA is being used by the TA, so it caused the case of each other waiting for the deadlock.

Prerequisites: non-deprivation of resources, the process cannot forcibly deprive resources that are being used by other processes.

Deadlock Analysis: When other circumstances arise, it is precisely because of this condition that causes the process to wait indefinitely.

Prerequisites: Cycle-by-resource waiting

Deadlock analysis: TA applies ra, while TB is using RA, and TB applies RB, but TC is using RB, at this time TC applies RC, and RC is used by TA, resulting in deadlock.

Resources that may have deadlocks

1. RID, single row in heap
2. Key, keys in the index, row locks
3. PAG, page
4. EXT, District structure
5. HOBT, heap or B-tree
6. TAB, table, including data and indexes
7. file, database files
8. Apps, application-specific resources
9. METADATA, meta data
10. Allocation_unit, allocation unit
11. DB, entire database

Ways to avoid deadlocks

Because the deadlock is due to the above four necessary conditions of the joint action produced, so according to the general idea, as long as can break one of them, can effectively avoid the production of deadlocks.

The following are the processing that can be made for the four prerequisites:

1. Allows concurrent access to resources.
2. Allow resource deprivation for the process.
3. The resources required for the process to be submitted once.
4. The orderly allocation of resources, that is, access to resources in the same order.

Other methods (the primary goal of these methods is to minimize the time spent on resources, thereby reducing the chance of deadlock generation):

1. Avoid user interaction in a transaction.
2. Keep the transaction short and in a batch.
3. Use lower-level isolation levels.
4. Use the isolation level based on row versioning.
5. Using a bound connection and binding multiple sessions to a transaction can effectively reduce the number of transactions.

Transfer from http://www.cnblogs.com/sitemanager/archive/2012/03/26/2417638.html

Main reference articles:

1. http://www.cnblogs.com/happyhippy/archive/2008/11/14/1333922.html"Highly Recommended"
2. http://msdn.microsoft.com/zh-cn/library/ms177433.aspx"msdn"
3. http://www.cnblogs.com/ching/archive/2012/02/01/2334659.html"is very detailed in another article"

SQL Server deadlock notes (GO)