Detailed description of the deadlock utility query optimizer in SQL Server

Deadlock) refers to the state in which processes are permanently blocked. SQL can detect deadlocks and terminate a transaction to intervene in the deadlock State. A typical example of A deadlock between two processes is: Obtain lock A in process T1 and apply for lock B; obtain lock B in process T2 and apply for lock A. Here we will demonstrate this situation:

1. Create a Database named InvDB.

2. Execute the following script to create the person table and fill in two pieces of data:

3. Execute the following query simultaneously in the two windows of SQL Server Management Studio:

This code runs at the default read committed isolation level. When two processes obtain an exclusive lock, they apply for a shared lock from the other side, causing a deadlock.

It can be seen that a process can be updated normally and the result is displayed, while another process has been rolled back:

(1 row (s) affected)
Msg 1205, Level 13, State 45, Line 8
Transaction (Process ID 55) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

4. Start SQL Server Profiler and select the following four Events:

Run the above deadlock experiment again and you can see the deadlock diagram as follows:

It is very interesting that the second execution of the above statement will not lead to deadlocks! This is because in the two processes, SQL Server will intelligently identify the update statement that does not need to be done, so it will not obtain the exclusive lock, of course, it will not deadlock. The SQL Server 2008 query optimizer is really powerful!

Original article title: deadlock in SQL Server

Link: http://www.cnblogs.com/foamliu/archive/2010/08/25/1808394.html