Key lookup deadlock resolution in SQL Server High frequency, highly concurrent access

Deadlocks are not unfamiliar to DBAs or database developers, and they can be triggered in a variety of ways, and in general, developers of database applications are designed with some considerations in mind to avoid deadlocks. But sometimes because of some special application scenarios such as high-frequency queries, highly concurrent queries due to the potential problems of database design, Some of the hard-to-catch deadlocks can occur to affect the business. Here we introduce the key lookup deadlock and related solutions due to design problems.

Here we turn on Trace Profiler to track the Deadlock view (Locks:deadlock graph) while testing. (You can also turn on trace flags, or snap deadlocks with extended events (xevents))

Create a Test object code

Create TableTestklup (Clskeyint  not NULL, Nlskeyint  not NULL, Cont1int  not NULL, Cont2Char( the))Create Unique Clustered IndexInx_cls onTestklup (Clskey)Create Unique nonclustered IndexInx_nlcs onTestklup (Nlskey) include (CONT1)Insert  intoTestklupSelect 1,1, -,'AAA'Insert  intoTestklupSelect 2,2, $,'BBB'Insert  intoTestklupSelect 3,3, -,'CCC'

Open Session 1 Simulate high frequency update operation

----Simulate high frequency update operation Declare @i intSet @i= - while 1=1 begin   UpdateTestklupSetCont1=@i   whereClskey=1  Set @i=@i+1 End

Open Session 2 analog High frequency select operation

-- --Analog high frequency select operation Declare @cont2 Char (+)  while 1 = 1 begin    Select @cont2 =  from where nlskey=1End

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

Figure 1-1

The following deadlock diagram is captured in the trace we opened. Figure 1-2

Figure 1-2

Deadlock Analysis: It can be seen that because the read process (108) Requests the write process (79) to hold the X lock is blocked while the write process (79) also applies the read process (108) lock held by the S lock. Read execution plan figure 1-3, write execution plan figure 1-4

(because reading the application under the default isolation level (read commit) is just an instantaneous process, after reading the immediate release, will not wait for the transaction to complete), so in the concurrency, the execution frequency is not very difficult to appear. But the high frequency we simulate makes the S lock get very high frequency, there are just two sessions, a read, A write creates a deadlock phenomenon.

Figure 1-3

Figure 1-4

Deadlock Reason: Additional lock (clustered index) requirement due to key lookup in read operation

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

We can start with the following aspects.

A remove additional keys to find locks required for lock

Cancel acquisition Lock when reading Operation B

A.1 We can create an overwrite 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[cont2])

A.2 According to query requirements, distribution execution, through the clustered index to get query columns, avoid key lookup.

Declare @cont2 Char( the)Declare @clskey int while 1=1begin    Select @clskey=Clskey fromTestklupwhereNlskey=1    Select @cont2=Cont2 fromTestklupwhereClskey=@clskeyEnd

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

Declare @cont2 Char (+)  while 1 = 1 begin    Select @cont2 =  from  with where nlskey=1End

conclusion. When we solve a problem, it's best to understand the nature of the problem and then implement it by finding a solution that is appropriate for your environment through problem points.