Pourquoi une requête à mon instance de base de données MySQL Amazon RDS a-t-elle été bloquée alors qu'il n'y a pas d'autre session active ?

Date de la dernière mise à jour : 16/11/2020

J'ai essayé d'exécuter une requête sur mon instance de base de données Amazon Relational Database Service (Amazon RDS) qui exécute MySQL, mais ma requête a été bloquée. Aucune autre requête n'était alors en cours d'exécution. Pour quelle raison la requête a-t-elle été bloquée et comment résoudre ce problème ?

Brève description

Les requêtes bloquées peuvent se produire, car une transaction dans InnoDB attend qu'une autre transaction libère un verrou. Les requêtes peuvent également être bloquées en raison de transactions non validées (pouvant apparaître comme NULL). Suivez les étapes ci-dessous pour identifier la requête ou la session qui pourrait bloquer votre requête.

Résolution

Identifier les transactions non validées

1.    Identifiez toutes les transactions en cours en exécutant la requête suivante sur la table INNODB_TRX :

select * from information_schema.innodb_trx\G

2.    Exécutez la requête suivante pour voir quelles transactions sont en attente et quelles transactions les bloquent :

SELECT
r.trx_id waiting_trx_id,
r.trx_mysql_thread_id waiting_thread,
r.trx_query waiting_query,
b.trx_id blocking_trx_id,
b.trx_mysql_thread_id blocking_thread,
b.trx_query blocking_query
FROM information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b
ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r
ON r.trx_id = w.requesting_trx_id;

Remarque : la transaction bloquée ne peut pas se poursuivre tant que l'autre transaction n'est pas validée ou annulée.

Lors de l'identification des transactions de blocage, une valeur NULL est signalée pour la requête de blocage si la session qui a émis la requête est devenue inactive. Dans ce cas, utilisez la requête de l'étape 2 pour trouver l'ID de liste de traitement blocking_thread.

3.    Pour MySQL 5.7 et les versions ultérieures, exécutez la requête suivante pour déterminer l'identifiant THREAD_ID de la transaction de blocage par l'ID de la liste de traitement blocking_thread substituant :

SELECT THREAD_ID FROM performance_schema.threads WHERE PROCESSLIST_ID = blocking_thread;

4.   Utilisez THREAD_ID pour interroger la table events_statements_current du schéma de performances. Cela détermine la dernière requête exécutée par le thread.

Remarque : veillez à remplacer le THREAD_ID par la valeur renvoyée à l'étape 3.

SELECT THREAD_ID, SQL_TEXT FROM performance_schema.events_statements_current WHERE THREAD_ID = THREAD_ID;

5. Après avoir identifié la session de blocage ou l'ID de thread, arrêtez la transaction en exécutant la procédure suivante :

CALL mysql.rds_kill(thread-ID);

Remarque : l'arrêt ou l'annulation d'une opération de longue durée peuvent être fastidieux et gourmands en E/S.