Introduction détaillée au code pour résoudre le problème de blocage de MySQL

Solution à un problème de blocage MySQL

1. Environnement

• CentOS, MySQL 5.6.21-70, JPA

• Scénario de problème : le système a une opération planifiée de mise à jour de l'état des données par lots, mettant à jour des milliers d'enregistrements à chaque fois, et le nombre total d'enregistrements dans le tableau est d'environ 5 millions.

2. Journal des erreurs

2017-2-25 17:38:41 org.hibernate.util.JDBCExceptionReporter logExceptions
严重: Lock wait timeout exceeded; try restarting transaction
2017-2-25 17:39:05 org.hibernate.util.JDBCExceptionReporter logExceptions
警告: SQL Error: 1213, SQLState: 40001
2017-2-25 17:39:05 org.hibernate.util.JDBCExceptionReporter logExceptions
严重: Deadlock found when trying to get lock; try restarting transaction

3. Dépannage

Check InnoDB status for locks
mysql> SHOW ENGINE InnoDB STATUS;

Check MySQL open tables
mysql> SHOW OPEN TABLES WHERE In_use > 0;

Check pending InnoDB transactions
mysql> SELECT * FROM `information_schema`.`innodb_trx` ORDER BY `trx_started`; 

Check lock dependency - what blocks what
mysql> SELECT * FROM `information_schema`.`innodb_locks`;

Après le dépannage, il a été constaté que des problèmes sont survenus lors de l'exécution d'instructions telles que ceci :

update t_task_tel set state='iok', update_date='2017-02-27 11:03:02' where tel_id=66042 and task_id=350199;

4. Analyse

Après avoir recherché des informations pertinentes, j'ai découvert que MySQL InnoDB n'a pas nécessairement tous des lignes- verrous de niveau.

Les fragments de matériel de référence pertinents sont les suivants :

MySQL InnoDB锁机制之Gap Lock、Next-Key Lock、Record Lock解析
//m.sbmmt.com/

4、锁选择
1）、如果更新条件没有走索引，例如执行”update from t1 set v2=0 where v2=5;” ，此时会进行全表扫描，扫表的时候，要阻止其他任何的更新操作，所以上升为表锁。
2）、如果更新条件为索引字段，但是并非唯一索引（包括主键索引），例如执行“update from t1 set v2=0 where v1=9;” 
那么此时更新会使用Next-Key Lock。使用Next-Key Lock的原因：
a）、首先要保证在符合条件的记录上加上排他锁，会锁定当前非唯一索引和对应的主键索引的值；
b）、还要保证锁定的区间不能插入新的数据。
3）、如果更新条件为唯一索引，则使用Record Lock（记录锁）。
 
InnoDB根据唯一索引，找到相应记录，将主键索引值和唯一索引值加上记录锁。但不使用Gap Lock（间隙锁）。

MySQL InnoDB 锁表与锁行
//m.sbmmt.com/

由于InnoDB预设是Row-Level Lock，所以只有「明确」的指定主键，MySQL才会执行Row lock (只锁住被选取的资料例) ，否则MySQL将会执行Table Lock (将整个资料表单给锁住)。

Selon la conclusion de l'analyse, il est supposé que la raison pour laquelle tel_id et task_id dans la condition Where ne sont pas UNIQUE (index unique) n'est pas établi lors de la mise à jour de la table _task_tel ;

5. Solution

Sur la base de cette analyse, essayez de résoudre le problème en établissant un UNIQUE (index unique) à travers les deux champs. de tel_id et task_id. (Vous pouvez également l'interroger d'abord, puis le mettre à jour en fonction de l'ID de clé primaire, afin que la grande quantité de données dans la table n'affecte pas les activités en ligne).

Après avoir résolu ce problème, le problème ne s'est pas reproduit.

Si votre problème est similaire à celui que j'ai rencontré, vous pouvez essayer de le résoudre en conséquence.

Ce qui précède est l'introduction détaillée du code pour résoudre le problème de blocage de MySQL. Pour plus de contenu connexe, veuillez faire attention au site Web PHP chinois (m.sbmmt.com) !