Causes and solutions of database deadlocks

Causes and solutions for database deadlocks: 1. A BUG occurs in the program, and the logic of the program needs to be adjusted; 2. The buttons on the page do not take effect immediately, and optimistic locks and pessimistic locks need to be used for control; 3. , execute multiple update statements that do not meet the conditions; the statements need to be analyzed and corresponding indexes established for optimization.

Causes and solutions for database deadlocks:

There are two basic types of locks in the database : Exclusive Locks (Exclusive Locks, i.e. X lock) and Shared Locks (Share Locks, i.e. S lock). When a data object is locked exclusively, other transactions cannot read or modify it. Data objects with shared locks can be read by other transactions, but cannot be modified. The database uses these two basic lock types to control the concurrency of database transactions.

Related graphic tutorials: mysql database graphic tutorials

The first situation of deadlock

A user A accesses table A (locks table A), and then accesses table B; another user B accesses table B (locks table B), and then attempts to access table A; at this time, user A due to user B has locked table B. It must wait for user B to release table B before it can continue. Similarly, user B must wait for user A to release table A before it can continue. This creates a deadlock.

Solution:

This kind of deadlock is relatively common and is caused by a bug in the program. There is no other solution except adjusting the logic of the program. Carefully analyze the logic of the program. When operating multiple tables in the database, try to process them in the same order, and try to avoid locking two resources at the same time. For example, when operating two tables A and B, always process them in the order of A first and then B. , When two resources must be locked at the same time, it must be ensured that the resources should be locked in the same order at any time.

The second case of deadlock

User A queries a record and then modifies the record; then user B modifies the record, then user A The nature of the lock in the transaction attempts to increase from the shared lock of the query to an exclusive lock, and the exclusive lock in user B must wait for A to release the shared lock because A has a shared lock, and A cannot increase due to B's exclusive lock. It is impossible for the exclusive lock to release the shared lock, so a deadlock occurs. This kind of deadlock is relatively hidden, but it often occurs in larger projects. For example, in a project, after clicking a button on the page, the button does not immediately become invalid, causing the user to quickly click the same button multiple times. In this way, the same piece of code performs multiple operations on the same record in the database, and this kind of failure can easily occur. lock situation.

Solution:

1. For controls such as buttons, make them invalid immediately after being clicked to prevent users from clicking repeatedly and avoid operating on the same record at the same time.

2. Use optimistic locking for control. Optimistic locking is mostly implemented based on the data version (Version) recording mechanism. That is to add a version identifier to the data. In version solutions based on database tables, this is usually achieved by adding a "version" field to the database table. When the data is read out, this version number is also read out, and when it is updated later, this version number is incremented by one. At this time, the version data of the submitted data is compared with the current version information of the corresponding record in the database table. If the version number of the submitted data is greater than the current version number of the database table, it will be updated, otherwise it will be considered as expired data. The optimistic locking mechanism avoids the database locking overhead in long transactions (neither user A nor user B locks the database data during the operation), which greatly improves the overall performance of the system under large concurrency. Hibernate has an optimistic locking implementation built into its data access engine. It should be noted that since the optimistic locking mechanism is implemented in our system, user update operations from external systems are not controlled by our system, so dirty data may be updated into the database.

3. Use pessimistic locking for control. In most cases, pessimistic locking relies on the locking mechanism of the database, such as Oracle's Select... for update statement, to ensure the maximum exclusivity of the operation. But what follows is a large overhead in database performance, especially for long transactions, which is often unbearable. For example, in a financial system, when an operator reads user data and makes modifications based on the read user data (such as changing the user account balance), if a pessimistic locking mechanism is used, it means that the entire operation process (The whole process from the operator reading the data, starting the modification to submitting the modification result, and even including the time when the operator went to make coffee in the middle), the database records are always locked. It is conceivable that if you face hundreds or thousands of concurrency, such a situation will lead to catastrophic consequences. Therefore, you must consider it carefully when using pessimistic locking for control.

The third situation of deadlock

If an update statement that does not meet the conditions is executed in a transaction, a full table scan will be performed and the row-level lock will be upgraded to a table-level lock. After multiple such transactions are executed, deadlock and blocking will easily occur. A similar situation occurs when the amount of data in the table is very large and the number of indexes created is too few or inappropriate, causing frequent full table scans. In the end, the application system will become slower and slower, and eventually blocking or deadlock will occur.

Solution:

Do not use too complex queries that relate multiple tables in SQL statements; use the "execution plan" to analyze the SQL statement. For complete tables Scan the SQL statements and create corresponding indexes for optimization.

Related learning recommendations: mysql video tutorial

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