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

This article will explain in detail how to achieve deadlock in SQLServer, the content of the article is of high quality, so the editor will share it for you as a reference. I hope you will have some understanding of the relevant knowledge after reading this article.

How does SQLServer deadlock?

Business scenarios for stress testing:

1. Simulated users submit applications

A) tables involved

i. Apply for the main form and generate one piece of data at a time.

ii. Details of the doctors applying for. One application includes multiple doctors, and one application contains 100 doctors.

iii. Details of individual doctors, one piece of data for each doctor

To sum up, 201 pieces of data need to be inserted in the creation of an application.

B) Application logic

i. First call save

ii. Then execute the submission logic

All kinds of logic verification, which is caused by historical reasons (the confusion of the code logic of a project that has been maintained for several years is unimaginable), always do a double comparison when submitting data, such as whether the data has the logic of duplicate rows. Here, the application doctor form and doctor details are read repeatedly. This is also the main cause of deadlocks, and this scenario is sufficient for reading and modifying the same table at the same time. In addition, data is inserted into other tables, such as status tracking information, emailing, and so on.

How does SQLServer deadlock?

As for why it is divided into two logic to deal with the requirements of the same action, it has been tested by the original designer, these logic includes a variety of EntityFramwork query writing, it is difficult to do effective optimization.

two。 Pressure setting

A) concurrent 8 users

B) add 5 users every 1 minute

Lowering the transaction isolation level to readuncommitted does not eliminate deadlocks, but the number of deadlocks is reduced, mainly the number of deadlocks caused by shared locks.

Piecewise analysis can also be said to be exclusion. Only execute part of the logic, for example, our above application is divided into two steps, first save and then submit, only save the result is still deadlock.

Looking for the method of deadlock tracking, trying to find the essential cause of deadlock. I simply translated some articles written by MVP according to my own understanding, which you can refer to.

SQL deadlock-type of lock

This paper explains the various locks in SQL and the compatibility between them. Only by knowing these can we know the scenario in which the lock occurs. For example, knowing that the shared locks are compatible can immediately reflect that the pure read operation will not block.

SQL deadlock-Lock and transaction level

Different transaction isolation levels affect the behavior of locks, which shows that lowering the transaction isolation level does not eliminate deadlocks.

SQL deadlock-blocking

Only when there is a blockage will it be upgraded to a deadlock, so understanding blocking is the first thing to do.

SQL deadlock-blocking detection

This article explains how to track and analyze the essential causes of deadlocks in two ways, and the relevant information about deadlocks can be easily derived through the performance monitoring workers included in SQL.

SQL deadlock-Why is there a deadlock

This article explains the principle of deadlock in great detail.

The important conclusion of the deadlock article:

Most deadlocks are caused by unoptimized queries, but because our project has too much logic in processing this application, it is unlikely to be effectively optimized in a short period of time. So I temporarily adopted a scheme that may not be very good, that is, to find a way to reduce the competition among exclusive locks. To put it simply, it is the logic of using certain means to avoid concurrent calls to update or insert data in the program.

Side door solution:

Through a ticket queue to solve the concurrency of data insertion and updates, the principle is that when a thread wants to insert data, it first takes the ticket and then queues, when it is its turn to perform database operations, and when other threads are executing, we queue up through our own family locks. This method solves the deadlock problem programmatically, but it is not recommended. This unconventional method is also subject to the logic of existing programs.

About how to achieve deadlock in SQLServer to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.