Mysql database deadlock causes and solutions _ MySQL

This article mainly introduces the cause and solution of the mysql database deadlock. For more information, see Deadlock (Deadlock)

Deadlock: A deadlock occurs when two or more processes compete for resources during execution. if there is no external force, they will not be able to proceed. It is said that the system is 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 usage is mutually exclusive, after a process applies for resources, the relevant process will never be allocated with necessary resources and cannot continue to run without external assistance, this produces a special deadlock. In one case, two or more threads in the execution program are permanently blocked (waiting), and each thread is waiting for resources occupied and blocked by other threads. For example, if thread A Locks record 1 and waits for record 2, and thread B locks record 2 and waits for record 1, A deadlock occurs between the two threads. In computer systems, if the system's resource allocation policy is inappropriate, it is more common that the program written by the programmer has errors and so on, it will lead to deadlocks caused by improper competition of resources. Locks can be implemented in multiple ways, such as intention locks, share-exclusive locks, lock tables, tree-like protocols, and timestamp protocols. The lock also has multiple granularities. for example, you can lock a table or a record.

The cause of the deadlock is as follows:

(1) insufficient system resources.
(2) the order in which the process is promoted is inappropriate.
(3) improper resource allocation.

If the system resources are sufficient, all process resource requests can be satisfied, and the possibility of deadlock is very low. Otherwise, a deadlock will occur due to competition for limited resources. Second, the process may run in different order and speed, and may also lead to deadlocks.

Four conditions for deadlock:

(1) mutex condition: a resource can only be used by one process at a time.
(2) request and retention conditions: when a process is blocked by requesting resources, it will not release the obtained resources.
(3) non-deprivation condition: the resources obtained by the process cannot be forcibly deprived before they are used.
(4) cyclic waiting condition: a cyclic waiting resource relationship is formed between several processes that are connected at the beginning and end.

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

Deadlock prevention and removal:

Understanding the cause of the deadlock, especially the four necessary conditions for the deadlock, can avoid, prevent and remove the deadlock as much as possible. Therefore, in terms of system design and process scheduling, pay attention to how to prevent these four necessary conditions from being established and how to determine reasonable resource allocation algorithms to avoid permanent occupation of system resources by processes. In addition, it is necessary to prevent the process from occupying resources while waiting. during system operation, it dynamically checks the resource applications that each system can meet by the process, determine whether to allocate resources based on the check results. if the system may experience a deadlock after allocation, no allocation will be made; otherwise, the allocation will be made. Therefore, reasonable planning should be made for resource allocation.
How to minimize deadlocks

Although deadlocks cannot be completely avoided, the number of deadlocks can be minimized. Reducing deadlocks to a minimum can increase transaction throughput and reduce system overhead, because only a few transactions are rolled back, and rollback will cancel all the work performed by the transaction. The application resubmit the rollback during the deadlock.

The following methods help minimize deadlocks:

(1) access objects in the same order.
(2) avoid user interaction in transactions.
(3) keep the transaction brief and process it in a batch.
(4) use a low isolation level.
(5) Use the bound connection.

Access Objects in the same order

If all concurrent transactions access objects in the same order, the possibility of deadlock will be reduced. For example, if two concurrent transactions obtain the lock on the Supplier table and then obtain the lock on the Part table, the other transaction is blocked on the Supplier table before one transaction is completed. After the first transaction is committed or rolled back, the second transaction continues. No deadlock occurs. The stored procedure is used to modify all the data to standardize the order of objects to be accessed.

Avoid user interaction in transactions

Avoid writing transactions that contain user interaction, because running batch processing without user interaction is much faster than manually responding to queries, for example, responding to application request parameter prompts. For example, if the transaction is waiting for user input, and the user goes to lunch or even goes home for a weekend, the user suspends the transaction so that it cannot be completed. This will reduce the system throughput, because any lock held by the transaction will be released only when the transaction is committed or rolled back. Even if no deadlock occurs, other transactions accessing the same resource will be blocked, waiting for the transaction to complete.

Keep the transaction brief and in a batch

A life-and-death lock is often used to concurrently execute multiple transactions that require long running in the same database. The longer the transaction runs, the longer it takes to hold the exclusive lock or update the lock, blocking other activities and possibly causing a deadlock.

Keeping transactions in one batch can minimize the number of network communications to and from the transaction, reduce the possible latency of completing the transaction and release the lock.

Low isolation level

Determine whether the transaction can run at a lower isolation level. The committed read operation allows the transaction to read the data that has been read (not modified) by another transaction without waiting for the completion of the first transaction. Using a lower isolation level (for example, commit read) instead of a higher isolation level (for example, serializable read) can shorten the time for holding shared locks, thus reducing lock contention.

Bind a connection

Bind the connection so that two or more connections opened by the same application can cooperate with each other. Any lock obtained by the secondary connection can be held as the lock obtained by the primary connection, and vice versa, so it will not block each other.