Shulou(Shulou.com)05/31 Report--

How to deeply analyze the problem of key search deadlock when mssql accesses with high frequency and high concurrency? I believe that many inexperienced people don't know what to do about it. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

Deadlock is no stranger to DBA or database developers, and its causes are various. generally speaking, database application developers will have certain considerations in the design to avoid deadlock as far as possible. But sometimes because of some special application scenarios such as high-frequency queries and potential problems in database design under high-concurrent queries, some deadlocks that are not easy to capture may affect the business. Here we introduce the key search deadlock and related solutions caused by design problems.

Here we open the trace profiler tracking deadlock view (locks:deadlock graph) while testing. (of course, you can also turn on trace tags, 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 1meme 1mlg100 minaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

Open session 1 to simulate high frequency update operation

-Analog high frequency update operation

Declare @ I intset @ i=100while 1 begin update testklup set cont1=@i where clskey=1 set @ i=@i+1 end

Open session 2 to simulate high frequency select operation

-Analog high frequency select operation

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

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

Figure 1-1

Figure 1-2

Figure 1-2

Deadlock analysis: it can be seen that while the read process (108) requests the X lock held by the write process (79) is blocked, the write process (79) applies for the S lock held by the read process (108).

(because under the default isolation level (read commit) to read the application S lock is only an instant process, it will be released immediately after reading, and will not wait for the transaction to be completed), so it is not easy to occur in the case of concurrency and low execution frequency. However, the high-frequency situation we simulate makes the S-lock acquisition frequency very high, and only two sessions, one read and one write, cause deadlock.

Figure 1-3

Figure 1-4 cause of deadlock: additional lock (clustered index) requirement caused by key lookup in read operation

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

We can start from the following aspects.

Cancel the acquisition lock during b read operation

A.1 We can create an override index to include the query columns 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, execute step by step, get the query column through the clustered index to avoid key search.

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

B by changing the isolation level and using optimistic concurrency mode, the source line does not need to be locked during read operation

Declare @ cont2 char (3000) while 1=1begin select @ cont2=cont2 from testklup with (nolock) where nlskey=1end, after reading the above, do you know how to deeply analyze the deadlock problem of mssql key search during high-frequency and high-concurrency access? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.