) Analyze and solve the SQL Server deadlock problem

-Deadlock. In short, two or more trans simultaneously request an object being requested by the other party, causing both parties to wait for each other. A simple example is as follows:
Trans1 trans2
------------------------------------------------------------------------
1. IDBConnection. BeginTransaction 1. IDBConnection. BeginTransaction
2. update table A 2. update table B
3. update table B 3. update table
4. IDBConnection. Commit 4. IDBConnection. Commit

It is easy to see that if trans1 and trans2 reach step 3 respectively, trans1 will request the X lock for B and trans2 will request the X lock for, the two locks have been held by the other Party on step 2. Because the lock is not obtained, the subsequent Commit cannot be executed, so that both parties begin to deadlock.

Let's look at a simple example to explain how to solve the deadlock problem.
-- Batch #1
Create database deadlocktest
GO
USE deadlocktest
SET NOCOUNT ON
Dbcc traceon (1222,-1)
-- In SQL2005, a new dbcc parameter is added, that is, 1222. Originally in 2000, we know that dbcc can be executed.
-- Traceon (1204,3605,-1) to view all deadlock information. In SqlServer 2005, 1204 is enhanced, which is 1222.
GO

IF OBJECT_ID ('T1 ') is not null drop table t1
IF OBJECT_ID ('p1') is not null drop proc p1
IF OBJECT_ID ('p2 ') is not null drop proc p2
GO

Create table t1 (c1 int, c2 int, c3 int, c4 char (5000 ))
GO

DECLARE @ x int
SET @ x = 1
WHILE (@ x <= 1000) BEGIN
Insert into t1 VALUES (@ x * 2, @ x * 2, @ x * 2, @ x * 2)
SET @ x = @ x + 1
END
GO

Create clustered index cidx ON t1 (c1)
Create nonclustered index idx1 ON t1 (c2)
GO

Create proc p1 @ p1 int as select c2, c3 FROM t1 WHERE c2 BETWEEN @ p1 AND @ p1 + 1
GO

Create proc p2 @ p1 int
UPDATE t1 SET c2 = c2 + 1 WHERE c1 = @ p1
UPDATE t1 SET c2 = c2-1 WHERE c1 = @ p1
GO

The preceding SQL statement creates a demo database for deadlock, inserts 1000 pieces of data, and creates a clustered index for column c1 and a non-clustered index for column c2 on table t1. In addition, two sp instances are created, namely select data and update data from t1.

Okay. Open a new query window and we will start executing the following query:
-- Batch #2

USE deadlocktest
SET NOCOUNT ON
WHILE (1 = 1) EXEC p2 4
GO

After the execution starts, we open the third query window and execute the following query:
-- Batch #3

USE deadlocktest
SET NOCOUNT ON
Create table # t1 (c2 int, c3 int)
GO

WHILE (1 = 1) BEGIN
Insert into # t1 EXEC p1 4
Truncate table # t1
END
GO

Start execution. Haha. Soon, we saw the following error message:
Msg 1205, Level 13, State 51, Procedure p1, Line 4
Transaction (Process ID 54) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Spid54 found a deadlock.
So how can we solve it?

In SqlServer 2005, we can do this:
1In the trans3 window, Select EXEC p1 4 and right click. Have you seen the menu? Select Analyse Query in Database Engine Tuning Advisor.
2Note that in the window on the right, wordload has three options: Load file, table, and query statement. Because we select the query statement method, we do not need to modify this radio option.
3. Click Start Analysis in the upper left corner.
4Smoke a cigarette. Come back and check the results! An analysis result window is displayed. In Index Recommendations, we find a message: Add a non-clustered Index on table t1: t2 + t1.
5. In the top menu of the current window, select Action and Apply Recommendations. The system will automatically create this index.

Run batch #3 again.

In this way, we can solve most SQL Server deadlocks. So what is the root cause of this deadlock? Why is the problem solved by adding a non clustered index? Let's break it down.