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

What are the causes and solutions of database deadlock? Many novices are not very clear about this. In order to help you solve this problem, the following editor will explain it in detail. People with this need can come and learn. I hope you can gain something.

Database deadlock causes and solutions: 1, the BUG of the program, need to adjust the logic of the program; 2, the button on the page does not take effect immediately, need to use optimistic lock, pessimistic lock to control; 3, execute multiple update statements that do not meet the conditions; need to analyze the statement, establish the corresponding index to optimize.

The causes and solutions of database deadlock:

There are two basic types of locks in the database: Exclusive Locks (X lock) and shared lock (Share Locks, S lock). When an exclusive lock is added to a data object, it cannot be read or modified by other transactions. 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.

The first case of deadlock

One user An accesses table A (locked table A) and then table B; another user B accesses table B (locked table B) and then attempts to access table A; at this time user A because user B has locked table B, it must wait for user B to release table B before continuing, and user B has to wait for user A to release table A before continuing, this deadlock occurs.

Solution:

This kind of deadlock is quite common, which is caused by the BUG of the program, and there is no other way to adjust the logic of the program. Carefully analyze the logic of the program, for the multi-table operation of the database, try to deal with rows in the same order as far as possible, and try to avoid locking two resources at the same time. For example, when operating two tables An and B, they are always processed in the order of An and B. when two resources must be locked at the same time, make sure that 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; when user B modifies the record, the nature of the lock in user A's transaction rises from a shared lock to an exclusive lock. The exclusive lock in user B has to wait for A to release the shared lock because A has a shared lock, and the exclusive lock that A cannot rise because of B's exclusive lock can not release the shared lock, so a deadlock occurs. This kind of deadlock is more hidden, but it often occurs in larger items. For example, in a project, after the button on the page is clicked, it does not immediately invalidate the button, so that the user will quickly click the same button many times, so that the same code manipulates the same record in the database for many times. it's easy to have this kind of deadlock.

Solution:

1, for buttons and other controls, click to make it invalid immediately, do not allow users to click repeatedly, to avoid the same record operation at the same time.

2. Use optimistic lock to control. Optimistic locks are mostly implemented based on data version (Version) recording mechanism. That is, to add a version identity to the data, which is generally achieved by adding a "version" field to the database table in the version solution based on the database table. When reading out the data, read out the version number together, and then add one to the version number when it is updated. At this point, the version data of the submitted data is compared with the current version information recorded in the database table, and if the submitted data version number is greater than the current version number of the database table, it will be updated, otherwise it will be regarded as out-of-date data. The optimistic locking mechanism avoids the database locking overhead in long transactions (user An and user B do not lock the database data during the operation), and greatly improves the overall performance of the system under large concurrency. Hibernate has an optimistic lock implementation built into its data access engine. It should be noted that because 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 to the database.

3. Use pessimistic lock to control. Pessimistic locks are mostly implemented by database locking mechanisms, such as Oracle's Select. For update statement to ensure maximum exclusivity of the operation. But it is followed by a large amount of database performance overhead, especially for long transactions, which is often unbearable. For example, in a financial system, when an operator reads the user's data and modifies it on the basis of the read user data (such as changing the user account balance), if the pessimistic locking mechanism is adopted, this means that during the whole operation (from the operator to read the data, start to modify until the modification result is submitted, and even the time for the operator to make coffee halfway) Database records are always locked, which can conceivably lead to catastrophic consequences in the face of hundreds of concurrency. Therefore, it must be carefully considered when using pessimistic locks for control.

The third case of deadlock

If a update statement that does not meet the conditions is executed in a transaction, a full table scan is performed and the row-level lock is raised to a table-level lock. After the execution of multiple such transactions, deadlocks and blocking are easy to occur. In a similar situation, when the amount of data in the table is very large and the index is too little or inappropriate, full table scans often occur, and eventually the application system will become slower and slower, resulting in blocking or deadlock.

Solution:

Do not use too complex related multi-table queries in SQL statements; use "execution plan" to analyze SQL statements, and establish corresponding indexes to optimize SQL statements with full table scans.

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

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.