High-frequency QL Server, key lookup deadlock resolution in highly concurrent access

Deadlock is no stranger to DBAs or database developers. It can lead to a variety of causes. In general, database application developers have certain considerations during design, so as to avoid deadlocks as much as possible. however, due to some special application scenarios, such as high-frequency queries and high-concurrency queries, some deadlocks that are difficult to capture may affect services due to potential database design problems. here we will introduce the key deadlock and related solutions caused by design issues.

Here, we will enable the trace Profiler trace deadlock view (locks: deadlock graph) at the same time of the test. (Of course, we can also enable the trace mark, or apply extended events (xevents) to catch deadlocks)

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 100, 200, 'aaa' insert into testklup select, 'bbb 'insert into testklup select 300, 'ccc'
 

Enable Session 1 to simulate high-frequency update

 
---- Simulate high-frequency update operation declare @ I intset @ I = 100 while 1 = 1 begin update testklup set [email protected] Where clskey = 1 Set @ [email protected] + 1 end
 

Enable Session 2 to simulate high-frequency select operations

 
---- Simulate high-frequency Select Operation declare @ cont2 char (3000) while 1 = 1 begin select @ cont2 = cont2 from testklup where nlskey = 1end
 

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

Figure 1-1

 

The following deadlock figure is captured in the trail we opened. Figure 1-2

Figure 1-2

 

Deadlock analysis: we can see that because the read process (108) request write process (79) holds the X lock is blocked, while the write process (79) applies for the read process (108) s lock held by the lock. read execution plan diagram 1-3, write execution plan diagram 1-4

(In the default isolation level (read commit), the Read Request S lock is only an instant process. After reading the lock, it is released immediately without waiting for the transaction to complete, the execution frequency is not high. however, the high frequency of the simulation results in a very high frequency of S lock acquisition. At this time, there are only two sessions, one read and one write, resulting in a deadlock.

 

Figure 1-3

Figure 1-4

 

Cause of deadlock: Additional lock (clustered index) requirements caused by key searches in read Operations

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

We can start from the following aspects.

A. Remove the lock required by the extra key search lock.

B. Cancel the lock acquisition during the read operation.

A.1 we can create a overwriting index so that the query columns in the SELECT statement are included in the specified index.

 

Create nonclustered index [inx_nlskey_incont2] on [DBO]. [testklup] ([nlskey] ASC) include ([cont2])
 

 

A.2 perform step-by-step query based on query requirements and obtain query columns through clustered indexes to avoid key search.

 
Declare @ cont2 char (3000) Declare @ clskey intwhile 1 = 1 begin select @ clskey = clskey from testklup where nlskey = 1 select @ cont2 = cont2 from testklup where clskey = @ clskeyend
 

 

B. By changing the isolation level, the optimistic concurrency mode is used. The source row does not need to be locked during read operations.

 
Declare @ cont2 char (3000) while 1 = 1 begin select @ cont2 = cont2 from testklup with (nolock) Where nlskey = 1end
 

 

Conclusion: when solving the problem, we 'd better find out the root cause of the problem and find a solution suitable for our environment through the problem points before implementation.