Shulou(Shulou.com)06/01 Report--

This article mainly introduces how the database deadlock is generated, which has a certain reference value, and friends who need it can refer to it. I hope you will learn a lot after reading this article. Next, let the editor take you to learn about it.

Deadlock (Deadlock)

The so-called deadlock: refers to two or more processes in the implementation process, caused by the competition for resources caused by a mutual waiting phenomenon, if there is no external force, they will not be able to move forward. At this point, it is said that the system is in a deadlock state or the system has a deadlock, and these processes that are always waiting for each other are called deadlock processes.

Because the occupation of resources is mutually exclusive, when a process applies for resources, the process can never allocate the necessary resources and can not continue to run without external assistance, which leads to a special phenomenon deadlock.

A situation in which two or more threads in the executing 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, while thread B locks record 2 and waits for record 1, a deadlock occurs between the two threads.

In a computer system, if the resource allocation strategy of the system is improper, it may be more common that there are errors in the program written by the programmer, which will lead to the deadlock of the process due to 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 granularities, such as locking on a table or locking on a record.

The main reasons for deadlocks are:

(1) lack of system resources.

(2) the order in which the process runs and advances is not appropriate.

(3) improper allocation of resources.

If the system resources are sufficient and the resource requests of the process can be met, the possibility of deadlock is very low, otherwise it will fall into a deadlock because of competing for limited resources. Secondly, the process runs in different order and speed, which may also lead to deadlock.

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 retention conditions: when a process is blocked by a request for resources, it holds on to the resources it has acquired.

(III) non-deprivation conditions: resources that have been acquired by the process cannot be forcibly deprived until they have been used up.

(4) cyclic waiting condition: a circular waiting resource relationship is formed between several processes.

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

Prevention and release of deadlocks:

By understanding the causes of deadlocks, especially the four necessary conditions for deadlocks, deadlocks can be avoided, prevented and released as much as possible. Therefore, in the aspects of system design and process scheduling, we should pay attention to how to prevent these four necessary conditions from being established, and how to determine the reasonable allocation algorithm of resources so as to prevent the process from occupying system resources permanently.

In addition, it is also necessary to prevent the process from occupying resources when it is in a waiting state. during the operation of the system, dynamically check each resource request that the system can meet by the process, and decide whether to allocate resources according to the inspection results. if deadlock may occur in the system after allocation, it will not be allocated, otherwise it will be allocated. Therefore, reasonable planning should be given to the allocation of resources.

How to minimize deadlocks

Although deadlocks cannot be completely avoided, the number of deadlocks can be minimized. Reducing deadlocks to a minimum increases transaction throughput and reduces system overhead because there are very few transaction rollbacks, which cancel all work performed by the transaction. Resubmitted by the application because it is rolled back during a 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 a low isolation level.

(5) use binding to connect.

1. Access objects in the same order

If all concurrent transactions access objects in the same order, deadlocks are less likely to occur. For example, if two concurrent transactions acquire a lock on the Supplier table and then the lock on the Part table, the other transaction is blocked on the Supplier table before one transaction completes. After the first transaction is committed or rolled back, the second transaction continues. No deadlock occurs. Using stored procedures for all data modifications standardizes the order of access objects.

2. Avoid user interaction in transactions

Avoid writing transactions that include user interaction, because batches without user interaction run much faster than users can manually respond to queries, such as prompts for application 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 so that it cannot be completed. This reduces the throughput of the system because any locks held by the transaction are released only when the transaction is committed or rolled back. Even if there is no deadlock, other transactions accessing the same resource are blocked, waiting for the transaction to complete.

3. Keep the transaction 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 takes to hold exclusive or updated locks, blocking other activities and possibly causing deadlocks.

Keeping the transaction in a batch can minimize the network traffic round trip of the transaction, reduce the possible delay in completing the transaction, and release the lock.

4. Use a low isolation level

Determines whether the transaction can run at a lower isolation level. Performing commit reads 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 commit reads) rather than a higher isolation level (such as serially readable) can reduce the time it takes to hold a shared lock, thereby reducing lock contention.

5. Use binding to connect

Use bind connections to enable two or more connections opened by the same application to 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 it does not block each other.

6. Use stored procedures to find out the processes and SQL statements that caused the deadlock

If a deadlock occurs, how can we detect which SQL statement or stored procedure is deadlocked? At this point, we can use the following stored procedure to detect the process and SQL statement that caused the deadlock.

Thank you for reading this article carefully. I hope it will be helpful for everyone to share the content of database deadlock. At the same time, I also hope you can support us, pay attention to the industry information channel, and find out if you have any problems. detailed solutions are waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.