Analysis of purge deadlock in MySQL database

Purge deadlock

Scenario description

Purge deadlock description

There are records (unique keys) 10, 20, 30, 40 (with self incrementing primary key) in the table. Now delete record 20 and submit the transaction. The purge thread has not recycled the record at this time, and insert a new record 20 at this time.

Review the insertion process. The complete insertion process is as follows:

Suppose there are records 10, 30, 50, 70; If it is a unique key, record 25 needs to be inserted.

1. Find records less than or equal to 25. Here is 10

If record 25 already exists in the record with uniqueness constraints, it is necessary to add s gap lock to record 25 (in the case of purge, the reason for adding s lock to old record 20 *)

The reason why you do not directly report an error to exit or prompt that it already exists is that the previous record 25 may be marked as delete mark and wait for purge

If it is assumed that there is no s gap lock here and there is no lock on record 30, two 25 can be inserted according to the following steps, thus breaking the uniqueness constraint

2. Find the next record of record 10, here is 30

3. Judge whether there is a lock on the next record 30 (if there is a case = 25, it will be discussed later)

If there is no lock on judgment 30, it can be inserted

If there is a record lock on judgment 30, it can be inserted

Judge that if there is gap lock / next key lock on 30, it cannot be inserted, Because the range of the lock is (10,30) / (10,30]; add the insert intent lock on 30 (in the waiting state at this time), when the gap lock / next key lock is released, the waiting transaction will be awakened. At this time, the insert intent lock can be obtained on record 30, and then record 25 can be inserted

In this scenario, the newly inserted record 20 is equal to the existing record 20 * with unique constraints. At this time, it is necessary to add s lock (with gap) to the record 20 *

demonstration

Because the old record 20 must exist when inserting record 20 * is simulated, use the debug version to stop the purge thread.

1. Because it is a unique index, it is necessary to check the uniqueness. Start from the old record 20 * (the first is less than or equal to its own value), then add an s lock to the 20 *, then check down to the first unequal record, that is, record 30, and then exit, but this record 30 also needs to be added with s lock

2. When inserting a new record 20, if it is found that there is a lock on the next record 30, the implicit lock when you insert it will be promoted to a display lock (see the insertion step)

3. The current locking range is (10,20], (20,30]

4. The newly inserted record 20 itself is an S-GAP lock (the previous 20 * has an S-lock. Because it is a unique index, it does not need a record lock, and gap is enough)

Therefore, record 25 cannot be inserted (lock waiting)

In this example, if there is a lock waiting, you should be vigilant. If two things wait for each other, it is a deadlock

summary

The above is the analysis of purge deadlock in MySQL database introduced by Xiaobian. I hope it will help you. If you have any questions, please leave me a message and Xiaobian will reply to you in time. Thank you very much for your support for the programming tips website!