What are the causes and solutions of database deadlocks?

Deadlock

The so-called deadlock: refers to two or more processes during the execution process, due to contention A phenomenon of mutual waiting caused by resources. Without external force, they will not be able to advance. At this time, the 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.

Since resource occupation is mutually exclusive, when a process applies for resources, the relevant process will never be allocated the necessary resources and cannot continue to run without external assistance. This creates a situation. Special phenomenon deadlock.

A situation in which two or more threads in the execution program are permanently blocked (waiting), and each thread is waiting for resources that are 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 a computer system, if the system's resource allocation strategy is improper, or more commonly, the program written by the programmer has errors, etc., it will cause the process to deadlock due to improper competition for resources.

There are many ways to implement locks, such as intention locks, shared-exclusive locks, lock tables, tree protocols, timestamp protocols, etc. Locks also have multiple granularities. For example, you can lock on a table or on a record.

The main reasons for deadlock are:

(1) Insufficient system resources.

(2) The order of process running and advancement is inappropriate.

(3) Improper allocation of resources, etc.

If the system resources are sufficient and the resource requests of the process can be satisfied, the possibility of deadlock is very low, otherwise it will fall into a deadlock due to competition for limited resources. Secondly, if the process running order and speed are different, deadlock may also occur.

Four necessary conditions for deadlock:

(1) Mutual exclusion condition: A resource can only be used by one process at a time.

(2) Request and hold conditions: When a process is blocked due to requesting resources, it will keep the obtained resources.

(3) Non-deprivation condition: The resources that the process has obtained cannot be forcibly deprived of them before they are used up.

(4) Loop waiting condition: A head-to-tail loop waiting resource relationship is formed between several processes.

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

Prevention and relief of deadlocks:

Understanding the causes of deadlocks, especially the four necessary conditions for producing deadlocks, you can avoid them as much as possible. Prevent and resolve deadlocks. Therefore, in terms of system design, process scheduling, etc., pay attention to how to prevent these four necessary conditions from being established, and how to determine a reasonable resource allocation algorithm to avoid processes permanently occupying system resources.

In addition, it is also necessary to prevent the process from occupying resources while it is in a waiting state. During the system running process, each resource request issued by the process that the system can satisfy is dynamically checked, and a decision is made based on the check results whether Allocate resources. If the system may deadlock after allocation, it will not be allocated, otherwise it will be allocated. Therefore, the allocation of resources must be properly planned.

How to minimize deadlocks

Although deadlocks cannot be completely avoided, the number of deadlocks can be minimized. Minimizing deadlocks increases transaction throughput and reduces system overhead because few transactions are rolled back, which undoes all the work performed by the transaction. Resubmitted by application due to rollback in case of deadlock.

The following methods help minimize deadlocks:

(1) Access objects in the same order.

(2) Avoid user interaction in transactions.

(3) Keep transactions short and in a batch.

(4) Use low isolation level.

(5) Use binding connection.

1. 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 acquire a lock on the Supplier table and then acquire a lock on the Part table, one transaction is blocked on the Supplier table until the other transaction completes. After the first transaction commits or rolls back, the second transaction continues. No deadlock occurs. Using stored procedures for all data modifications standardizes the order in which objects are accessed.

2. Avoid user interaction in transactions

Avoid writing transactions that include user interaction, because running batches without user interaction is much faster than users manually responding to queries. For example, reply to the application's prompt for request parameters. For example, if a transaction is waiting for user input and the user goes to lunch or even goes home for the weekend, the user suspends the transaction and cannot complete it. This will reduce the throughput of the system because any locks held by the transaction will not be released until the transaction commits or rolls back. Even if a deadlock does not occur, other transactions accessing the same resource will be blocked, waiting for the transaction to complete.

3. Keep transactions short and in a batch

Deadlocks usually occur when multiple long-running transactions are executed concurrently in the same database. The longer a transaction runs, the longer it holds an exclusive or update lock, blocking other activity and possibly causing a deadlock.

Keeping transactions in a batch can minimize the amount of network communication round-trips for transactions, reduce possible delays in completing transactions, and release locks.

4. Use low isolation level

Determine whether the transaction can run at a lower isolation level. Performing a committed read allows a transaction to read data that has been read (unmodified) by another transaction without having to wait for the first transaction to complete. Using a lower isolation level (such as Read Committed) instead of a higher isolation level (such as Serializable Read) reduces the time a shared lock is held, thereby reducing lock contention.

5. Use bound connections

Use bound connections so that two or more connections opened by the same application can cooperate with each other. Any lock acquired by a secondary connection can be held like a lock acquired by the primary connection and vice versa, so they do not block each other.

6. Use stored procedures to find out the process and SQL statement that caused the deadlock

If a deadlock occurs, how do we detect which SQL statement or stored procedure caused the deadlock? ? At this time, we can use the following stored procedure to detect and find out the process and SQL statement that caused the deadlock.

Related recommendations: "mysql tutorial"

The above is the detailed content of What are the causes and solutions of database deadlocks?. For more information, please follow other related articles on the PHP Chinese website!