Database Deadlock Causes and Optimization Schemes

Deadlock overview:

The Java language uses the synchronized keyword to ensure atomicity, because each Object has an implicit lock, which is also called a monitor object, which automatically acquires this internal lock before entering synchronized, and once it leaves this method, Whether it is completed or interrupted, the lock will be automatically released. Although this is an exclusive lock, each lock request is mutually exclusive. Compared with many advanced locks (Lock/ReadWriteLock, etc.), the cost of synchronized is higher than the latter. , but the syntax of synchronzied is relatively simple, and it is also easier to use and understand. Once the lock is called by the lock() method to obtain the lock and is not released correctly, it is likely to cause deadlock, so the release operation of Lock is always followed by the finally code. Inside the block, this is also an adjustment and redundancy in the code structure. It is impossible to avoid deadlocks even if synchronzied is used, so deadlocks are often prone to errors. The reasons and solutions for deadlocks are described below.

Deadlock description:

Deadlock is an error at the operating system level, short for process deadlock.

Deadlock refers to a situation in which multiple processes cyclically wait for resources occupied by other parties and remain in a stalemate indefinitely. If there is no external force, then each process involved in the deadlock will always be in a blocked state.

The deadlock problem is unique to multithreading, and it can be thought of as an extreme case where switching between threads consumes system performance. In deadlock, threads wait for each other's resources without releasing their own resources, resulting in endless waiting, and the result is that system tasks can never be completed. The deadlock problem is a problem that should be resolutely avoided and eliminated in multi-threaded development.

Cause of deadlock:

Generally speaking, the following conditions need to be met for a deadlock problem to occur:

Mutual exclusion condition: A resource can only be used by one thread at a time.
Request and hold condition: When a process is blocked by requesting a resource, it will hold on to the obtained resource.

No deprivation condition: The resource that the process has obtained cannot be forcibly deprived before it is used up.
Circular waiting condition: A cyclic waiting resource relationship is formed between several processes.
The deadlock problem can be solved by breaking any one of the four necessary conditions for deadlock.

Deadlock solution:

Deadlock is caused by four necessary conditions, so in general, as long as one of the four necessary conditions is violated, the deadlock situation should not occur.

If we want to break the mutual exclusion condition, we need to allow the process to access certain resources at the same time. This method is limited by the actual scene, and it is not easy to realize the condition;

To break the non-preemption condition, it is necessary to allow the process to forcibly seize some resources from the occupant, or simply understand that the process that occupies the resource can no longer apply for other resources, and must release the resources in hand before initiating the application, which is actually very important. Difficult to find applicable scenarios;

A process must apply for all resources before running, otherwise the process cannot enter the ready-to-execute state. This method seems to be useful, but its disadvantage is that it may lead to reduced resource utilization and process concurrency;

To avoid resource application loops, resources are classified and numbered in advance and assigned by number. This method can effectively improve resource utilization and system throughput, but increases system overhead and increases the resource occupation time of a process.

If we find a deadlock situation during the deadlock check, then we must try to eliminate the deadlock and make the system recover from the deadlock state. There are several ways to eliminate deadlocks:

The simplest and most common method is to restart the system, but this method is very expensive, it means that all the computing work that has been done by all processes before this will be wasted, including those involved in the deadlock. , and processes that are not involved in the deadlock;

Undo the process, depriving it of resources. Terminate the processes participating in the deadlock and reclaim the resources they hold, thereby releasing the deadlock. At this time, there are two situations: cancel all processes participating in the deadlock at one time and deprive all resources; or gradually cancel the processes participating in the deadlock and gradually recover the resources occupied by the deadlock process. Generally speaking, certain principles should be followed when choosing a process to be withdrawn gradually, the purpose is to withdraw those processes with the least cost, such as determining the cost of the process according to the priority of the process; considering the cost of running the process and external factors related to this process Factors such as the cost of the work;

The process rollback strategy is to let the process participating in the deadlock roll back to a certain point before the deadlock does not occur, and continue to execute from this point, so that the deadlock will no longer occur when it is executed again. Although this is an ideal method, it is extremely expensive to operate. It is impossible to have a mechanism such as a stack to record every change of the process for future rollback.

MySQL deadlock solution

Suppose we found a deadlock situation when we checked the engine status with Show innodb status, as shown in Listing 7.

Listing 7. MySQL deadlock

WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 843102 n bits 600 index `KEY_TSKTASK_MONTIME2` of table
`dcnet_db/TSK_TASK` trx id 0 677833454 lock_mode X locks rec but not gap waiting
Record lock, heap no 395 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 8; hex 8000000000000425; asc %;; 1: len 8; hex 800012412c66d29c;
asc A,f ;; 2: len 8; hex 800000000097629c; asc b ;;

***WE ROLL BACK TRANSACTION (1)
We assume that there is an index on the data table involved. This deadlock is caused by two records accessing the same index at the same time.

Let's first take a look at the InnoDB type of data table. As long as the index problem can be solved, the deadlock problem can be solved. MySQL's InnoDB engine is a row-level lock. It should be noted that this is not a record lock, but an index lock. During UPDATE and DELETE operations, MySQL not only locks all index records scanned by the WHERE condition, but also locks adjacent key values, the so-called next-key locking;

For example, the statement UPDATE TSK_TASK SET UPDATE_TIME = NOW() WHERE ID > 10000 will lock all records whose primary key is greater than or equal to 1000. Before the statement is completed, you cannot operate on records whose primary key is equal to 10000; When the cluster index record is locked, the related cluster index record also needs to be locked to complete the corresponding operation.

Let's analyze the two SQL statements where the problem occurs:

When "update TSK_TASK set STATUS_ID=1064,UPDATE_TIME=now () where STATUS_ID=1061 and MON_TIME
Assuming that "update TSK_TASK set STATUS_ID=1067, UPDATE_TIME=now () where ID in (9921180)" is executed almost at the same time, this statement first locks the cluster index (primary key). Since the value of STATUS_ID needs to be updated, it is also necessary to lock a certain part of KEY_TSKTASK_MONTIME2. some index records.

In this way, the first statement locks the record of KEY_TSKTASK_MONTIME2 and waits for the primary key index, while the second statement locks the record of the primary key index and waits for the record of KEY_TSKTASK_MONTIME2, so a deadlock occurs.

We solved the deadlock problem by splitting the first statement: that is, first find out the IDs that meet the conditions: select ID from TSK_TASK where STATUS_ID=1061 and MON_TIME < date_sub(now(), INTERVAL 30 minute); then update the status: update TSK_TASK set STATUS_ID=1064 where ID in (….).