Est-il possible de montrer tous les verrous qui sont actifs dans une base de données mysql ?
J'ai lancé ce programme mais il y a tellement de lignes que je n'arrive pas à atteindre le sommet une fois qu'il est terminé.
Réponses
Trop de publicités?Voir le lien de Marko pour les tables InnoDB et les mises en garde.
Pour MyISAM, il n'y a pas de solution facile du type "voici la requête incriminée". Vous devriez toujours commencer par une liste de processus. Mais assurez-vous d'inclure le mot-clé complet afin que les requêtes imprimées ne soient pas tronquées :
SHOW FULL PROCESSLIST;Cela vous montrera une liste de tous les processus actuels, leur requête SQL et leur état. En général, si une seule requête provoque le blocage de plusieurs autres, elle devrait être facile à identifier. Les requêtes affectées auront un statut de Locked et la requête incriminée restera seule, peut-être en attente de quelque chose d'intensif, comme une table temporaire.
Si ce n'est pas évident, vous devrez utiliser vos pouvoirs de déduction SQL pour déterminer quelle partie du SQL incriminé est à l'origine de vos problèmes.
@Dan Carley Lorsqu'une requête est en attente d'exécution parce que la ligne qu'elle est censée traiter est verrouillée par une autre transaction, la liste des processus affiche "updating" et non "locked" dans l'instance MySQL (v5.7). Je ne suis pas sûr de voir le statut "verrouillé" même si la ligne est verrouillée.
@VishnuPedireddi oui, c'est mon expérience. IMO la détection des verrous est cassée sur MySQL. Il devrait y avoir un événement d'attente de verrou et le statut devrait être affiché en attente d'un verrou. Sur toutes les autres bases de données relationnelles majeures, Postgres, Oracle, SQL Server, DB2, il y a des attentes de verrouillage spécifiques. Sur MySQL, elles sont cachées sous wait/io/sql/table/handler qui couvre également la lecture des tampons par le CPU, l'attente des tampons par les E/S et le verrouillage au niveau de la ligne !
psayre23
Points
358
Si vous utilisez InnoDB et avez besoin de vérifier les requêtes en cours d'exécution, je recommande
show engine innodb status;
comme mentionné dans le lien de Marko. Cela vous donnera la requête de verrouillage, combien de lignes/tables sont verrouillées par celle-ci, etc. Regardez sous TRANSACTIONS.
Le problème de l'utilisation de SHOW PROCESSLIST c'est que vous ne verrez pas les verrous à moins que d'autres requêtes ne fassent la queue.
HexInteractive
Points
239
Essayez SHOW OPEN TABLES :
show open tables where In_Use > 0 ;
Peter V. Mørch
Points
732
Aucune des réponses ne peut montrer tous les verrous qui sont actuellement détenus.
Faites-le par exemple dans mysql dans un terminal.
start transaction;
update someTable set name="foobar" where ID=1234;
-- but no rollback or commit - just let it sit thereIl est clair que la transaction ci-dessus détient un verrou, car la transaction est toujours active. Mais aucune requête n'est en cours en ce moment et personne n'attend de verrou nulle part (du moins pas encore).
INFORMATION_SCHEMA.INNODB_LOCKS est vide, ce qui est logique étant donné que le documentation car il n'y a qu'une seule transaction et personne n'attend de verrou. Aussi INNODB_LOCKS est de toute façon déprécié.
SHOW ENGINE INNODB STATUS est inutile : someTable n'est pas du tout mentionné
SHOW FULL PROCESSLIST est vide, car le coupable n'exécute pas de requête en ce moment.
Vous pouvez utiliser INFORMATION_SCHEMA.INNODB_TRX , performance_schema.events_statements_history y performance_schema.threads pour extraire les requêtes que les transactions actives ont exécutées dans le passé, comme indiqué dans le document mon autre réponse mais je n'ai pas trouvé de moyen de le voir. someTable est verrouillé dans le scénario ci-dessus.
Les suggestions faites dans les autres réponses jusqu'à présent ne vous aideront pas du tout.
Disclaimer : Je n'ai pas installé innotop et je n'ai pas pris la peine de le faire. Peut-être que pourrait travail.
J'avais justement l'intention de rédiger une question portant spécifiquement sur ce scénario (démarrer la transaction, exécuter la mise à jour et attendre), puis j'ai remarqué votre réponse. J'ai ce problème en production. J'ai eu l'idée de détecter un verrou "bloqué" dans le code lorsque j'obtiens l'erreur "Lock wait timeout exceeded", puis de faire deux choses en même temps : réexécuter la requête et exécuter SHOW ENGINE INNODB STATUS (à partir de l'autre thread ou processus, sur une deuxième connexion). Mais j'aimerais avoir un moyen plus simple...
Je ne suis pas un expert en la matière, mais la transaction de l'exemple ne dépend de rien, alors pourquoi détiendrait-elle un verrou ? Elle n'aura besoin que d'un court verrou pendant commit pour effectuer l'écriture. Vous avez besoin par exemple de SELECT ... FOR UPDATE pour verrouiller explicitement.
@MattiasWallin SELECT FOR UPDATE créera un verrou, car SELECT ne le ferait pas autrement. Mais UPDATE crée également un verrou jusqu'à ce que la transaction soit annulée ou soumise. (Je ne suis pas un expert non plus)
Anvesh
Points
664
Référence tirée de cet article.
Vous pouvez utiliser le script ci-dessous :
SELECT
pl.id
,pl.user
,pl.state
,it.trx_id
,it.trx_mysql_thread_id
,it.trx_query AS query
,it.trx_id AS blocking_trx_id
,it.trx_mysql_thread_id AS blocking_thread
,it.trx_query AS blocking_query
FROM information_schema.processlist AS pl
INNER JOIN information_schema.innodb_trx AS it
ON pl.id = it.trx_mysql_thread_id
INNER JOIN information_schema.innodb_lock_waits AS ilw
ON it.trx_id = ilw.requesting_trx_id
AND it.trx_id = ilw.blocking_trx_id
- Réponses précédentes
- Plus de réponses
1 votes
Vous pouvez interroger les tables INNODB_LOCK_WAITS et INNODB_LOCKS.