MySQL : solution permanente au problème "En attente du verrouillage des métadonnées de la table"

Question

Ma base de données MySQL sert de backend de stockage pour trois applications Web. Cependant, j'ai récemment rencontré l'erreur "En attente du verrouillage des métadonnées de la table" de manière permanente. Cela arrive presque tout le temps et je ne comprends pas pourquoi.

mysql> show processlist
    -> ;
+------+-----------+-----------------+------------+---------+------+---------------------------------+------------------------------------------------------------------------------------------------------+
| Id   | User      | Host            | db         | Command | Time | State                           | Info                                                                                                 |
+------+-----------+-----------------+------------+---------+------+---------------------------------+------------------------------------------------------------------------------------------------------+
|   36 | root      | localhost:33444 | bookmaker2 | Sleep   |  139 |                                 | NULL                                                                                                 |
|   37 | root      | localhost:33445 | bookmaker2 | Sleep   |  139 |                                 | NULL                                                                                                 |
|   38 | root      | localhost:33446 | bookmaker2 | Sleep   |  139 |                                 | NULL                                                                                                 |
|   39 | root      | localhost:33447 | bookmaker2 | Sleep   |   49 |                                 | NULL                                                                                                 |
|   40 | root      | localhost:33448 | bookmaker2 | Sleep   |  139 |                                 | NULL                                                                                                 |
| 1315 | bookmaker | localhost:34869 | bookmaker  | Sleep   |   58 |                                 | NULL                                                                                                 |
| 1316 | root      | localhost:34874 | bookmaker3 | Sleep   |   56 |                                 | NULL                                                                                                 |
| 1395 | bookmaker | localhost:34953 | bookmaker  | Sleep   |   58 |                                 | NULL                                                                                                 |
| 1396 | root      | localhost:34954 | bookmaker3 | Sleep   |   46 |                                 | NULL                                                                                                 |
| 1398 | root      | localhost:34956 | bookmaker3 | Query   |   28 | Waiting for table metadata lock | CREATE TABLE IF NOT EXISTS LogEntries  ( 
                    lid         INT NOT NULL AUTO_INCREMEN |
| 1399 | root      | localhost       | NULL       | Query   |    0 | NULL                            | show processlist                                                                                     |
+------+-----------+-----------------+------------+---------+------+---------------------------------+------------------------------------------------------------------------------------------------------+

Bien sûr, vous pouvez tuer le processus correspondant. Cependant, si je redémarre le programme qui tente de créer la structure de table pour la base de données "bookmaker3", le processus nouvellement créé est à nouveau dans Metallock.

Je ne peux même pas supprimer la base de données :

mysql> drop database bookmaker3;

Cela créera également un verrou métallique.

Comment résoudre ce problème ?

Answer 1

Malheureusement, la solution acceptée est fausse. C'est tout à fait exact

C'est définitivement (presque définitivement ; voir ci-dessous) la chose à faire. Mais ensuite ça s'est vu,

...et 1398 n'est pas la connexion à la serrure. comment ça? 1398 est une connexion en attente d'être verrouillée. Cela signifie qu'il n'a pas encore acquis le verrou, donc le tuer n'a aucun effet. Le processus détenant le verrou détiendra toujours le verrou, et le prochain thread essayant d'effectuer quelque chose également s'arrêtera et entrera "attendre le verrouillage des métadonnées" dans l'ordre approprié.

Vous ne pouvez pas garantir qu'un processus "En attente de verrouillage des métadonnées" (WFML) ne se bloquera pas également, mais vous pouvez être sûr que le simple fait de tuer le processus WFML ne fera complètement rien .

La vraie raison est que un autre processus détient le verrou, et plus important encore, SHOW FULL PROCESSLIST ne vous dira pas directement quel processus .

Une chose dont vous pouvez être sûr est que il n'y a pas de processus marqués "En attente du verrouillage des métadonnées". On peut dire que ces personnes sont des victimes.

SHOW FULL PROCESSLIST WILL vous dit si un processus fait quelque chose, oui. Habituellement, cela fonctionnera. Ici, le processus détenant le verrou ne fait rien et est caché dans d'autres threads qui ne font rien non plus et sont signalés comme "en veille".

Si SHOW FULL PROCESSLISTvous montre un processus exécutant DML, ou dans un état "envoi de données", alors c'est est presque certainement le coupable. D'autres processus attendent qu'il libère le verrou (il peut s'agir de verrous implicites ; le processus n'a pas du tout besoin d'émettre LOCK TABLE, qui se verrouille en fait d'une manière différente). Mais un processus peut détenir un verrou sans effectuer aucune opération et être marqué de manière appropriée comme « en veille ».

Dans le cas d'OP, le coupable est presque certainement le processus 1396, qui a été démarré avant le processus 1398, est maintenant dans l'état 睡眠 et ce depuis 46 secondes. Depuis que 1396 a apparemment fait tout ce qu'il devait faire (il s'avère qu'il dort maintenant, et ce depuis 46 secondes, en ce qui concerne MySQL), aucun thread n'entre dans il peut maintenir le verrou et pourtant maintenez-le endormi avant (sinon 1396 s'arrêterait également).

En raison de la politique de verrouillage « sans blocage » de MySQL, aucun processus ne peut détenir un verrou, le libérer et le restaurer à nouveau. Par conséquent, les attentes de verrouillage sont toujours causées par des processus qui détiennent toujours le verrou et ne l'ont jamais détenu auparavant. Ceci est utile (nous exploiterons ce fait ci-dessous) car cela garantit que la "file d'attente" de verrouillage est ordonnée.

Important : Si vous vous connectez à MySQL en tant qu'utilisateur restreint, SHOW FULL PROCESSLIST n'affichera pas tous les processus. Le verrou peut donc être détenu par un processus que vous ne voyez pas.

Donc : si SHOW FULL PROCESSLISTvous montre tout et montre un processus en cours, alors ce processus est probablement responsable et vous devez attendre qu'il termine ce qu'il fait (ou vous pouvez le tuer - à vos propres risques).

Le reste de cette réponse traite d'une situation confuse où un processus attend sans raison apparente et personne ne semble faire quoi que ce soit.

Mieux显示进程列表

SELECT ID, TIME, USER, HOST, DB, COMMAND, STATE, INFO
    FROM INFORMATION_SCHEMA.PROCESSLIST WHERE DB IS NOT NULL
    AND (`INFO` NOT LIKE '%INFORMATION_SCHEMA%' OR INFO IS NULL)
    ORDER BY `DB`, `TIME` DESC

Ce qui précède peut être ajusté pour afficher uniquement les processus qui sont en SOMMEIL, et il les triera de toute façon par ordre temporel décroissant, il est donc plus facile de trouver les processus bloqués (qui, en raison de l'ordre, sont généralement immédiatement avant "en attente de métadonnées lock" Dormez un ; et c'est toujours un sommeil de plus

que n'importe quel temps d'attente.

Choses importantes

Gardez tous les processus « en attente de verrouillage des métadonnées » séparés.

Solution rapide et sale, pas vraiment recommandée, mais rapide

Tuez tous les processus en état "en veille" sur la même base de données qui sont plus anciens que le thread le plus ancien en état "en attente de verrouillage des métadonnées". Voici ce que ferait Arnaud Amaury :

• Pour chaque base de données avec au moins un thread dans WaitingForMetadataLock :
  • La connexion la plus ancienne en WFML sur cette base de données date de Z secondes
  • Tous les threads "dormants" de cette base de données plus anciens que Z doivent mourir. Commencez par le plus frais, juste au cas où.
  • S'il y a une ancienne connexion non en veille sur cette base de données, c'est probablement celle qui détient le verrou, mais elle fait quelque chose . Vous pouvez bien sûr le tuer, mais surtout s'il s'agit d'une mise à jour/insertion/suppression, faites-le à vos propres risques.
  • Après chaque KILLKILL, réévaluez la situation et redémarrez le processus en conséquence. Les processus en attente peuvent être en cours d'exécution maintenant, ou ils ont peut-être été exécutés brièvement et sont maintenant en veille. Ils peuvent même détenir de nouveaux verrous de métadonnées maintenant.

Quatre-vingt-dix-neuf fois sur cent, le thread à tuer est le le plus jeunethread qui dort et plus âgé que l'ancien thread en attente du verrouillage des métadonnées :

TIME     STATUS
319      Sleep
205      Sleep
 19      Sleep                      
(*) L'ordre TIME comporte en fait des millisecondes, ou on me dit qu'il ne les affiche tout simplement pas. Ainsi, même si les deux processus ont une valeur temporelle de 19, le processus le plus bas devrait être plus jeune. 

Réparation plus ciblée

Exécutez 
SHOW ENGINE INNODB STATUS et regardez la section "TRANSACTION". Entre autres choses, vous trouverez quelque chose comme SHOW ENGINE INNODB STATUS
TRANSACTION 1701, ACTIVE 58 sec;2 lock struct(s), heap size 376, 1 row lock(s), undo log entries 1
MySQL thread id 1396, OS thread handle 0x7fd06d675700, query id 1138 hostname 1.2.3.4 whatever;
Maintenant, vous utilisez SHOW FULL PROCESSLIST pour vérifier ce que fait le thread ID 1396 avec sa transaction #1701. Il est très probablement dans un état « endormi ». Donc : une transaction active (#1701) avec un verrou actif, elle a même apporté quelques modifications car elle a une entrée de journal d'annulation... mais est actuellement inactive. C'est le fil que vous devez tuer. Ces changements sont perdus. 
N'oubliez pas que ne rien faire dans MySQL ne signifie pas ne rien faire en général. Si vous obtenez des enregistrements de MySQL et créez un CSV pour le téléchargement FTP, la connexion MySQL est inactive pendant le téléchargement FTP. 
En fait, si le processus utilisant MySQL et le serveur MySQL se trouvent sur la même machine, qui exécute Linux, et que vous disposez des privilèges root, il existe un moyen de savoir quel processus possède le verrou de connexion demandé. Cela permet à son tour de déterminer (en fonction de l'utilisation du processeur, ou au pire strace -ff -p pid) si le processus fait vraiment  quelque chose, pour aider à déterminer s'il est sécuritaire de tuer quelqu'un. 
Pourquoi cela arrive-t-il ? 
J'ai vu cela se produire avec des applications Web qui utilisent des connexions MySQL "persistantes" ou "regroupées", maintenant généralement avec très peu de gain de temps : l'instance d'application Web se termine, mais pas la connexion , elle est donc encore verrouillée. Rester vivant... et bloque les autres. 
Une autre approche intéressante  que j'ai trouvée consistait, dans l'hypothèse ci-dessus, à exécuter une requête qui renvoie certaines lignes  et à n'en récupérer que certaines . Si la requête n'est pas définie sur "auto-clean" (mais le DBA sous-jacent le fait), elle maintiendra la connexion ouverte et empêchera un verrouillage complet de la table. J'ai rencontré cela avec un morceau de code qui vérifiait qu'une ligne existait en la sélectionnant et en vérifiant si elle contenait une erreur (elle n'existe pas) ou non (elle doit exister), mais sans réellement récupérer la ligne. 
PHP et PDO
PDO a des capacités de connexion persistantes. C'est ainsi que je m'assure que PDO ne regroupe pas les connexions et ne ferme pas chaque connexion. Très désordonné. 
Lors de l'ouverture, définissez les options (la quatrième option est new PDO()) : 
PDO::ATTR_PERSISTENT => false
Lorsque déconnecté : 
// We should have no transactions and no locks.
// So we discard them.
try {
    $pdo->exec('ROLLBACK WORK');
    $pdo->exec('UNLOCK TABLES');
} catch (Exception $err) {
    // Send a mail
}
// No cooperative locks. So this will not hurt a bit.
try {
    $pdo->exec('DO RELEASE_ALL_LOCKS()');
} catch (Exception $err) {
    // Send a mail
}
// Ensure the connection withers on the vine, but not too soon.
$pdo->exec('SET wait_timeout = 5');

// $pdo->setAttribute(PDO::ATTR_TIMEOUT, 5);
// If nothing else works!
// try {
//     $pdo->exec('KILL CONNECTION_ID()');
// } catch (Exception $err) {
//     // Exception here is expected: "Query execution was interrupted"
// }
// Invoke the garbage collector
$pdo = NULL;
Demandez à la base de données
 Si vous disposez de la dernière version de MySQL,  mais pas trop nouvelle  car  elle sera obsolète , une autre façon de trouver le coupable est (encore une fois, vous avez besoin du mode information d'autorisation) 
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS 
     WHERE LOCK_TRX_ID IN 
        (SELECT BLOCKING_TRX_ID FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS);
La solution réelle prend du temps et du travail
Les problèmes sont souvent causés par cette architecture : 

Lorsque l'application Web se termine ou qu'une instance de thread léger d'application Web se termine, le pool de conteneurs/connexions peut ne pas . C'est le conteneur qui maintient la connexion ouverte, donc évidemment la connexion n'est pas fermée. Comme prévu, MySQL ne considère pas l'opération terminée. 
Si l'application Web ne nettoie pas après elle-même (aucune transaction ROLLBACK或COMMIT，没有UNLOCK TABLES etc.), alors tout ce que l'application Web a commencé à faire  existe toujours  et peut toujours bloquer tout le monde. 
Alors il y a deux solutions. Pire encore, réduire le délai d'inactivité. Mais devinez ce qui se passe si vous attendez trop longtemps entre les requêtes (littéralement : « le serveur MySQL a disparu »). Vous pouvez ensuite utiliser mysql_ping s'il est disponible (bientôt obsolète. PDO a une solution de contournement .  Alternativement  vous pouvez vérifier  l'erreur  et rouvrir la connexion si elle se produit (c'est la méthode Python). Donc - pour un petite commission de performance - c'est faisable 
.
Des solutions meilleures et plus intelligentes ne sont pas si simples à mettre en œuvre. Essayez de laisser le script nettoyer après lui-même, assurez-vous de récupérer toutes les lignes ou de libérer toutes les ressources de requête, d'intercepter toutes les exceptions et de les gérer correctement, ou, si possible, ignorez complètement les connexions persistantes. Laissez chaque instance créer sa propre connexion ou utilisez un pilote de pool intelligent  (en PHP PDO, utilisez PDO::ATTR_PERSISTENT 显式设置为 false). 
 Alternativement (comme en PHP), vous pouvez demander au destructeur et au gestionnaire d'exceptions de forcer le nettoyage de la connexion en validant ou en annulant la transaction (ce qui devrait suffire), et peut-être même en émettant un déverrouillage de table explicite et RELEASE_ALL_LOCKS (), ou en soumettant connexion suicide (KILL CONNECTION_ID()) pour de bons résultats. 
Je ne connais pas de moyen d'interroger les ressources d'ensemble de résultats existantes pour les libérer ; le seul moyen est de sauvegarder ces ressources  dans un tableau privé.