Comment identifier la requête spécifique provoquant une erreur « Délai d'attente de verrouillage dépassé » dans MySQL ?

Comment identifier la requête spécifique qui détenait le verrou lorsque l'erreur de dépassement du délai d'attente du verrouillage se produit dans MySQL

Introduction :

L'erreur Dépassement du délai d'attente de verrouillage dans MySQL indique qu'une requête a rencontré un délai d'attente de verrouillage lors de la tentative d'accès à une ressource verrouillée. Cela peut se produire dans des scénarios dans lesquels plusieurs requêtes accèdent aux mêmes données et où une requête bloque l’exécution d’une autre en raison d’un verrou conflictuel. Déterminer la requête spécifique provoquant l'attente du verrouillage peut aider à résoudre le problème et à prévenir de futures occurrences.

Identification de la requête coupable :

Dans le journal des erreurs, le mot " transaction" est un indicateur clé indiquant que la requête tentant d'accéder à la base de données modifie une ou plusieurs tables InnoDB. Connaissant la requête qui a déclenché l'erreur, il est possible d'identifier les tables impliquées.

À l'aide de la commande SHOW ENGINE INNODB STATUSG, vous pouvez obtenir des informations sur les tables concernées et l'état de verrouillage. Cette commande affiche des informations détaillées, notamment :

• Spins partagés RW : Spécifie le nombre de tours en attente d'un verrou en lecture partagé.
• RW -excl spins : Indique le nombre de tours en attente d'une exclusivité lock.
• Attentes de spin mutex : Nombre de tours en attente sur les mutex.

Exemple de sortie :

Le La sortie suivante de SHOW ENGINE INNODB STATUSG montre qu'une requête spécifique attend un verrou exclusif sur la pièce jointe table :

RW-excl spins: 787046, OS waits: 39353
...
Latest foreign key error (a full list is printed in the InnoDB error log when a foreign key error occurs):
...
Transaction:
TRANSACTION 0 606162814, ACTIVE 0 sec, process no 29956, OS thread id 1223895360 updating or deleting, thread declared inside InnoDB 499
mysql tables in use 1, locked 1
...
Foreign key constraint fails for table `backoffice`.`attachment`:

,
  CONSTRAINT `attachment_ibfk_2` FOREIGN KEY (`file_id`) REFERENCES `file` (`file_id`)
Trying to delete or update in parent table, in index `PRIMARY` tuple:
...
But in child table `backoffice`.`attachment`, in index `PRIMARY`, there is a record:

Résolution :

Pour résoudre le problème, vous pouvez augmenter la valeur du paramètre innodb_lock_wait_timeout, qui spécifie la durée maximale d'attente d'une transaction un verrou avant de signaler une erreur. En augmentant ce délai d'attente, vous pouvez accorder plus de temps tampon pour que les transactions soient terminées et réduire le risque d'erreurs de délai d'expiration de verrouillage.

Définition de innodb_lock_wait_timeout :

• Pour définir le délai d'attente de manière permanente, ajoutez la ligne suivante à /etc/my.cnf et redémarrez MySQL :

  [mysqld]
  innodb_lock_wait_timeout=120

• Pour définir le délai d'expiration de la session en cours, exécutez la requête suivante :

  SET GLOBAL innodb_lock_wait_timeout = 120;

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!