Instance analysis database deadlock generation

Recently, the system prompts that "the transaction (process ID 54) and another process are deadlocked on the locked resource and have been selected as the deadlock victim. Run the transaction again ."

I have seen this before, but I have no idea where to start. Zhu always prompts that all SQL statements related to this table should be searched in the center of the table accessed by the wrong statement to see if deadlock may occur. In fact, when I hear this prompt, I am also confused. Later, I saw that the SQL statement that was sacrificed had three tables and was the most frequently used three tables. How much work would it take if I thought of Zhu Zong? In addition, I still have some details about the database deadlock. Isn't this a haystack?

The next day I tried to reproduce the deadlock and then find the SQL statements before and after the deadlock through the database. The range is much smaller (I can't tell you the details of the deadlock, of course, this is unexpected ).

The first step is to reproduce the deadlock. Just a long time ago, I wrote a robot to test the system concurrency, which could imitate multiple clients for regular operations. However, some days have not been updated. After modification for about two hours, the system finally ran up and worked well. 18 robots perform routine operations. I did some unconventional operations on the client myself. The client paused and immediately ran exec sp_who_lock.

The manual sorting is as follows:

54 update sale_object set objstateid = @ objstateid blocked by 86

71, 76, 86, 101 are 54 blocked select * From sale_objstate, sale_object, sale_subsvc

60. 66 is blocked by 71. Update sale_object set objstateid = @ objstateid

 

The meanings of "exclusive lock X" and "shared Lock S" are summarized as follows:

-- Select adds a shared lock to the readcommitted transaction and an exclusive lock to the repeatableread transaction;

-- The exclusive lock is always applied during insert, update, and delete operations;

-- Update can only be executed after the select transaction releases the shared lock to the exclusive lock;

-- If transaction t adds the S lock to the Data Object a, other transactions can only apply the S lock to abut not the X lock until t releases the S lock on.

 

Query the statements before calling the preceding SQL statement in the Code. The conclusions are as follows:
-- Process No. 86 readcommitted
86 insert sale_objstate obtain the exclusive sale_objstate lock
86 select sale_objstate: exclusive lock
86 Update requires an exclusive sale_object lock

-- Process 54 repeatableread
54 select * From sale_subsvc, sale_object, sale_objstate
Obtain the exclusive lock of sale_subsvc and sale_object in sequence. The exclusive lock of sale_objstate is required but is occupied by 86 and cannot be obtained.(Here, I learned the table lock in sequence based on the from statement in SQL, because as a program, it is impossible to lock three tables at the same time, so ......)

-- Process 71 is the same as process 86 (omitted here)

 

The 54 process uses high-level transactions, which is also the most critical aspect of deadlock.

Solution:

1. If the query of 54 processes in the program does not need to use the repeatableread transaction, change it to readcommitted transaction.

2. If the repeatableread transaction must be used in the program, you must switch the order of sale_object and sale_objstate in the SQL statement select * From sale_subsvc, sale_object, and sale_objstate. However, this change is also risky, because you do not know whether there are other locations that cause deadlocks. This solution is complicated and difficult to discuss in detail theoretically.

The purpose of this article is to let readers understand the details of database deadlocks. If you have any unclear descriptions, please point out that I will reply.

Because my skills are cainiao-level, please correct me if I understand something wrong in this article!