MySQL database deadlock reason and solution _mysql

Deadlock (deadlock)

The so-called deadlock: refers to two or more than two processes in the implementation process, as a result of competing for resources caused by a mutual wait for the phenomenon, without external forces, they will not be able to push down. It is said that the system is in a deadlock state or the system produces a deadlock, and these processes that are always waiting for each other are called deadlock processes. Because the resource occupation is mutually exclusive, when a process to apply for resources, so that the process without external assistance, can never allocate the necessary resources and can not continue to run, which produces a special phenomenon deadlock. A situation in which two or more threads in the execution program are permanently blocked (waiting), and each thread waits for resources that are occupied and blocked by other threads. For example, if thread a locks up record 1 and waits for record 2, and thread B locks records 2 and waits for a record of 1, a deadlock occurs on two threads. In computer systems, if the resource allocation strategy of the system is improper, it is more common that programmers write programs with errors and so on, which leads to the process of deadlock caused by the improper competition resources. Locks are implemented in a variety of ways, such as intent locks, shared-exclusive locks, lock tables, tree protocols, timestamp protocols, and so on. Locks are also available in a variety of granularity, such as locking on a table or locking the record.

Deadlocks are caused mainly by:

(1) Insufficient system resources.
(2) The sequence of the process of moving forward is unsuitable.
(3) Improper allocation of resources.

If the system has sufficient resources, the process's resource requests can be met, the likelihood of deadlock is very low, otherwise, because of the contention for limited resources and fall into deadlock. Second, the process is running in a different order than the speed, may also produce deadlock.

Four necessary conditions to produce a deadlock:

(1) Mutually exclusive condition: A resource can only be used by one process at a time.
(2) Request and retention conditions: a process that is blocked by requesting resources, retains the resources that have been acquired.
(3) Non-deprivation of conditions: the resources that the process has acquired cannot be forcibly deprived until the end of use.
(4) Cyclic waiting condition: the process of forming a kind of end-to-end circular waiting resource relationship between several processes.

These four conditions are necessary for deadlock, as long as the system is deadlocked, these conditions must be set up, and as long as one of the above conditions is not satisfied, the deadlock will not occur.

Prevention and release of deadlocks:

Understanding the causes of deadlocks, especially the four necessary conditions for deadlocks, is the most likely way to avoid, prevent, and unlock deadlocks. Therefore, in the system design, process scheduling and so on to pay attention to how to not let these four prerequisites, how to determine the rational allocation of resources algorithm, to avoid the process of permanent occupy the system resources. In addition, you want to prevent the process from consuming resources while it is in a waiting state. In the process of system operation, the process issued by each system can meet the resource request for dynamic check, and according to the inspection results to decide whether to allocate resources, if the system may occur after the allocation of deadlock, will not be allocated, otherwise allocated. Therefore, the allocation of resources should be given reasonable planning.
How to minimize a deadlock

Although deadlocks cannot be completely avoided, the number of deadlocks can be minimized. Minimizing deadlocks can increase transaction throughput and reduce system overhead because only a few transactions are rolled back, and rollback cancels all work performed by the transaction. The application is resubmitted as a result of a deadlock rollback.

The following methods help you minimize deadlocks:

(1) Access the object in the same order.
(2) Avoid user interaction in a transaction.
(3) Keep the transaction short and in a batch.
(4) Use low isolation level.
(5) using a bound connection.

Access objects in the same order

If all concurrent transactions access the object in the same order, the likelihood of a deadlock is reduced. For example, if two concurrent transactions obtain a lock on the Supplier table and then obtain a lock on the part table, another 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. Deadlock does not occur. Using stored procedures for all data modifications can standardize the order in which objects are accessed.

Avoid user interaction in a transaction

Avoid writing transactions that contain user interaction, because batches that run without user interaction are much faster than users manually respond to queries, such as prompts to reply to application request parameters. For example, if a transaction is waiting for a user to enter, and the user goes to lunch or even goes home for the weekend, the user suspends the transaction and makes it impossible to complete. This reduces system throughput because any locks held by a transaction are released only when the transaction commits or rolls back. Even if a deadlock is not present, other transactions that access the same resource are blocked waiting for the transaction to complete.

Keep transactions short and in a batch

Deadlocks usually occur when multiple long-running transactions are executed concurrently in the same database. The longer the transaction is run, the longer it will hold the exclusive lock or update the lock, blocking other activities and possibly causing deadlocks.

Keep transactions in a batch, you can minimize the network traffic round-trip to the transaction, reduce the possible delay in completing the transaction, and release the lock.

Use low isolation Level

Determines whether a transaction can run at a lower isolation level. Performing a commit read allows the transaction to read data that has been read (unmodified) by another transaction without waiting for the first transaction to complete. The use of lower isolation levels (for example, commit reading) without high isolation levels (such as serializable reading) reduces the time to hold shared locks, thereby reducing lock contention.

Using Bound connections

Use a bound connection to enable two or more connections that are open by the same application to cooperate with one another. Any locks acquired by a secondary connection can be held in the same way as locks obtained by the primary connection, and vice versa, and therefore do not block each other.