Key lookup deadlock resolution in SQL Server High frequency concurrent access

Deadlocks are not unfamiliar to DBAs or database developers, and they can be triggered in a variety of ways. In general, database application developers in the design will have a certain consideration in order to avoid the creation of deadlock. But sometimes because of some special application scenarios such as High-frequency query, high concurrent query due to database design potential problems, some difficult to capture deadlock may occur to affect the business. Here for you to introduce the design problem caused by the key to find deadlocks 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, ' AAA '
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
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

The following deadlock figure was captured in our open trace. 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
Begin
Select @cont2 =cont2 from Testklup with (NOLOCK) where Nlskey=1
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.