Le cas est le suivant :
lors de l'utilisation de Show innodb status pour vérifier l'état du moteur, un problème de blocage a été découvert :
*** (1) TRANSACTION :
TRANSACTION 0 677833455, ACTIVE 0 sec, processus n° 11393, ID de thread du système d'exploitation 278546 lecture de l'index de démarrage.
tables MySQL utilisées 1, verrouillées 1
LOCK WAIT 3 structures de verrouillage, taille du tas 320
ID de thread MySQL 83, ID de requête 162348740 dcnet03 dcnet Recherche de lignes pour mise à jour
update TSK_TASK set STATUS_ID=1064,UPDATE_TIME=now () où STATUS_ID=1061 et MON_TIME
*** (1) EN ATTENTE DE L'OCTROI DE CE VERROU :
ENREGISTREMENT DE VERROUS espace id 0 numéro de page 849384 n bits 208 index `PRIMARY` de la table `dcnet_db/TSK_TASK` trx id 0 677833455 lock_mode X verrouille l'enregistrement mais pas l'espace en attente
Verrouillage d'enregistrement , tas n °
92 ;; 3 : lentille 8 ; hexadécimal 80000000000050b2 ; 4 : lentille 8 000000502a ; asc 8000000000005426 ; ; 7 : lentille 23 ; hexadécimal 75706c6f6164666972652e636f6d2f6
8616e642e706870 ; asc xxx.com/ ; 8 : lentille 8 ; hexadécimal 80000000000042b ;9
: lentille 4 ; hexadécimal 474bfa 2b ; ; asc N$;;
* (2) TRANSACTION :
TRANSACTION 0 677833454, ACTIVE 0 sec, processus n° 11397, ID de thread du système d'exploitation 344086, mise à jour ou suppression, thread déclaré dans
les tables mysql InnoDB 499 en cours d'utilisation 1, verrouillé 1
3 structures de verrouillage, taille de tas 320, annuler les entrées du journal 1
ID de thread MySQL 84, ID de requête 162348739 dcnet03 dcnet Mise à jour de
la mise à jour TSK_TASK set STATUS_ID=1067,UPDATE_TIME=now () où ID dans (9921180)
*** (2) CONTIENT LE(S) VERROU(S) :
ENREGISTRER l'identifiant de l'espace VERROUS 0 numéro de page 849384 n bits 208 index `PRIMARY` de la table `dcnet_db/TSK_TASK` trx id 0 677833454 lock_mode X verrouille l'enregistrement mais pas l'espace
Verrouillage de l'enregistrement, tas no 92 ENREGISTREMENT PHYSIQUE : n_fields 11 ; format compact bits d'information 0
0 : len " ; 4 : len 8 ; hexadécimal 800000000000502a; asc P*;; hexadécimal 8000000000005426;; 7 : len 23 ; asc uploadfire.com/hand.php;; 8 : longueur 8 ; hexadécimal 800000000000042b ; 8; hex 8000000000004e24; asc N$ ;;
*** (2) EN ATTENTE DE L'ACCORD DE CE VERROU :
ENREGISTREMENT DES VERROUS espace id 0 page no 843102 n bits 600 index `KEY_TSKTASK_MONTIME2` de la table `dcnet_db/TSK_TASK` trx id 0 677833454 lock_mode X verrouille l'enregistrement mais pas l'espace en attente
Verrouillage de l'enregistrement, tas no 395 ENREGISTREMENT PHYSIQUE : n_fields 3 ; format compact 0
0 : len 8 00000000425 ;; 9c; montée b ;;
*** WE ROLL BACK TRANSACTION (1)
Ce problème de blocage concerne la table TSK_TASK,
qui est utilisée pour sauvegarder les tâches desurveillance
du système. Voici les champs et index pertinents :
ID : clé primaire ;
Statut de la tâche ;
index : KEY_TSKTASK_MONTIME2 (STATUS_ID, MON_TIME).
L'analyse montre que les deux instructions impliquées ne devraient pas impliquer le même enregistrement TSK_TASK, alors pourquoi cela provoque-t-il un blocage ?
Après avoir consulté la documentation du site officiel de MySQL, j'ai découvert que cela était lié au mécanisme d'indexation de MySQL. Le moteur InnoDB de MySQL utilise des verrous au niveau des lignes. Ma compréhension initiale était que les enregistrements sont verrouillés directement, mais ce n'est pas réellement le cas.
Les points clés sont les suivants :
au lieu de verrouiller les enregistrements, l'index est verrouillé ;
lors des opérations UPDATE et DELETE, MySQL verrouille non seulement tous les enregistrements d'index analysés par la condition WHERE, mais verrouille également les valeurs de clé adjacentes, ce qu'on appelle la clé suivante. verrouillage ;
par exemple, l'instruction UPDATE TSK_TASK SET UPDATE_TIME = NOW() WHERE ID > 10000 verrouillera tous les enregistrements avec une clé primaire supérieure ou égale à 1000. Avant que l'instruction ne soit terminée, vous ne pouvez pas opérer sur les enregistrements avec une clé primaire égale. à 10000 ;
lorsque l'index non-cluster ( Lorsqu'un enregistrement d'index non-cluster est verrouillé, l'enregistrement d'index de cluster associé doit également être verrouillé pour terminer l'opération correspondante.
En analysant les deux instructions SQL où le problème s'est produit, il n'est pas difficile de trouver le problème :
lorsque "update TSK_TASK set STATUS_ID=1064,UPDATE_TIME=now () où STATUS_ID=1061 et MON_TIME
En supposant que "update TSK_TASK set STATUS_ID=1067,UPDATE_TIME=now () which ID in (9921180)" soit exécuté presque simultanément, cette instruction verrouille d'abord l'index du cluster (clé primaire). Puisque la valeur de STATUS_ID doit être mise à jour, elle est exécutée. est également nécessaire pour verrouiller une certaine partie de KEY_TSKTASK_MONTIME2 certains enregistrements d'index.
De cette façon, la première instruction verrouille l'enregistrement de KEY_TSKTASK_MONTIME2 et attend l'index de clé primaire, tandis que la deuxième instruction verrouille l'enregistrement de l'index de clé primaire et attend l'enregistrement de KEY_TSKTASK_MONTIME2. Dans ce cas, un blocage se produit.
L'auteur a résolu le problème de blocage en divisant la première instruction :
recherchez d'abord l'ID qualifié : sélectionnez l'ID dans TSK_TASK où STATUS_ID=1061 et MON_TIME < date_sub(now(), INTERVAL 30 minute) ; puis mettez à jour le statut : mettez à jour TSK_TASK set STATUS_ID= ; 1064 où ID entre (….)
À ce stade, le problème de blocage est complètement résolu.