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 ?

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. Ces transactions peuvent apparaître comme NULL. Suivez ces étapes pour identifier la requête ou la session qui pourrait bloquer votre requête.

Solution

Identifier les transactions non validées

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

select * from information_schema.innodb_trx\G

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

Pour MySQL 5.7 et versions antérieures :

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;

Pour MySQL 8.0 :

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       performance_schema.data_lock_waits w
INNER JOIN information_schema.innodb_trx b
  ON b.trx_id = w.blocking_engine_transaction_id
INNER JOIN information_schema.innodb_trx r
  ON r.trx_id = w.requesting_engine_transaction_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 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 ou l'ID de thread bloquant, arrêtez la transaction en exécutant la procédure suivante :

Remarque : avant d'exécuter cette procédure, déterminez si vous avez besoin de la transaction ou si vous pouvez l'arrêter en toute sécurité.

CALL mysql.rds_kill(thread-ID);

Remarque : l'arrêt ou l'annulation d'une opération de longue durée peut prendre beaucoup de temps et nécessiter des entrées/sorties importantes.

Informations connexes

Arrêt d'un session ou d'une requête

Options pour les instances de base de données MySQL

La table INFORMATION_SCHEMA_INNODB_TRX sur le site Web MySQL

Identification des transactions bloquées sur le site Web MySQL 

Tâches d'administrateur de bases de données courantes pour les instances de base de données MySQL