[Fatal] SQL Server is always deadlocked, or prompts that the process has been sacrificed. How can this problem be solved?

From: http://atkins.5d6d.com/viewthread.php? Tid = 6847

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:
1. In the trans3 window, Select Exec P1 4 and right click. Have you seen the menu? Select analyse query in Database Engine Tuning Advisor.
2. Note 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 the start analysis button in the upper left corner.
4. Smoke a cigarette. Come back and check the result! 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 automatically creates 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?
This time, let's analyze why the deadlock occurs? Let's review the two SP statements:
Create proc P1 @ P1 int
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
Strange! P1 has no insert, no Delete, no update, but a SELECT statement. P2 is the update statement. As we mentioned earlier, updata A and update B in trans1; Upate B and update a in trans2 are not pasted!
So what causes a deadlock?
Check the SQL deadlock information from the event log:
Spid X is running this query (line 2 of Proc [P1], inputbuffer "… Exec P1 4 ...") :
Select C2, C3 from T1 where C2 between @ P1 and @ P1 + 1
Spid y is running this query (line 2 of Proc [P2], inputbuffer "Exec P2 4 "):
Update T1 set C2 = C2 + 1 where c1 = @ p1

The select is waiting for a shared key lock on index t1.cidx. The update holds a conflicting x lock.
The update is waiting for an exclusive key lock on index t1.idx1. The select holds a conflicting s lock.
First, let's look at the execution plan of P1. What do you think? You can run set statistics profile on. The following is the execution plan of P1.
Select C2, C3 from T1 where C2 between @ P1 and @ P1 + 1
| -- Nested loops (inner join, outer references [uniq1002], [T1]. [C1])
| -- Index seek (object [T1]. [idx1]), seek [T1]. [C2]> = [@ P1] and [T1]. [C2] <= [@ P1] + (1) ordered forward)
| -- Clustered index seek (object [T1]. [CIDX]), seek [T1]. [C1] = [T1]. [C1] and [uniq1002] = [uniq1002]) lookup ordered forward)
We can see a nested loops. The first row uses the index t1.c2 to perform seek. The rowid obtained by seek is used in the second row to query the data of the entire row through the clustered index. What is this? Bookmark lookup! Why? Because the C2 and C3 we need cannot be completely brought out by the t1.c1 index, we need to bookmark it for search.
Okay, let's look at the P2 execution plan.
Update T1 set C2 = C2 + 1 where c1 = @ p1
| -- Clustered Index Update (Object :( [T1]. [CIDX]), object :( [T1]. [idx1]), set :( [T1]. [C2] = [expr1004])
| -- Compute scalar (define :( [expr1013] = [expr1013])
| -- Compute scalar (define :( [expr1004] = [T1]. [C2] + (1), [expr1013] = case when...
| -- Top (rowcount est 0)
| -- Clustered index seek (Object :( [T1]. [CIDX]), seek :( [T1]. [C1] = [@ P1]) ordered forward)
Locate a row through the seek of the clustered index and start updating. Note that during update, it will apply for an X lock for clustered index.
As a matter of fact, we can see why update has a deadlock on select. During update, an X lock for clustered index will be applied to block the lock (note, it is not a deadlock !) The last clustered index seek in the SELECT statement. Where is the other half of the deadlock? Note that in our SELECT statement, C2 exists in index idx1, and C1 is a clustered index CIDX. The problem is here! We updated the value C2 in P2, so sqlserver will automatically update the non-clustered Index containing the C2 column: idx1. Where is idx1? In our SELECT statement just now. The change to this index column means that a row or some rows in the index set need to be re-arranged, and re-arranged requires an X lock.
So ........., The problem was discovered.
To sum up, a query uses a non-clustered index to select data, so it will hold an S lock on the non-clustered index. When some select columns are not on this index, it needs to find the corresponding clustered index row based on rowid, and then find other data. At this time, in the second query, update is busy on Clustered indexes: positioning, locking, modification, etc. However, because a column being modified is another non-clustered index column, it needs to change the information of the non-clustered index at the same time, this requires the second X lock on the non-clustered index. Select starts to wait for the X lock of the update, update starts to wait for the s lock of the select, deadlock, and so on.
So why does the deadlock disappear when we add a non-clustered index? Let's take a look at the execution plan following the Index automatically added above:
Select C2, C3 from T1 where C2 between @ P1 and @ P1 + 1
| -- Index seek (Object :( [deadlocktest]. [DBO]. [T1]. [_ dta_index_t1_7_2073058421 _ k2_k1_3]), seek :( [deadlocktest]. [DBO]. [T1]. [C2]> = [@ P1] and [deadlocktest]. [DBO]. [T1]. [C2] <= [@ P1] + (1) ordered forward)
Oh, there is no need for clustered index, because the added overwrite index is enough to select all the information. That's simple.
In fact, in sqlserver 2005, if you use profiler to capture eventid: 1222, a deadlock chart will appear, which is very intuitive.
The following method helps minimize the number of deadlocks (For details, refer to sqlserver online help and search: to minimize the number of deadlocks.
Access objects in the same order.
Avoid user interaction in transactions.
Keep the transaction brief and in a batch.
Use a lower isolation level.
Use the row version-based isolation level.
Set the read_committed_snapshot database option to on to enable row Version Control for committed read transactions.
Use Snapshot isolation.
Use bind connection.