Correction de "dépassement du délai d'attente de verrouillage ; essayez de redémarrer la transaction" pour une table Mysql "bloquée" ?

Question

À partir d'un script, j'ai envoyé une requête comme celle-ci des milliers de fois à ma base de données locale :

update some_table set some_column = some_value

J'ai oublié d'ajouter la clause where, donc la même colonne a été définie avec la même valeur pour toutes les lignes de la table et cela a été fait des milliers de fois et la colonne était indexée, donc l'index correspondant a probablement été mis à jour de nombreuses fois également.

J'ai remarqué quelque chose d'anormal car cela prenait trop de temps, j'ai donc interrompu le script. J'ai même redémarré mon ordinateur depuis, mais quelque chose est resté bloqué dans la table, car les requêtes simples prennent beaucoup de temps à s'exécuter et lorsque j'essaie de supprimer l'index pertinent, cela échoue avec ce message :

Lock wait timeout exceeded; try restarting transaction

Il s'agit d'une table innodb, donc le blocage de la transaction est probablement implicite. Comment puis-je corriger cette table et supprimer la transaction bloquée ?

Answer 1

J'avais un problème similaire et je l'ai résolu en vérifiant les threads qui s'exécutent. Pour voir les threads en cours d'exécution, utilisez la commande suivante dans l'interface de ligne de commande mysql:

SHOW PROCESSLIST;

Cela peut également être envoyé depuis phpMyAdmin si vous n'avez pas accès à l'interface de ligne de commande mysql.
Cela affichera une liste de threads avec les identifiants correspondants et le temps d'exécution, vous pouvez donc KILL les threads qui prennent trop de temps pour s'exécuter. Dans phpMyAdmin, vous aurez un bouton pour arrêter les threads en utilisant KILL, si vous utilisez l'interface de ligne de commande, utilisez simplement la commande KILL suivie de l'identifiant du thread, comme dans l'exemple suivant:

KILL 115;

Cela terminera la connexion pour le thread correspondant.

Answer 2

Cela a commencé à m'arriver lorsque la taille de ma base de données a augmenté et que je faisais beaucoup de transactions dessus.

La vérité est qu'il y a probablement un moyen d'optimiser soit vos requêtes soit votre base de données, mais essayez ces 2 requêtes pour contourner le problème.

Exécutez ceci :

SET GLOBAL innodb_lock_wait_timeout = 5000; 

Et ensuite ceci :

SET innodb_lock_wait_timeout = 5000;

Answer 3

Redémarrez MySQL, cela fonctionne bien.

MAIS attention, si une requête est bloquée, il y a un problème quelque part :

• dans votre requête (caractère mal placé, produit cartésien, ...)
• un nombre très élevé d'enregistrements à éditer
• des jointures ou des tests complexes (MD5, sous-chaînes, LIKE %...%, etc.)
• problème de structure des données
• modèle de clé étrangère (verrouillage en chaîne/boucle)
• données mal indexées

Comme l'a dit @syedrakib, ça fonctionne mais ce n'est pas une solution viable à long terme pour la production.

Attention : redémarrer peut affecter vos données en les laissant dans un état incohérent.

De plus, vous pouvez vérifier comment MySQL gère votre requête avec le mot clé EXPLAIN et voir s'il est possible d'optimiser la requête (index, tests complexes,...).

Answer 4

Accéder aux processus dans mysql.

Vous verrez qu'il y a encore une tâche en cours.

Arrêtez le processus particulier ou attendez qu'il se termine.

Answer 5

J'ai rencontré ce problème en essayant de supprimer un certain groupe d'enregistrements (en utilisant MS Access 2007 avec une connexion ODBC à MySQL sur un serveur web). En général, je supprimais certains enregistrements de MySQL puis les remplaçais par des enregistrements mis à jour (supprimant en cascade plusieurs enregistrements liés, ce qui simplifie la suppression de tous les enregistrements liés pour une seule suppression).

J'ai essayé d'exécuter les opérations disponibles dans phpMyAdmin pour la table (optimiser, vider, etc), mais j'ai obtenu une erreur de permission nécessaire pour RELOAD lorsque j'ai essayé de vider. Comme ma base de données est sur un serveur web, je ne pouvais pas redémarrer la base de données. La restauration à partir d'une sauvegarde n'était pas une option.

J'ai essayé d'exécuter la requête de suppression pour ce groupe d'enregistrements sur l'accès MySQL du cPanel sur le web. J'ai obtenu le même message d'erreur.

Ma solution : J'ai utilisé le MySQL Query Browser gratuit de Sun (Oracle) (que j'avais précédemment installé sur mon ordinateur) et j'ai exécuté la requête de suppression là-bas. Ça a marché tout de suite, Problème résolu. J'ai ensuite pu à nouveau exécuter la fonction en utilisant le script Access en utilisant la connexion ODBC d'Access à MySQL.