The reason of Mysql database lock and the solution method _mysql

databases, like the operating system, are shared resources that are used by multiple users. When multiple users have concurrent access to the data, multiple transactions are generated in the database while the same data is being accessed. Without control of concurrent operations, incorrect data can be read and stored, which destroys the consistency of the database. Lock is a very important technology to realize concurrency control of database. In the actual application of the lock-related anomalies often encountered, when two transactions need a set of conflicting locks, but not to continue the transaction, there will be deadlock, seriously affect the normal implementation of the application.

There are two basic types of locks in the database: exclusive locks (Exclusive Locks, i.e., x locks) and shared locks (Share Locks, or S locks). When a data object is added to the exclusive lock, no other transaction can read and 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 concurrently control the transactions of the database.

The first case of deadlock

A User A accesses table A (lock table a) and then accesses table B; Another user B accesses table B (locked table B), then attempts to access table A; Then user A because User B has locked table B, it must wait for User B to release table B to continue, same user B to wait for User a release table A to continue, This is where the deadlock is created.

Workaround:

This deadlock is more common, due to the bug in the program, in addition to the adjustment of the logic of the program there is no other way. Careful analysis of the logic of the program, for a database of multiple table operations, try to do the same in the same order, as far as possible to avoid locking two resources at the same time, such as operations A and B tables, always in the order of a after B processing, must be locked at the same time two resources, to ensure that at any time should be in the same order to lock the resources.

The second case of deadlock

User A queries a record, then User B modifies the record, at which point the nature of the lock in User A's transaction is raised to an exclusive lock by the query's shared lock attempt, and the exclusive lock in User B must wait a to release the shared lock because a has a shared lock. An exclusive lock that cannot be raised because of a exclusive lock on B cannot release the shared lock, and a deadlock occurs. This deadlock is more covert, but often occurs in larger projects. such as in a project, the button on the page after clicking, does not make the button immediately invalid, so that users will quickly click the same button, so that the same piece of code on the database of the same record many operations, it is easy to appear this deadlock.

Workaround:

1, for buttons and other controls, click to make it immediately invalid, do not allow users to repeat the click, to avoid the same record on the same operation.
2, the use of optimistic lock control. Most optimistic locks are implemented based on the data version (versioning) recording mechanism. That is, adding a version identity to the data, in a version solution based on a database table, typically by adding a "version" field to the database table. When you read out the data, read the version number together, and then when you update it, add one to this version number. At this point, the version data for the submitted data is compared to the current version information of the corresponding record in the database table, and is updated if the submitted data version number is greater than the current version number of the database table, otherwise it is considered to be an expired data. The optimistic locking mechanism avoids the locking overhead of the database in the long transaction (both user A and User B do not lock the database data), and greatly improves the overall performance of the system under the large concurrent volume. Hibernate has a built-in optimistic lock implementation in 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 and may cause dirty data to be updated to the database.
3, use pessimistic lock to control. Pessimistic locks are most often dependent on the database's lock mechanism, such as the Oracle SELECT ... for UPDATE statement, to ensure maximum operational exclusivity. But then comes the massive cost of database performance, especially for long transactions, which are often unsustainable. such as a financial system, when an operator reads a user's data, and the user data read out on the basis of modification (such as changing the user account balance), if the use of pessimistic locking mechanism, it means that the entire operation (from the operator read data, start to modify until the completion of the submission of changes to the whole process, Even when the operator goes to make coffee in the middle of the day, the database record is always locked, and it can be expected that, if faced with hundreds of concurrent cases, this will lead to disastrous consequences. Therefore, the use of pessimistic lock control must be considered clearly.

The third case of deadlock

If an UPDATE statement that does not meet the condition is executed in a transaction, a full table scan is performed, and a row-level lock is raised to a table-level lock, which can easily result in deadlocks and blocking after the execution of multiple such transactions. Similarly, when the amount of data in a table is very large and the index is too small or unsuitable, a full table scan is often made, and the end application system slows down and eventually blocks or deadlocks.

Workaround:

Do not use queries that are too complex to associate multiple tables in SQL statements, analyze SQL statements using the execution plan, and establish appropriate indexes for SQL statements that have full table scans.
5. Summary
Overall, the resulting memory overflow and lock tables are caused by poor code writing, so improving the quality of your code is the most fundamental solution. Some people think that it is wrong to implement the function first and then revise it in the test phase when there is a bug. Just as the quality of a product is determined in the manufacturing process, not the quality test, the quality of the software has been decided in the design and coding phase, and the test is only a validation of the quality of the software, because it is impossible to find all the bugs in the software.