First, deal with the idea of deadlock resolution

Treatment ideas: prevention to avoid to detect to remove.

If you can prevent it, it’s best to avoid it if you can, and if you detect a deadlock, try to resolve it as soon as possible.

Four elements of deadlock

  1. Exclusive condition: a resource can only be used by one thread at a time.
  2. Request and hold conditions: when a process is blocked by requesting resources, it holds on to acquired resources.
  3. Non-dispossession condition: a process cannot forcibly take away a resource it has acquired until it is used up.
  4. Circular waiting condition: a circular waiting resource relationship is formed between several processes.

Deadlock problems can be solved by breaking any one of the four necessary conditions for deadlock. But in a real world scenario, we can’t break mutual exclusion, and to solve the deadlock problem, we need to break the other three conditions.

Three, deadlock detection algorithm:

There are three processes and four resources, and each data represents the following:

  • E vector: total resources
  • Vector A: residual resources
  • C matrix: The number of resources owned by each process. each row represents the number of resources owned by a process
  • R matrix: The number of resources requested by each process

If the resources requested by process P1 and P2 are not satisfied, only process P3 can execute, and then release the resources owned by process P3, A = (2 2 20 0). P2 can execute the command. After the command is executed, resources owned by P2 are released. A = (4 2 2 1). P1 can also be executed. All processes execute without deadlock.

Deadlock detection algorithm is summarized as follows:

Each process is initially unmarked, and execution may be marked. When the algorithm ends, any process that is not marked is a deadlocked process.

  1. Find an unmarked process Pi that requests resources less than or equal to A.

  2. If such A process is found, add the i-th row vector of the C-matrix to A, mark the process, and revert to 1.

  3. If there is no such process, the algorithm terminates.

MySQL > update MySQL > update MySQL > update MySQL

  1. The relationship between InnoDB isolation levels, indexes, and locks assumes a message table (MSG) with three fields. Id is the primary key, token is a non-unique index, and Message has no index. Innodb uses a clustered index for primary keys, which is a data storage method where table data is stored along with primary keys and primary key leaves store row data. For a normal index, the leaf node stores the primary key.

  2. Relationship between indexes and locks

    Delete from MSG where ID =2; Because of the deletion by ID, a row lock is created that locks a single record.

    Delete from MSG where token= ‘CVS ‘; Since the token is a secondary index, the record corresponding to the secondary index is locked, which is a total of two rows.

    1. Delete from MSG where message= ‘order number’;

  3. Phantom and gap locking transaction A gets one record on the first query, but two records on the second query. Damn it from transaction A’s point of view! In fact, this is unreal read, although the line lock, but still can not avoid the unreal read.Repeatable Read (RR) InnoDB default isolation level is Repeatable read (RR), the so-called Repeatable read means that the same field is read for many times, unless the data is modified by the transaction itself, it can prevent dirty read and unrepeatable read, but phantom read is still possible. How to solve the illusion, it is necessary to use gap lock.

    Update MSG set message= ‘order’ where token= ‘asD’; Because token is a secondary index, in addition to placing an X lock on the index’s record, it also places a lock on the interval between the ‘ASD’ and the two adjacent indexes.Insert into MSG values (null, ‘asD ‘,’ hello ‘); commit; At this time, the asD has been locked, so the execution cannot be performed immediately and the gap lock needs to be released. This avoids the illusion problem.

How to avoid deadlocks as far as possible

In actual application scenarios, deadlocks cannot be avoided 100%, but can only be reduced as far as possible by following specifications:

  • Table and row data are accessed in a fixed order. For example, in a scenario where two jobs are updated in batches, sort the IDS first and then execute them. In this way, cross-lock waiting is avoided. In addition, you can avoid deadlocks by adjusting the SQL order of the two transactions to be consistent.
  • Break big things down into small things. Large transactions tend to be deadlocked. If business permits, try to break large transactions into small transactions to reduce the large volume of data update operations of a transaction.
  • In the same transaction operation, try to lock all the resources needed at one time, and do not be preempted by other things to reduce the deadlock probability.
  • Update as much as possible by primary key ID or index. As you can see, if the index or ID is not moved, a full table scan will be performed, and locks will be added to each row, which can easily cause deadlocks.
  • Reduce the isolation level of things. This method has a wide impact. If business permits, it is a good choice to lower the isolation level. For example, changing the magic isolation level from RR to RC can avoid many deadlocks caused by gap locks.

This article was created and shared by Mirson. For further communication, please add to QQ group 19310171 or visit www.softart.cn