Partagez une méthode pour résoudre le problème de blocage de MySQL

Partagez une méthode pour résoudre le problème de blocage de 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, il a été constaté que MySQL InnoDB. ce n'est pas nécessairement tout. C'est un verrou au niveau de la ligne.

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

Verrouiller la sélection
1) Si la condition de mise à jour ne passe pas par l'index, par exemple, exécutez "update from t1 set v2= 0 où v2=5;", À ce stade, une analyse complète de la table sera effectuée. Lors de l'analyse de la table, toute autre opération de mise à jour doit être empêchée, elle est donc mise à niveau vers un verrou de table.
2) Si la condition de mise à jour est un champ d'index, mais qu'il ne s'agit pas d'un index unique (y compris l'index de clé primaire), par exemple, si vous exécutez "update from t1 set v2=0 which v1=9;" 🎜>

alors la mise à jour utilisera Next-Key Lock. Raisons d'utiliser Next-Key Lock :

a), assurez-vous d'abord qu'un verrou exclusif est ajouté aux enregistrements qui remplissent les conditions, ce qui verrouillera la valeur de l'index non unique actuel et l'index de clé primaire correspondant b), et également Il est garanti que de nouvelles données ne peuvent pas être insérées dans l'intervalle verrouillé.
3) Si la condition de mise à jour est un index unique, utilisez Record Lock.

InnoDB trouve l'enregistrement correspondant en fonction de l'index unique et ajoute le verrou d'enregistrement à la valeur d'index de clé primaire et à la valeur d'index unique. Mais n'utilisez pas Gap Lock (gap lock).
Étant donné qu'InnoDB utilise par défaut le verrouillage au niveau de la ligne, MySQL exécutera le verrouillage de ligne (verrouillera uniquement les données sélectionnées) uniquement si la clé primaire est "clairement" spécifiée. Sinon, MySQL exécutera le verrouillage de table (verrouillera toutes les données). le formulaire est verrouillé).


Sur la base de la conclusion de l'analyse, on suppose que la raison est que tel_id et task_id n'ont pas établi UNIQUE (index unique) dans la condition Where 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.