Deadlock in SQL Server execution of select and update statements at the same time

From: http://www.oecp.cn/hi/zhaolihong/blog/1980 When SQL Server was recently used in projects, it was found that frequent updates and frequent queries cause deadlocks in high concurrency. We usually know that if two transactions insert or modify data to a table at the same time, it will occur when the X lock of the table is requested, and it is already held by the other party. Because the lock is not obtained, the subsequent commit cannot be executed, so that both parties begin to deadlock. But how can a deadlock occur when the SELECT statement and the update statement are executed simultaneously? After reading the following analysis, you will understand ......

First, let's take an example:
 
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

P1 has no insert, no Delete, no update, but a SELECT statement. P2 is the update statement.
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
@ 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 see a nested
Loops, the first row, uses the index t1.c2 for seek. The rowid of the 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,
The X lock of the index is blocked. (Note that it is not a deadlock !) The last clustered index in select
Seek. 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, for clustered
There is no requirement for the index, because the added overwrite index is enough to select all the information. That's simple.

In fact
If profiler is used to capture eventid: 2005, a deadlock chart appears, Which is 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 allow committed read transactions to use row version control.

-Use Snapshot isolation.

· Use the bound connection.