Deadlock description in SQL Server _mssql

A typical example of two process deadlocks is: Get lock A In process T1, apply for lock B; Get lock B In process T2, request lock A, we'll do the following to demonstrate this situation:

1. Create a database named Invdb.

2. Execute the following script to create the person table and populate two data:

3. Execute the following query at the same time in the two windows of SQL Server Management Studio:

This code runs under the default Read Committed isolation level, and two processes request a shared lock for each other in order to create a deadlock, respectively, when acquiring an exclusive lock.

It is visible that a process can update and display the results normally, and another process has been rolled back:

(1 row (s) affected)
MSG 1205, level, state, line 8
Transaction (Process ID) is deadlocked on lock and another Process and has been chosen as the deadlock Vict im. Rerun the transaction.

4. Start SQL Server Profiler, select the following 4 events:

To perform the above deadlock experiment again, you can see the deadlock diagram shown below:

It is interesting to note that the second execution of the above statement does not take place as a deadlock! This is because in two processes, SQL Server will intelligently recognize that the UPDATE statement is not needed, so it will not get the exclusive lock, of course, it will not deadlock. The query optimizer for SQL Server 2008 is really powerful!