SQL Server deadlock notes)

The so-called deadlock is mainly because process B needs to access the resources of process A, and process A does not release the resources it occupies for various reasons, therefore, the database will remain in the blocking status. Four necessary conditions: mutex, a resource, at the same time point, can only be accessed by one process. Deadlock analysis: When task TA is using resource RA

The so-called deadlock is mainly because process B needs to access the resources of process A, and process A does not release the resources it occupies for various reasons, therefore, the database will remain in the blocking status. Four necessary conditions: mutex, a resource, at the same time point, can only be accessed by one process. Deadlock analysis: When task TA is using resource RA

The so-called deadlock is mainly because process B needs to access the resources of process A, and process A does not release the resources it occupies for various reasons, therefore, the database will remain in the blocking status.

Four necessary conditions:

Prerequisites:A resource is mutually exclusive and can only be accessed by one process at a time point.

Deadlock analysis: when the task TA is using resource RA, an unknown problem occurs, and thus the execution is unexpectedly stopped, but RA has not been released yet, the deadlock on the resource RA is also the root cause of the deadlock. Other causes are basically based on this.

Prerequisites:Resource request and persistence. Each process can request access to another resource while using one resource.

Deadlock analysis: each process can request to access multiple resources. Therefore, when task TA uses resource RA, it requests to access the resource RB that is being used by task TB, to apply for a RA that is being used by the TA, TBS will wait for each other and cause a deadlock.

Prerequisites:The process cannot forcibly deprive the resources that are being used by other processes.

Deadlock analysis: when other situations occur, it is precisely because of this condition that the process will not wait.

Prerequisites:Resource cyclic wait

Deadlock analysis: TA applies for RA, while TB is using RA, while TB applies for RB, But TC is using RB. At this time, TC applies for RC, and RC is being used by TA, resulting in a deadlock.

Resources that may cause deadlocks

1. RID, a single row in the heap
2. KEY, KEY in the index, row lock
3. PAG, page
4. EXT, partition structure
5. HOBT, heap or B tree
6. TAB, table, including data and Indexes
7. File, database File
8. APP, dedicated application resources
9. METADATA, METADATA
10. Allocation_Unit, allocation Unit
11. DB, the entire database

Methods To avoid deadlocks

Since deadlocks are generated by the combination of the four necessary conditions above, as long as one of them can be broken, the deadlock can be effectively avoided.

The following are the solutions for the four necessary conditions:

1. Allow concurrent access to resources.
2. Process Resource deprivation is allowed.
3. The resources required by the process to go far are submitted at one time.
4. Resources are allocated sequentially, that is, resources are accessed in the same order.

Other methods (the main goal of these methods is to minimize the resource occupation time, thus reducing the chance of deadlock ):

1. Avoid user interaction in transactions.
2. Keep the transaction brief and in a batch.
3. Use a lower isolation level.
4. Use the row version-based isolation level.
5. Bind a connection and bind multiple sessions to a transaction. This effectively reduces the number of transactions.

References:

1. Http://www.cnblogs.com/happyhippy/archive/2008/11/14/1333922.html strongly recommended 」
2. Http://msdn.microsoft.com/zh-cn/library/ms177433.aspx MSDN 」
3. Http://www.cnblogs.com/ching/archive/2012/02/01/2334659.html-another very detailed article 」