SQL Server Profiler Analysis of deadlock steps

This article will introduce several major steps for SQL Server Profiler to analyze deadlocks. This is also to help you better manage your databases.

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 ), at the same time, process1 requests one exclusive lock (X) for table2, process2 holds one exclusive lock (X) for table2, and process2 requests one exclusive lock (X) for table1, 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 (usually a process that occupies a small amount of resources as the victim), terminates its transactions, and prompts 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:

 
 

  
  
UseTest
  
  
-- Create two tables used for analysis deadlock: DealLockTest_1 & DealLockTest_2
  
  
Go
  
  
SetNocountOn
  
  
Go
  
  
Ifobject_id ('deallocktest _ 1') IsNotNull
  
  
DropTableDealLockTest_1
  
  
Go
  
  
CreateTableDealLockTest_1
  
  
(
  
  
IDintIdentity (1, 1) PrimaryKey,
  
  
Namenvarchar (512)
  
  
)
  
  
Ifobject_id ('deallocktest _ 2') IsNotNull
  
  
DropTableDealLockTest_2
  
  
Go
  
  
CreateTableDealLockTest_2
  
  
(
  
  
IDintIdentity (1, 1) PrimaryKey,
  
  
Namenvarchar (512)
  
  
)
  
  
Go
 
 

-- Insert some test data

 
 

  
  
InsertIntoDealLockTest_1(Name) 
  
  
SelectnameFromsys.all_objects 
  
  
InsertIntoDealLockTest_2(Name) 
  
  
SelectnameFromsys.all_objects 
  
  
Go
 
 

 

After creating a table and inserting test data, execute the script code 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.

 
 

  
  
UseTest
  
  
Go
  
  
-- First session
  
  
BeginTran
  
  
UpdateDealLockTest_1
  
  
SetName = n' test1'
  
  
WhereID> 0
  
  
/* Here the Waitfor wait is to easily obtain the occurrence of a deadlock */
  
  
WaitforDelay '00: 00: 05'
  
  
UpdateDealLockTest_2
  
  
SetName = n' test2'
  
  
WhereID> 0
  
  
CommitTran
  
  
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.

 
 

  
  
UseTest
  
  
Go
  
  
-- Second session
  
  
BeginTran
  
  
UpdateDealLockTest_2
  
  
SetName = n' test1'
  
  
WhereID> 0
  
  
/* Here the Waitfor wait is to easily obtain the occurrence of a deadlock */
  
  
WaitforDelay '00: 00: 05'
  
  
UpdateDealLockTest_1
  
  
SetName = n' test2'
  
  
WhereID> 0
  
  
CommitTran
  
  
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 Chain RPC: Completed SP: StmtCompleted SQL: BatchCompleted SQL: BatchStarting

Click the execute button to start the Trace.

3. Execute the test code and monitor deadlocks.

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: SPID, which is the identifier assigned by the server to the process that owns the lock. Server Batch ID: Server Batch ID (SBID ). Execution context ID: The 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: the deadlock priority of a process. For more information about possible values, see SET DEADLOCK_PRIORITY (Transact-SQL ). Used logs: The amount of log space used by the process. Owner ID: 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 state.

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 B-tree index in a table without clustered indexes) or the lock of the heap data page

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. in line 1 of SQL: BatchStarting, SPID 54 is started in the First Session), obtain an exclusive lock in index PK _ DealLock _ 3214EC274222D4EF, and then process the wait state, (because in this instance I set Waitfor Delay '00: 00: 05 ')

2. in line 2 of SQL: BatchStarting, SPID 55 started in the second session), obtain an exclusive lock in index PK _ DealLock _ 3214EC2745F365D3, and then process the wait 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, but PK _ DealLock _ 3214EC2745F365D3 has currently given SPID 55 the 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, but PK _ DealLock _ 3214EC274222D4EF has already given 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 Kill ). By displaying the Left and Right elliptical process nodes, you can find that the process nodes on the left are the least used logs.

5. The first session of SPID 54 is rolled back and the second session of SPID 55 is executed successfully.

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 have not mentioned it. There are too many articles in this regard on the Internet)