SQL Server dead-lock analysis

Sometimes you like your books. -- Feng Tang

1. The dead-lock event occurs in sqlserver, mainly because two threads occupy the exclusive lock of a resource respectively, and then the two threads obtain the exclusive lock of the resources owned by the other side. This causes dead-lock.

 

2. when encounters dead-lock, it selects one thread from two threads as the winner, the winner transaction is executed, the other thread will become the loser, and the loser transaction will be rollback, for loser, sqlserver returns an error message to the application. If the application is robust, sqlserver will handle the error and obtain the dead-lock error, the transaction that has failed is committed again. If not processed, an error occurs.

 

3. SQL Server Profiler can be used in SQL Server to track deadlocks. The specific process is as follows:

3.1 select related transactions, such:

 

If SQL Server encounters a deadlock, the events in the list will be triggered, so that you can track and determine the cause of the dead-lock.

3.2 run tracking

The dead-lock situation encountered in the educational administration system must be reproduced again. Let SQL Server Profiler capture.

3.3 Analysis and tracking

After obtaining the dead-lock phenomenon, you can see a diagram similar to the following in profiler:

 

 

The figure shows that the dead-lock events is set out in row 9th, and the specific description is displayed. Lines 13 and 14 show that one transaction is forcibly rolled back and the other transaction continues to be executed. You can click "view more details" in Row 3. See what SQL statements cause dead-lock and what resources are acquired by two transactions.

 

3.4 after the information is obtained, further in-depth analysis is made based on the specific code logic.

Reference:

Http://www.simple-talk.com/ SQL /learn-sql-server/how-to-track-down-deadlocks-using-sql-server-2005-profiler/