Simultaneous select and UPDATE statements during high concurrency in SQL Server deadlock problem (ii)

SQL Server deadlock makes the problem that we often encounter, the deadlock of database operation is unavoidable, this article does not intend to discuss how deadlocks are produced, the focus is to resolve deadlocks. Hopefully it will help you learn about the deadlock in SQL Server.

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

<span style= "FONT-SIZE:18PX;" ><span style= "FONT-SIZE:18PX;" >create table Testklup (clskey int NOT NULL, Nlskey int. NOT NULL, CONT1 int NOT null, Cont2 char) create unique Clustered index inx_cls on Testklup (clskey) Create unique nonclustered index Inx_nlcs on Testklup (Nlskey) include (CONT1) in SERT into Testklup Select 1,1,100, ' AAA ' Insert to Testklup select 2,2,200, ' BBB ' insert into Testklup select 3,3,300, ' CCC ' & Lt;/span></span>

Open Session 1 Simulate high frequency update operation

----Analog High Frequency update operation

<span style= "FONT-SIZE:18PX;" ><span style= "FONT-SIZE:18PX;" >declare @i intset @i=100while 1=1 begin update Testklup set [email protected] where Clskey=1 set @[email protected]+1 End</span></span>

Open Session 2 analog High frequency select operation

----Analog High Frequency Select operation

<span style= "FONT-SIZE:18PX;" ><span style= "FONT-SIZE:18PX;" >declare @cont2 char (in) while 1=1begin  select @cont2 =cont2 from Testklup where nlskey=1end</span></ Span>

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

The following deadlock diagram is captured in the trace we opened. 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.

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

<span style= "FONT-SIZE:18PX;" ><span style= "FONT-SIZE:18PX;" >create nonclustered INDEX [inx_nlskey_incont2] on [dbo]. [Testklup] ([Nlskey] ASC) INCLUDE ([Cont2]) </span></span>

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

<span style= "FONT-SIZE:18PX;" ><span style= "FONT-SIZE:18PX;" >declare @cont2 Char declare @clskey intwhile 1=1 begin  Select @clskey =clskey from Testklup where Nlskey=1
   select @cont2 =cont2 from Testklup where [email protected] end</span></span>

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

<span style= "FONT-SIZE:18PX;" ><span style= "FONT-SIZE:18PX;" > Declare @cont2 char (in) while 1=1 begin     Select @cont2 =cont2 from Testklup with (NOLOCK)  where Nlskey=1 end & Lt;/span></span>

Concluding remarks. When solving a problem, it is 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.

Simultaneous select and UPDATE statements during high concurrency in SQL Server deadlock problem (ii)