MySQL deadlock problem

This article mainly introduced the MySQL database lock generation reason and the solution, needs the friend can refer to the next

The database, like the operating system, is a shared resource used by multiple users. When multiple users access data concurrently, in the database, multiple transactions are generated concurrently to access the same data. If the concurrency operation is not controlled, it is possible to read and store incorrect data and compromise the consistency of the database. Locking is a very important technology to realize concurrency control of database. In practical applications often encounter lock-related anomalies, when two transactions require a set of conflicting locks, and cannot continue the transaction, there will be a deadlock, serious impact on the normal execution of the application.

There are two basic types of locks in the database: Exclusive lock (Exclusive Locks, X Lock) and shared lock (Share Locks, S lock). When a data object is added to an exclusive lock, other transactions cannot 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 types of locks to control the concurrency of a database's transactions.

The first case of deadlock

A User A accesses table A (locks up table A), then accesses table B, another user B accesses table B (Locks table B), and then attempts to access table A; User A because User B has locked table B, it must wait for User B to release table B to continue, and User B waits for user A to release table A to continue. This is where the deadlock occurs.

Workaround:

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

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 is raised to the exclusive lock by the shared lock attempt of the query, and the exclusive lock in User B is present because a has a shared lock, so must wait for a to release the shared lock. An exclusive lock that cannot be raised because of the exclusive lock of B will not be able to release the shared lock, so there is a deadlock. This kind of deadlock is more subtle, but it often happens in slightly larger projects. In the case of a project, the button clicked on the page, did not make the button immediately invalid, so that the user will quickly click the same button multiple times, so that the same piece of code on the same record of the database multiple operations, it is easy to see this deadlock situation.

Workaround:

1, for the button and other controls, click to make it immediately invalid, do not allow users to repeat the click, to avoid the same record at the same time operation.
2, use optimistic lock to control. Optimistic locks are mostly implemented based on the data version (versions) recording mechanism. is to add a version identity to the data, which is typically done by adding a "version" field to the database table in the version solution based on the database table. When the data is read, the version number is read together, and then the version number is added one after the update. At this point, the version data of the submitted data is compared to the current version information of the database table corresponding to the record, and if the submitted version number is greater than the current version number of the database table, it is updated, otherwise it is considered to be outdated data. The optimistic locking mechanism avoids the database lock-up overhead in long transactions (both user A and User B do not locking the database data), which greatly improves the overall performance of the system under large concurrency. Hibernate has built-in optimistic locking implementations in its data access engine. It is important to note that since the optimistic locking mechanism is implemented in our system, user update operations from external systems are not controlled by our system and may result in dirty data being updated into the database.
3, use pessimistic lock to control. Pessimistic locks are implemented in most cases by the locking mechanism of the database, such as Oracle's SELECT ... for UPDATE statement, to ensure maximum operation exclusivity. But it comes with a lot of overhead for database performance, especially for long transactions, which are often unsustainable. As a financial system, when an operator reads a user's data and modifies it on the basis of the user's data being read (such as changing the user's account balance), a pessimistic locking mechanism means that the entire operation (from the operator reads the data, starts the modification, and commits the modified result. Even when the operator takes the time to cook the coffee, the database record is always locked, and you can imagine that if you face hundreds of concurrent cases, it will result in catastrophic 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 criteria is executed in the transaction, the full table scan is performed, and the row-level lock is raised to a table-level lock, and after many such transactions are executed, it is easy to create deadlocks and blockages. In a similar situation, when the amount of data in a table is very large and the index is too small or inappropriate, a full table scan often occurs, and eventually the application system slows down and eventually becomes blocked or deadlocked.

Workaround:

The SQL statement does not use a query that is too complex to correlate multiple tables, parses the SQL statement using the execution plan, and optimizes the corresponding indexes for SQL statements with full table scans.
5. Summary
In general, the generation of memory overflow and lock table is due to bad code writing, so improving the quality of the code is the most fundamental solution. Some people think that the implementation of the function first, there is a bug in the testing phase of the correction, this idea is wrong. Just as the quality of a product is determined in the process of manufacturing, rather than in quality testing, the quality of the software has been decided during the design and coding stages, and testing is only a validation of the quality of the software, since it is impossible to find all the bugs in the software.

"Original" http://www.jb51.net/article/78089.htm

MySQL deadlock problem