In simple interpretation of MsSql in high frequency, high concurrency access key to find deadlock problem _mssql

Deadlocks are no stranger to DBAs or database developers, it's a variety of triggers, in general, database application developers in the design will have some considerations and then try to avoid deadlock. But sometimes because of some special application scenarios such as High-frequency query, high concurrent query due to the potential problems of database design, Some deadlocks that are not easy to capture may appear to affect the business. Here we introduce the key lookup deadlock caused by design problems and related solutions.

Here we turn on Trace Profiler to trace the deadlock view (Locks:deadlock graph) while testing. (You can, of course, turn on trace tags, or Apply extended events (xevents) to catch deadlocks)

To create a test object code

CREATE TABLE Testklup
(
clskey int not NULL,
Nlskey int not NULL,
cont1 int not null,
Cont2 char (3000)
)

Create unique clustered index inx_cls on Testklup (clskey)

create unique nonclustered index Inx_nlcs on Testklup (nlskey Include (cont1)

insert INTO Testklup select 1,1,100, ' Triple-a '
insert into Testklup select 2,2,200, ' BBB '
Insert INTO Testklup select 3,3,300, ' CCC '

Open Session 1 analog HF update operation

----Analog High Frequency update operation

 DECLARE @i int
set @i=100 while
1=1
 begin 
 Update Testklup set cont1=@i 
 where Clskey=1
 Set @i=@i+1 End
 

Open Session 2 analog high-frequency select operation

----Analog High Frequency Select operation

Declare @cont2 char (3000) while
1=1 the
begin
  Select @cont2 =cont2 from Testklup where Nlskey=1
end

When session 2 is open for a short period of time, we can see a similar error message: Figure 1-1

Figure 1-1

The following deadlock figure was captured in our open trace. Figure 1-2

Figure 1-2

Deadlock Analysis: You can see that due to the read process (108) Request write process (79) held by the X lock is blocked, write process (79) and also request read process (108) lock hold s lock. Read execution plan 1-3, write execution plan 1-4

(due to the default isolation level (read commit) Read request S lock is just an instant process, after reading the release immediately, will not wait for the transaction to complete, so it is not easy to appear in the case of concurrent, low execution frequency. But our simulated high-frequency situation makes the S lock get very high frequency, at this time there are only two sessions, one read, A write creates a deadlock phenomenon.

Figure 1-3

Figure 1-4
Deadlock Reason: Additional lock (clustered index) requirements caused by key lookup in read operations

Solution: After understanding the cause of the deadlock, it is easier to solve.

We can start with the following several aspects.

A eliminates additional keys to find locks required for locks

Cancel acquire lock when read operation B

A.1 We can create an overlay index to include the query column in the SELECT statement in the specified index

CREATE nonclustered INDEX [inx_nlskey_incont2] on [dbo]. [Testklup]
([Nlskey] ASC) INCLUDE ([Cont2])

A.2 According to the query requirements, step through the clustered index to get the query column, to avoid key lookup.

Declare @cont2 char (3000)
declare @clskey int while
1=1
begin
  Select @clskey =clskey from Testklup where Nlskey=1

  Select @cont2 =cont2 from Testklup where clskey= @clskey
end

b by changing the isolation level, using optimistic concurrency mode, the source line does not need to be locked when reading operations

Declare @cont2 char (3000) while
1=1 the
begin
  Select @cont2 =cont2 from Testklup with (NOLOCK) where nlskey=1< C12/>end

Conclusion. When we solve the problem, it is best to understand the nature of the problem, through the problem point to find a suitable solution for their environment to implement.