Use SQL Server Profiler to monitor and analyze deadlocks

In two or more SQL server processes, a deadlock occurs when each process locks the resources that other processes attempt to lock. For example,
The process process1 holds one exclusive lock (x) for Table1, and process1 requests one exclusive lock (x) for Table2 ),
The process process2 holds one exclusive lock (x) for Table2, and process2 requests one exclusive lock (x) for Table1)

In this case, a deadlock occurs. Unless an external process is disconnected, the two transactions in the deadlock will wait for an indefinite period of time.

The Microsoft SQL Server database engine deadlock monitor regularly checks deadlocked tasks.

If the monitor detects a circular dependency, it selects one of the tasks as the victim (Generally, processes that account for a small amount of resources are used as victims.), Then terminate the transaction and prompt an error.1205.

Here we use SQL Server Profiler to monitor and analyze the deadlock process, so that we can deeply understand the cause of the deadlock.

 

1. Create a test table.

On Microsoft SQL Server Management studio, create a new query and write the creation table deallocktest_1 & deallocktest_2:

 

Script:

Code

Use test

-- Create two tables used for analysis deadlock: deallocktest_1 & deallocktest_2
Go
Set nocount on
Go
If object_id ('allocktest _ 1') is not null
Drop table deallocktest_1
Go
Create Table deallocktest_1
(
Id int identity (1, 1) primary key,
Name nvarchar (512)
)

If object_id ('allocktest _ 2') is not null
Drop table deallocktest_2
Go
Create Table deallocktest_2
(
Id int identity (1, 1) primary key,
Name nvarchar (512)
)

Go

-- Insert some test data
Insert into deallocktest_1 (name)
Select name from SYS. all_objects

Insert into deallocktest_2 (name)
Select name from SYS. all_objects
Go

After creating a table and inserting test data,Execute the script code first (because we do not need to trace the code)Then we simulate two sessions, one containing one transaction. Here we will create two new queries. The first session is to update the deallocktest_1 table and wait for 5 seconds to update deallocktest_2.

 

 

Code

Use test
Go

-- First session

Begin tran

Update deallocktest_1
Set Name = n' test1'
Where ID> 0


/* Here the waitfor wait is to easily obtain the occurrence of a deadlock */
Waitfor delay '00: 00: 05'


Update deallocktest_2
Set Name = n' test2'
Where ID> 0

Commit tran

Go

 

After the code is written, do not execute the code first, and then write the second session code. The second session updates the table order, just opposite to the first session, after updating the deallocktest_2 table, wait for 5 seconds to update deallocktest_1.

 

 

Code

Use test
Go
-- Second session

Begin tran

Update deallocktest_2
Set Name = n' test1'
Where ID> 0

/* Here the waitfor wait is to easily obtain the occurrence of a deadlock */
Waitfor delay '00: 00: 05'

Update deallocktest_1
Set Name = n' test2'
Where ID> 0

Commit tran

Go

 

Do not execute the second session code.

 

 

2. Start SQL Server Profiler and create trace ).

 

Start the SQL Server Profiler tool (you can find it on the Microsoft SQL Server Management studio tool menu) and create a trace. The trace attribute selection mainly includes:

Deadlock Graph
Lock: deadlock
Lock: deadlock chain
RPC: Completed
SP: stmtcompleted
SQL: batchcompleted
SQL: batchstarting

 

Click the execute button to start the trace.

 

3. Run the test code &MonitoringDeadlock.

Go to the Microsoft SQL Server Management studio interface and execute the code for the first session and the second session. Wait for five seconds and we will find that one of the sessions receives an error message.

 

 

 

After switching to the SQL Server Profiler interface, we can find that the SQL Server Profiler receives the deadlock information during script execution.

 

 

OK. Here we will stop the "suspend trail" or "Stop trail" button on SQL Server Profiler. Next we will analyze the deadlock process in detail.

 

 

4. Analyze deadlocks

For example, we can see that the first session is in spid 54 and the second session is in spid 55. Once the SQL Server discovers a deadlock, it will determine a winner and can be successfully executed, and the other, as victims, should be rolled back.

As you can see, in the eventclass column, two SQL: batchcompleted Events follow lock: deallock, one of which is the victim and will be rolled back. another SQL statement: batchcompleted will be the winner and executed successfully.

So who are the winners and who are the victims? Don't worry. We can know the result of the returned information through the deallock graph event.

 

 

 

Although we cannot understand the meaning of the deallock graph, we know some useful information through the relationship described in the graph. In the figure, there is a process node with an elliptical shape on both sides. When you move the cursor over the process node, you can see the code executed internally, such as insert, update, and delete. the left oval shape with the cross is the victim, and the right oval shape without the cross is the winner. The two rectangles in the middle are a resource node, which describes objects in the database, such as a table, a row, or an index. In our current instance, the resource node describes how to obtain the exclusive lock (x) In the clustered INDEX request ). Between the oval shape and the rectangle, an arrow line is used to process the relationship between the node and the resource node, including the mode of describing the lock.

 

Next, let's take a closer look at the data description in the figure.

First, let's look at the elliptical shape on the right as the winner. We can see that the content includes:

 

Server process ID: The server process identifier (spid), that is, the identifier assigned by the server to the process with the lock.
Server Batch ID: Server Batch ID (sbid ).
Execution context ID: Execution context identifier (ECID ). The execution context ID of the given Thread associated with the specified spid. ECID = {0, 1, 2, 3,... n}. 0 indicates the master thread or parent thread, and {1, 2, 3,... n} indicates the child thread.
Deadlock priority:For more information about possible values of a process's deadlock priority, see set deadlock_priority (TRANSACT-SQL ).
Used logs:The amount of log space used by the process.
Owner ID: The ID of the transaction that is using the transaction and is currently waiting for the lock.
Transaction descriptor:Pointer to the transaction descriptor that describes the transaction status.
 

This data description is sufficient for us to understand deadlocks unless we work in a dedicated SQL Server organization.

 

 

 

Next, let's look at the elliptical processing node on the left as the victim. It tells us the following information:

1. It is a failed transaction. (Blue Cross representation)

2. It is the T-SQL code as a victim.

3. It has an exclusive lock (X) on the resource node in the lower right ).

4. It requests an exclusive lock (x) for the resource node in the upper right corner ).

 

 

 

 

Let's take a look at the two rectangular Resource Nodes in the middle. The two processing nodes have the right to use each of them to execute their own code, and at the same time, they have the action to request resources from each other, this leads to resource competition.

This makes us understand the cause of the deadlock.

 

Some information about the resource node is described here:

 

HOBT:Heap or B-tree. Used to protect the lock on the B-tree (INDEX) or heap data page in a table without clustered Indexes

Associated objid:ID of the associated object, which is only the ID of the object associated with the index.

Index Name:Index name

 

 

 

 

 

 

 

 

 

Let's sort out the data monitored by SQL Server Profiler again:

Review:

 

1. on the first line of SQL: batchstarting, spid 54 (started in the First Session), obtain an exclusive lock in index pK _ deallock _ 3214ec274222d4ef and then process the waiting state, (because in this instance I set waitfor delay '00: 00: 05 ')

2. in row 3 SQL: batchstarting, spid 55 (started in the second session), obtain an exclusive lock in index pK _ deallock _ 3214ec2745f365d3, and then process the waiting state, (because in this instance I set waitfor delay '00: 00: 05 ')

3. Each process obtains an exclusive lock (x). After several seconds, they start to request the exclusive lock.

Spid 54 (first session), first request an exclusive lock (x) for pK _ deallock _ 3214ec2745f365d3 ), however, pK _ deallock _ 3214ec2745f365d3 has been obtained for spid 55 (second session. Spid 54 is waiting.

At the same time,

Spid 55 (second session), start to request an exclusive lock (x) for pK _ deallock _ 3214ec274222d4ef ), however, pK _ deallock _ 3214ec274222d4ef has been obtained for spid 54 (the first session. Spid 55 is waiting.

 

Here the process is blocked, causing a deadlock.

 

4. SQL Server checks that the two processes (the first and second sessions) have deadlocks, and adds the processes that consume less resources to the victim list and terminates them (kill ). You can findUsed logsThe least is the process node on the left.

 

5. spid 54 (the first session) is rolled back, And spid 55 (the second session) is successfully executed.

 

Here we have completed the deadlock monitoring and analysis.

 

(Note: It is the definition of other deadlocks, the deadlock mode, the deadlock avoidance and prevention, and so on. It is not the focus of this article. I did not propose it. There are too many articles on the Internet)

 

(End)