Monitoring analysis deadlocks with SQL Server Profiler

In two or more SQL Server processes, each process locks a resource that another process is trying to lock, and a deadlock occurs, for example,
The process Process1 holds 1 exclusive locks (x) on Table1, while PROCESS1 requests 1 exclusive locks (x) for Table2,
Process Process2 holds 1 exclusive locks (x) on table2, while process2 to Table1 requests 1 exclusive locks (x)

In this case, a deadlock occurs, except when an external process disconnects a deadlock, the two transactions in the deadlock will wait indefinitely.

The Microsoft SQL Server Database Engine deadlock monitor periodically checks for tasks that fall into a deadlock.

If the monitor detects a circular dependency, one of the tasks will be chosen as a victim ( usually by selecting a process that takes up less resources ) and then terminating its transaction and prompting for error 1205.

Here we use SQL Server Profiler to monitor the process of analyzing deadlocks, so that we can understand the causes of deadlocks in depth.

1. Create a test table.

On Microsoft SQL Server Management Studio, create a new query, write table deallocktest_1 & deallocktest_2 Two tables:

Script:

Code UseTest

--Create two tables used to parse deadlocks 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
insert into< Span style= "color: #000000;" > deallocktest_1 (Name)
    select  name from  sys.all_objects

    
 into deallocktest_2 (Name)
     select name from sys.all_objects
go
    

After creating the table and inserting the test data, execute the script code (because we do not need to track the code), and then we simulate two sessions, one session containing a transaction. Here we will create a new two query, where the first session, after updating the Deallocktest_1 table, waits 5 seconds and updates deallocktest_2.

Code UseTest
Go

--First session

BeginTran

UpdateDeallocktest_1
SetName=N‘Test1‘
WhereId>0


/*Here the waitfor waits, is to easily get the occurrence of a deadlock*/
WaitforDelay‘00:00:05‘


UpdateDeallocktest_2
Set Name=N'test2'
Where ID >0

Commit Tran

Go

After the code is written, we don't execute the code, and then we write the second session code, and the second session updates the table in the same order as the first session, waiting 5 seconds after updating the deallocktest_2 table, and updating deallocktest_1.

Code UseTest
Go
--A second session

BeginTran

UpdateDeallocktest_2
SetName=N‘Test1‘
WhereId>0

/*Here the waitfor waits, is to easily get the occurrence of a deadlock*/
WaitforDelay‘00:00:05‘

UpdateDeallocktest_1
Set Name=N'test2'
Where ID >0

Commit Tran

Go

The second session code is also not executed first.

2. Start SQL Server Profiler and create Trace (trace).

Start the SQL Server Profiler tool (found on the Tools menu in Microsoft SQL Server Management Studio), and create a Trace,trace property selection that primarily 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. Execute the test code & monitor the deadlock.

Go to the Microsoft SQL Server Management Studio interface, execute the code for the first session & second session, wait for 5 seconds, and we'll find that one of the sessions received an error message

When we switch to the SQL Server Profiler interface, we can see that SQL Server Profiler received information about the deadlock that occurred during the execution of the script.

OK, first stop the "pause tracking" Or "Stop Tracking" button on the SQL Server Profiler, and we'll analyze the deadlock process in detail below.

4. Analysis of Deadlocks

For example, we can see the first session in SPID 54, the second session in SPID 55, and once SQL Server discovers the deadlock, it will determine a winner that can be executed successfully, and another as a victim to rollback.

You can see that in the EventClass column, two sql:batchcompleted events are immediately behind Lock:deallock, one of which is the victim and it will be rolled back. And the other sql:batchcompleted will be the winner, Executed successfully.

Who is the winner, then, and who is the victim? Don't worry, we can know the result by Deallock graph event, the information returned.

While we cannot understand the meaning of the Deallock graph diagram, we know some useful information through the relationships depicted in the diagram. The ellipse on both sides of the graph is quite a processing node, and when the mouse moves to the top, you can see code that executes internally, such as Insert,update,delete. The left oval with a fork is the victim, and the right ellipse without the fork is the winner. The middle two rectangles are the resource nodes (Resource node) that describe the objects in the database, such as a table, a row, or an index. In our current instance, the resource node describes the request for an exclusive lock (X) on the clustered index. Between the ellipse and the rectangle, the line with the arrows indicates that the relationship between the node and the resource node is processed, including the pattern describing the lock.

Next we look at the figures in more detail.

First look at the right side as the winner of this oval, we can see the content contains:

Server Process ID: The server process identifier (SPID), which is the identifier that the server assigns to the process that owns the lock.
Server Batch ID: Server batch identifier (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}, where 0 always represents the main thread or parent thread, and {A-line, ... n} represents a child thread.
Deadlock Priority: deadlock priority for a process for more information about possible values, see SET deadlock_priority (Transact-SQL).
used log: The amount of log space used by the process.
owner ID: The transaction ID of the process that is using the transaction and is currently waiting for the lock.
Transaction Descriptor: A pointer to a transaction descriptor that describes the state of the transaction.

These data describe, for us to understand deadlocks, only need to know some of them enough, unless we work in a specialized SQL Server organization, it is possible to understand them in depth.

Let's look at the Oval processing node on the left as the victim, which tells us the following information:

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

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

3. It has an exclusive lock (X) on the resource node at the bottom right.

4. It requests an exclusive lock (X) on the resource node at the top right.

We look at the two-rectangle resource node, and the two processing nodes use each of them to execute their own code, while there is a resource request to the other party, and the competition for resources occurs.

This also allows us to understand the cause of the deadlock.

Here is a description of some of the resources node information:

HoBT: heap or B-tree. Locks used to protect B-trees (indexes) or heap data pages in tables that do not have a clustered index

Associated ObjID: The associated object ID, which is just the object ID associated with the index.

Index name : indexed name

Let's re-organize the data that SQL Server Profiler monitors:

Review Chart:

1. In line 3rd sql:batchstarting, SPID 54 (the first session starts), get an exclusive lock on the index PK__DEALLOCK__3214EC274222D4EF, and then process the wait state (because in this instance I set up the WAITFOR Delay ' 00:00:05 ')

2. In line 6th sql:batchstarting, SPID 55 (the second session starts), get an exclusive lock on the index PK__DEALLOCK__3214EC2745F365D3, and then process the wait state (because in this instance I set up the WAITFOR Delay ' 00:00:05 ')

3. Two processes each get an exclusive lock (X), a few seconds in the past, they began to request exclusive lock.

SPID 54 (first session), first requests an exclusive lock (X) for pk__deallock__3214ec2745f365d3, but PK__DEALLOCK__3214EC2745F365D3 is currently available to SPID 55 (the second session). SPID 54 waits.

While

SPID 55 (second session) begins to request an exclusive lock (X) for Pk__deallock__3214ec274222d4ef, but Pk__deallock__3214ec274222d4ef is currently available to SPID 54 (the first session). SPID 55 to wait.

There is a process blocking, which can result in a deadlock.

4.SQL Server checks that the two processes (the first & second session) are deadlocked and terminates (kill) on the victim list for a process that consumes less resources. By displaying the left and right elliptical process nodes, you can see that the least used log is the process node on the left.

5. SPID 54 (the first session) is rolled back (Rollback), and SPID 55 (the second session) executes successfully.

Here we have finished, monitoring and analysis of deadlocks.

( Note : Is the definition of other deadlocks, deadlock mode, deadlock avoidance & prevention, etc., not the focus of this article, I did not present, online too much of this article)

(

Monitoring analysis deadlocks with SQL Server Profiler