MySQL Development Advanced Article Series 14 lock problem (avoid deadlock, deadlock view analysis)

I. Overview

In general, deadlock is an application design problem, by adjusting the business process, database object design, transaction size, and access to the database of SQL statements, the vast majority of deadlocks can be avoided, the following are a few common methods to avoid deadlocks.
1. In the application, if different programs operate concurrently with multiple tables, you should try to agree to access the tables in the same order, which can greatly reduce the chance of deadlocks. Working with tables sequentially is a common way to avoid deadlocks. For example, there are two different stored procedures and a complex pruning operation on a table. In this case, consider having one execution complete before the other is executing.
2. When processing data in batches in a program, it can significantly reduce the likelihood of deadlocks if the data is sorted beforehand to ensure that each thread processes the records in a fixed order. For example, the common is multi-threaded under the program lock lock, in the process of maintaining serial processing.
3. In a transaction, if you want to update a record, you should request a sufficient level of lock, that is, exclusive lock, instead of requesting a shared lock, and then requesting an exclusive lock, because when the user requests an exclusive lock, other transactions may have acquired a shared lock of the same record, resulting in a lock conflict. I understand that the record is to be updated first in the transaction to select: The for Update method obtains an exclusive lock, which can be updated directly without regard to lock collisions after the logic is processed in the transaction. The code is as follows:

SETAutocommit=0--get an exclusive lock on the data that will be updated firstSELECT *  fromCityWHEREcity_id=103  for UPDATE;--logical processing ....--last update to avoid lock conflictsUPDATECitySETCityName='Hangzhou' WHEREcity_id=103;COMMIT;

4. At the default level repeatable read, if two threads are simultaneously logged with select for the same condition: For update plus locks, two threads will be locked successfully if the condition record is not met . When a program discovers that a record does not exist, it attempts to insert a new data, and if two threads do so, a deadlock occurs. This is because a gap lock is generated under repeatable read. In this case, you can avoid the problem by changing the isolation level to read commited . If the table is affixed with two isolation levels, the difference in lock generation occurs.

5. When under repeatable Read, if two threads perform a select first: For update. In determining whether there is a qualifying record, if not, insert the record, at this time, only one line Cheng Nen insert succeeds, another thread will have a lock wait, when the 1th thread commits, the 2nd line Cheng because the primary key value repeats, an exception occurs. But it gets an exclusive lock that needs to be executed rollback release exclusive lock. Avoid affecting other transactions.
Summary: Although the above introduction and SQL optimization measures, can greatly reduce the deadlock, but deadlock is difficult to completely avoid. So. It is a good programming habit to always capture and handle deadlock exceptions in program design. In a program exception or commit or rollback.

Two. Check the cause of the deadlock

If a deadlock occurs, you can use the show ENGINE INNODB STATUS command to determine why the last deadlock occurred. The results of the return include details of the deadlock-related transaction, such as the SQL statement that caused the deadlock, the locks that the transaction has acquired, what locks are waiting, and the transactions that were rolled back, to analyze the cause of the deadlock and the improvement measures.

-- View last Deadlock SHOW ENGINE  INNODB STATUS;

LATEST detected DEADLOCK------------------------2018- ,- Geneva  -: -: $ 0x7f3a12209700*** (1) TRANSACTION: TRANSACTION 35489574, ACTIVE theSEC startingINDEX READMySQL TABLESinch  Use 1, Locked1LOCK WAIT4LOCK struct (s), HEAP size1136,2ROW LOCK (s) MySQL thread ID2634494, OS thread handle139887387092736, QUERY ID109768880 172.168.18.202Root Sending DATA--because session 2 has acquired an exclusive lock, some statements await SELECT *  fromCitynewWHEREcity_id=103  for UPDATE***(1) Waiting forThis LOCK toBe Granted:record LOCKSSPACEId479Page NO3N Bits the INDEXGen_clust_index of TABLE' Test '. ' Citynew ' Trx ID35489574Lock_mode X Waiting*** (2) TRANSACTION: TRANSACTION 35489577, ACTIVE8SEC startingINDEX READ, thread declared inside INNODB theMySQL TABLESinch  Use 1, Locked14LOCK struct (s), HEAP size1136,3ROW LOCK (s) MySQL thread ID2634624, OS thread handle139887388956416, QUERY ID109768953 172.168.18.202RootStatistics--dead Lock SELECT *  fromCityWHEREcity_id=103  for UPDATE***(2) holds the LOCK (S):RECORD LOCKSSPACEId479Page NO3N Bits the INDEXGen_clust_index of TABLE' Test '. ' Citynew ' Trx ID35489577Lock_mode X***(2)waiting  for this LOCK   toBe  Granted:RECORD LOCKSSPACEId477Page NO3N Bits the INDEX PRIMARY  of TABLE' Test '. ' City ' Trx ID35489577Lock_mode X LOCKS Rec but notGap Waiting***WERoll Back TRANSACTION (2)------------

MySQL Development Advanced Article Series 14 lock problem (avoid deadlock, deadlock view analysis)