SQL Server deadlock

ThisArticleThis article mainly tells you how to analyze and correctly solve the SQL Server deadlock problem. SQL Server database deadlocks are generally two or more trans, at the same time, the two sides wait for each other because of the actual application object being requested by the other party. A simple example is as follows:

 
  
  


   
   
Trans1 trans2

   
   
Idbconnection. begintransaction idbconnection. begintransaction

   
   
Update Table A 2. Update Table B

   
   
Update table B 3. Update Table

   
   
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 (,-1) to view all SQL Server 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 c2c2 = C2 + 1 where c1 = @ p1
 
  
  
 
  
Update T1 set c2c2 = 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 (11 = 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 (11 = 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 finds the SQL Server 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, we will analyze why SQL Server is deadlocked? 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 c2c2 = C2 + 1 where c1 = @ p1

   
   
Update T1 set c2c2 = 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 c2c2 = 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 c2c2 = 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.

In fact, we can see why the SQL Server deadlock occurs on the SELECT statement during update. 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 on SQL Server.

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.