SQL Server Deadlock Detailed

Read the online a lot about the deadlock of the blog, we usually introduce the principle of the deadlock, I also write a detailed analysis. Of course, it's about SQL Server's deadlock.

Fetching deadlock events using SQL Profiler

2. Select tab: Event Selection, tick show all events in the lower right corner, find locks event, tick on deadlock graph.

3, then select the Third tab: Event extraction settings, tick the deadlock XML, pop-up file save path, enter the filename.

4, the above settings, the deadlock will be saved to the deadlock XML file, the operation found that only the last deadlock diagram saved, if the deadlock is more, you need to use SQL Profiler to save one by one.

5, the deadlock XML file default suffix is. XDL, double-click Open directly will use SSMS open, only see very basic information. As follows:

This illustration does not explain the problem in detail. We can take another approach, left-click to select the deadlock XML file, right-select Open with: Text editor. The contents of the entire deadlock are at a glance, as follows:

Red box, I marked out, I mainly on the black box inside the details, in fact, there are a lot of friends to understand the deadlock after the basic has been understood.

1. Every deadlock XML file has a root node: deadlock-list.

2, under the root node, there is a node deadlock victim= "process59bcbc8", where the victim value is the number of deadlocks, if there are multiple deadlocks under the Open SQL Profiler, then their numbers are consistent, Only the deadlock numbers that are tracked by different SQL Profiler will be different. In addition, the value is the sacrificial process ID number, which is distinguished from the other ID number, which is described later in detail.

3, there are two nodes under the deadlock victim node, process-list,resource-list. The Process-list node mainly explains some information about deadlocks. The Resource-list node explains the grant of the lock.

4, process-list node, generally there are two process nodes, each node represents a deadlock in a process, if more than one process caused by the deadlock, then there will be multiple processes node. Let's look at the first process node, which, according to the previous picture, is the process of being sacrificed.

1) attribute id= "process7a9a988", this is the process ID number that is sacrificed, in the file we can see the ID number of two process nodes is different. The ID property of the other node is id= "Process7abee08"

2) attribute waitresource= "page:7:1:8459005", page represents this is a page lock, this page appears in the database id=7, file id=1, page id=8459005 location. The waitresource= "page:7:1:7861369" under another Procee node.

3) Attribute Transactionname = "Select", which represents the transaction type, select indicates that this is a query transaction and can be compared to the Transactionname property under another Procee node, transactionname= " UPDATE "

4) attribute lockmode= "S", the lock mode is a shared lock. Another process node's lock mode lockmode= "IX" is an intent exclusive lock.

5) attribute status= "suspended", the state of the lock. The status= "suspended" of another process node.

6) attribute Hostname= "999VISTA" refers to the computer name that operates the transaction. The hostname= "Jack" of another process node

7) The attribute loginname= "sa" refers to the SQL account that is logged into the database. Another process node is loginname= "AAA".

8) Attribute Isolationlevel= "Read Committed (2)" refers to the transaction isolation level.

9) node Executionstack, under which there are two frame nodes, the text in the middle of the first node is the execution statement. The second node, did not understand what meaning.

10) node Inputbuf, the text in the middle of this node is also the execution statement, and the text of the first frame node under the Executionstack node, it is inferred that the second node under the Executionstack node is not another SQL statement in the execution process??

5, Resource-list node. There are two Pagelock nodes under this node, each representing a page lock held by each of the two processes. If there are multiple page locks, will there be more than one pagelock node? Or a table lock, what node would that be??

1) The first pagelock node, fileid= "1" pageid= "8459005" dbid= "7", this follows the Process-list node of the first process node of the Properties waitresource= "page: 7:1:8459005 "means the same. Objectname= "Table name", this is the table name, the specific format: Db_name.schema_name. table_name.

2) There are multiple owner-list nodes under the Pagelock node, and this is a node for each process, because the deadlock is caused by two processes and only two nodes.

3) The Owner-list node has an owner, which represents the grant of the lock, presumably: If you hold multiple locks, there may be multiple nodes.

4) Owner node: id= "Process7abee08" mode= "IX", ID indicates the process Id,mode represents the lock type.

5) There are multiple waiter-list nodes under the Pagelock node, which corresponds to the Owner-list node one by one.

6) The Waiter-list node has a waiter node, which represents the grant situation of the lock, presumably: If you hold multiple locks, there may be multiple nodes.

7) Waiter node: id= "process7a9a988" mode= "S" requesttype= "Wait", the ID indicates that the process Id,mode represents the lock type, and RequestType represents the wait grant.

8) through the above introduction, we can learn:

Process process7a9a988 First has a page:7:1:7861369 share lock s, this time, it requests to get page:7:1:8459005 share lock s, at the same time, Process7abee08 first owned the page : 7:1:8459005 Intent Exclusive Lock IX, and request to acquire PAGE:7:1:7861369 's intent exclusive lock, so that two processes blocking each other causes the deadlock. I combined with the database table structure analysis, found that the table has 6,880 characters per row of fields, that is, a row occupies a page, while the process process7a9a988 using a LINQ query, because LINQ caused this query actually do not walk the clustered index, Go ahead. The index of the AccountId field and then Caseid in the nested loop primary key index is one of the reasons for the deadlock.

The above is my opinion on the deadlock, if there is objection, welcome to discuss.