Causes and solutions of mysql database deadlock

Deadlock (Deadlock)

The so-called deadlock: refers to two or more processes in the execution of the process, due to the competition for resources caused by a phenomenon of waiting for each other, without the action of external forces, they will not be able to advance. When a system is said to be in a deadlock state or the system has a deadlock, these processes that are always waiting for each other are called deadlock processes. Because resource occupancy is mutually exclusive, when a process requests a resource, the process can never allocate the necessary resources without the assistance of external forces, so that it cannot continue to operate, resulting in a special phenomenon deadlock. A situation in which two or more threads in the executing program are permanently blocked (waiting), each waiting for a resource that has been occupied and blocked by another thread. For example, if thread A locks record 1 and waits for record 2, while thread B locks record 2 and waits for record 1, then both threads are deadlocked. In a computer system, if the system's resource allocation strategy is improper, or more commonly, the programmer may write a program with errors, etc., it will lead to the deadlock of the process due to the improper competition for resources. Locks can be implemented in many ways, such as intention locks, shared-exclusive locks, lock tables, tree protocols, timestamp protocols, and so on. Locks have a variety of granularity, such as locking on a table or locking on a record.

The main causes of deadlock are:

(1) Insufficient system resources.
(2) The sequence of process running and advancing is not appropriate.
(3) Improper resource allocation, etc.

If the system is adequately resoursed and the process's resource requests are met, deadlocks are less likely to occur, otherwise they will result in deadlocks as a result of competing for limited resources. Second, deadlocks can occur when processes run in different order and at different speeds.

Four necessary conditions for creating a deadlock:

(1) Mutual exclusion condition: a resource can only be used by one process at a time.
(2) Request and hold condition: When a process is blocked by requesting a resource, it will hold on to the acquired resource.
(3) Condition of not depriving: The resources obtained by the process cannot be deprived before the end of use.
(4) Cyclic wait condition: A kind of cyclic wait resource relationship with end-to-end connection is formed among several processes.

These four conditions are necessary for a deadlock. As long as a system deadlock occurs, these conditions must be true, but as long as 1 of the above conditions is not met, no deadlock will occur.

Prevention and release of deadlocks:

Understanding the cause of deadlocks, and in particular the four necessary conditions that cause them, is the best way to avoid, prevent, and release deadlocks. Therefore, in the system design, process scheduling and other aspects pay attention to how not to let the four necessary conditions hold, how to determine the reasonable allocation algorithm of resources, to avoid the process permanently occupy the system resources. In addition, also want to prevent process in the wait state under the condition of occupancy resources, in the process of system operation, the process of each one application for the resources of the system can satisfy the dynamic check and decide whether to allocate resources according to the results of the inspection, if the deadlock may occur after the distribution system, shall not be assigned, or to be assigned. Therefore, the allocation of resources should be given a reasonable plan.
How to minimize deadlocks

While deadlocks cannot be completely avoided, they can be minimized. Keeping deadlocks to a minimum increases transaction throughput and reduces overhead, because only a few transactions are rolled back, which cancels all work that the transaction performs. Recommit by an application due to rollback at deadlock time.

The following methods help to minimize deadlocks:

(1) Access objects in the same order as 1.
(2) Avoid user interaction in transactions.
(3) Keep transactions short and in 1 batch.
(4) Use a low isolation level.
(5) Use binding connection.

Access objects in the same order as 1

Deadlock is less likely if all concurrent transactions access objects in the same 1 order. For example, if two concurrent transactions acquire a lock on the Supplier table and then acquire a lock on the Part table, the other transaction is blocked on the Supplier table until one of the transactions completes. After the first transaction commits or rolls back, the second transaction continues. No deadlocks occur. Using stored procedures for all data modifications standardizes the order in which objects are accessed.

Avoid user interaction in transactions

Avoid writing transactions that involve user interaction, because batching without user interaction can be run much faster than a user can manually respond to queries, such as a prompt to respond to application request parameters. For example, if the transaction is waiting for user input and the user has gone to lunch or even gone home for the weekend, the user suspends the transaction so that it cannot be completed. This will reduce the throughput of the system because any locks held by the transaction will only be released when the transaction commits or rolls back. Even if a deadlock does not occur, other transactions accessing the same 1 resource will be blocked waiting for the transaction to complete.

Keep the transaction short and in 1 batch

Deadlocks typically occur when multiple long-running transactions are executed concurrently in the same 1 database. The longer a transaction runs, the longer it holds an exclusive lock or update lock, which blocks other activities and can lead to deadlocks.

Keeping the transaction in one batch minimizes the network traffic round trips to the transaction, reduces the possible delay in completing the transaction, and releases locks.

Use low isolation levels

Determines whether a transaction can run at a lower isolation level. Performing a commit read allows a transaction to read (unmodified) data from another transaction without having to wait for the first transaction to complete. Using a lower isolation level (such as commit reads) rather than a higher isolation level (such as serializable reads) can reduce lock contention by reducing the time a shared lock is held.

Use binding connections

Use binding connections so that two or more connections open with the 1 application can cooperate with each other. Any locks acquired by the secondary connection can be held as locks acquired by the primary connection, and vice versa, and therefore do not block each other.