Hands-on SQL Server deadlock

Now that we have this requirement, we need to have some knowledge about deadlocks to solve deadlocks. For non-DBAs, we do not need to know much about them, you just need to know the basic concepts and common analysis methods. After all, we don't have to rely on this to eat, and we don't have to go into a very fine realm. Here I found a system blog written by Microsoft MVP, which is very important for me to understand deadlocks. Here I will share my current solutions to solve deadlocks.

Business scenarios of stress testing:

1. submit an application by simulating a user

A) tables involved

I. Apply for a master table and generate a data entry at a time.

Ii. Details of the applied doctor. One application contains multiple doctors and one application contains 100 doctors.

Iii. Details of a single doctor and detailed data of each doctor

For the creation of the preceding application, 201 data records must be inserted.

B) application logic

I. Call save first

Ii. then execute the submission Logic

Logic verification is caused by historical reasons (it is hard to imagine that a project code logic that has been maintained for several years is confusing). It always performs double verification when submitting data, for example, if the data has the logic of repeated rows, the application form and the doctor details are read repeatedly. This is also the main cause of the deadlock. In this scenario, the same table can be read and modified at the same time. In addition, data will be inserted into other tables, such as status tracking information and sending emails.

As to why it is split into two logics to deal with the original requirement of the same action, we have already been tested by the original designers. These logics include the query methods of various EntityFramwork, it is difficult to make effective optimization.

2. Stress settings

A) 8 concurrent users

B) add 5 users every minute

Methods you have learned:

• [Translation]: SQL deadlock-lock type

Explains various locks in SQL and their compatibility. Only by understanding these locks can we know the scenarios where locks occur, for example, if we know that the shared locks are compatible, we can immediately reflect that pure read operations will not be blocked.

• [Translation]: SQL deadlock-lock and transaction level

Changing the transaction isolation level will affect the lock action. It is important to explain that the deadlock cannot be eliminated when the transaction isolation level is reduced.

• [Translation]: SQL deadlock-blocking

Only when blocking occurs will it be upgraded to a deadlock. It is the first thing to know about blocking.

• [Translation]: SQL deadlock-blocking Detection

This article describes how to track and analyze the essential causes of deadlocks in two ways. It also allows you to easily export information related to deadlocks through the performance monitoring engineers that come with SQL.

• [Translation]: SQL deadlock-Why is there a deadlock?

This article describes in detail how deadlocks are generated.

Conclusion:

Most deadlocks are caused by unoptimized queries. However, because our project has too many logic to process this application, it is unlikely that we can effectively optimize it in a short time, so I have adopted a scheme that may not be very good, that is, to reduce the competition between the exclusive locks, to put it simply, you can avoid calling the update or insert data logic concurrently in a program by some means.

Partial door solution:

Solve the concurrency of data insertion and update through a queue of ticket receiving queues. The principle is that when a thread wants to insert data, it first obtains the ticket and then queues. When the number is its turn, it can perform database operations, when other threads are running, we use a self-family lock to implement queuing. This method solves the deadlock problem in the largest program, but it is not recommended to do so. The use of this unconventional method is also subject to the logic of the existing program. If you have solved the deadlock problem in EF, share it with me.

 
 

  
  
public   int AddWithSpinLock(ObjectModel.Request svarRequest) 
  
  
 { 
  
  
     bool lockTaken = false; 
  
  
     svarRequest.Ticket = Guid.NewGuid(); 
  
  
     var newRequestId = 0; 
  
  
     try 
  
  
     { 
  
  
         _spinlock.Enter(ref lockTaken); 
  
  
         _queue.Enqueue(svarRequest); 
  
  
         while (null != _queue && _queue.Count > 0 && _queue.Peek().Ticket == svarRequest.Ticket) 
  
  
         { 
  
  
             // do something<br>                    _queue.Dequeue(); 
  
  
             return newRequestId; 
  
  
         } 
  
  
     } 
  
  
     catch (Exception ex) 
  
  
     { 
  
  
         if (lockTaken) _spinlock.Exit(false); 
  
  
         _queue.Dequeue(); 
  
  
         throw ex; 
  
  
     } 
  
  
     finally 
  
  
     {                
  
  
         if (lockTaken) _spinlock.Exit(false); 
  
  
     } 
  
  
     return newRequestId; 
  
  
 }