Mysql database lock causes and solutions, mysql database

Mysql database lock causes and solutions, mysql database

A database is a shared resource used by multiple users, just like an operating system. When multiple users access data concurrently, multiple transactions can access the same data simultaneously in the database. If concurrent operations are not controlled, incorrect data may be read and stored, compromising Database Consistency. Locking is a very important technology for implementing database and sending control. In practice, we often encounter lock-related exceptions. When two transactions need a set of conflicting locks and cannot continue the transaction, a deadlock will occur, strict impact on normal application execution.

There are two basic lock types in the database: Exclusive Locks and Share Locks ). When an exclusive lock is applied to a data object, other transactions cannot read or modify it. Data Objects with a shared lock 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.

First case of deadlock

One user A accesses Table A (Table A is locked) and then table B; the other user B accesses table B (Table B is locked), and then tries to access Table; because user B has locked table B, user A must wait for user B to release Table B. User B must wait for user A to release Table A to continue, this causes deadlocks.

Solution:

This deadlock is common because of bugs in the program. There are no other methods except for the logic of the adjusted program. Carefully analyze the program logic. When performing multi-Table operations on the database, try to process them in the same order and avoid locking two resources at the same time. For example, when operating two tables A and B, it is always processed in the order of A and B. When two resources must be locked at the same time, make sure that the resources should be locked in the same order at any time.

The second situation of deadlock

User A queries A record and then modifies the record. Then user B modifies the record. In this case, the nature of the lock in user A's transaction is increased from the shared lock in the query to the exclusive lock, because A has A shared lock, the exclusive lock in user B must wait for A to release the shared lock, and the exclusive lock that A cannot rise due to the exclusive lock of B cannot be released, A deadlock occurs. This type of deadlock is relatively hidden, but it often occurs in the larger metrics. For example, if a button on a project does not expire immediately after it is clicked, the user can 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, which is easy to cause this deadlock.

Solution:

1. For buttons and other controls, click them to make them invalid immediately, so that users are not allowed to click them again to avoid operations on the same record at the same time.
2. Use optimistic locks for control. Optimistic locks are mostly implemented based on the data Version record mechanism. Add a version ID for the data. In the database table-based version solution, you can add a "version" field to the database table. When reading the data, read the version number together, and then add one to the version number when updating the data. In this case, the version data of the submitted data is compared with the current version information recorded in the corresponding data base table. If the submitted data version number is greater than the current version number of the database table, it is updated, otherwise, expired data is considered. The Optimistic Locking mechanism avoids the overhead of locking the database in long transactions (neither user A nor user B locks the database data during operations ), this greatly improves the overall performance of the system with high concurrency. Hibernate has built-in optimistic lock implementation in its data access engine. Note that the optimistic lock mechanism is implemented in our system, and the user update operations from external systems are not controlled by our system, therefore, dirty data may be updated to the database.
3. Use pessimistic locks for control. Pessimistic locks are implemented by the database lock mechanism in most cases, such as the Oracle Select... For update statement to ensure the maximum degree of exclusiveness of the operation. However, there is 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 user data and modifies it based on the read user data (such as changing the user account balance), if a pessimistic lock mechanism is used, this means that the database records are always locked during the entire operation (from the operator's reading data, starting modification to submitting modification results, or even the time when the operator goes to coffee making, it can be imagined that in the face of hundreds of concurrency, this situation will lead to disastrous consequences. Therefore, we must consider the use of pessimistic locks for control.

The third situation of deadlock

If an update statement that does not meet the conditions is executed in the transaction, a full table scan is executed to promote the row-Level Lock to the table-Level Lock. After multiple such transactions are executed, it is easy to cause deadlocks and congestion. Similarly, when the data volume in the table is very large and the index is too small or inappropriate, full table scanning often occurs, and the application system will become slower and slower, eventually congestion or deadlock occurs.

Solution:

Do not use too complex queries associated with multiple tables in SQL statements. Use the "Execution Plan" to analyze SQL statements and create indexes for SQL statements with full table scans for optimization.
5. Summary
Generally, memory overflow and lock tables are caused by poor code writing. Therefore, improving the code quality is the most fundamental solution. Some people think that this idea is wrong to first implement the function and correct it in the test phase when there is a BUG. If the quality of a product is determined in the manufacturing process, rather than in the quality inspection, the quality of the software has been determined in the design and coding stage, testing is only a verification of software quality, because it is impossible to identify all the bugs in the software.

Articles you may be interested in:

• Mysql database Locking Mechanism