Pourquoi une requête adressée à mon instance de base de données Amazon RDS for MySQL a-t-elle été bloquée alors qu'aucune autre session n'est active ?

Lecture de 4 minute(s)

Lorsque j'exécute une requête sur mon instance de base de données Amazon Relational Database Service (Amazon RDS) for MySQL, la requête est bloquée. Aucune autre requête n'est en cours d'exécution. Je souhaite résoudre ce problème.

Résolution

Un blocage des requêtes se produit parce qu'une transaction dans InnoDB attend qu'une autre transaction libère un verrou. Les transactions non validées peuvent également bloquer les requêtes. Ces transactions apparaissent sous la forme NULL.

Utiliser la table INNODB_TRX pour identifier les transactions non validées

Pour identifier la requête ou la session qui bloque votre requête, procédez comme suit :

Pour afficher les transactions en cours, exécutez la requête suivante dans la table INNODB_TRX :

select * from information_schema.innodb_trx\G

Pour afficher les transactions en attente et celles qui les bloquent, exécutez l'une des requêtes suivantes en fonction de votre version de MySQL.
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;

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 avoir lieu que lorsque l'autre transaction est validée ou restaurée. Lorsque la session qui a émis la requête est inactive, une valeur NULL est signalée pour la requête bloquante. Dans ce cas, utilisez la requête précédente pour trouver l’ID PROCESSLIST_ID de blocking_thread.

Pour MySQL 5.7 ou version ultérieure, exécutez la requête suivante pour déterminer l’ID PROCESSLIST_ID de la transaction bloquante à l'aide de l’ID THREAD_ID bloquant de substitution :

SELECT PROCESSLIST_ID FROM performance_schema.threads where THREAD_ID = blocking;

Exécutez la requête suivante dans la table events_statements_current avec l’ID THREAD_ID pour déterminer la dernière requête exécutée par le thread :

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

Remarque : Remplacez THREAD_ID par la valeur que vous avez reçue à l'étape 3.

Exécutez la commande suivante pour arrêter la transaction :
```
CALL mysql.rds_kill(PROCESSLIST_ID);
```
Remarque : L'arrêt ou la restauration d'une transaction de longue durée prend du temps et requiert un grand volume d’I/O.

Identifier les transactions bloquant XA

Si blocking_thread est 0 et CALL mysql.rds_kill(PROCESSLIST_ID); ne fonctionne pas, il se peut qu'une transaction XA vous bloque.

Pour identifier la transaction XA à l'origine du blocage, exécutez la commande suivante :

XA RECOVER;

La commande précédente renvoie une sortie pour les transactions XA qui sont à l'état Préparé : Pour arrêter le blocage, utilisez la colonne de données pour restaurer ou valider la transaction :

XA RECOVER;

+----------+--------------+--------------+--------------------+
| formatID | gtrid_length | bqual_length | data               |
+----------+--------------+--------------+--------------------+
|   1      |           10 |            0 | RePostTest         |
+----------+--------------+--------------+--------------------+
1 row in set (0.00 sec)

XA COMMIT 'RePostTest';
XA ROLLBACK 'RePostTest';

Si l'erreur suivante s'affiche lorsque vous effectuez la validation ou la restauration, convertissez le XID en hexadécimal et restaurez la transaction :

« ERROR 1397 (XAE04): XAER_NOTA: Unknown XID »

Pour récupérer la transaction, exécutez la commande suivante pour récupérer les paramètres gtrid_length et bqual_length afin d’extraire les parties nécessaires du champ de données :

mysql> xa recover convert xid;
+----------+--------------+--------------+----------------------------------------+
| formatID | gtrid_length | bqual_length | data                                   |
+----------+--------------+--------------+----------------------------------------+
|   1      |           10 |            0 | 0x5265506F737454657374                 |
+----------+--------------+--------------+----------------------------------------+
1 row in set (0.00 sec)

mysql> XA ROLLBACK X'5265506F737454657374';
Query OK, 0 rows affected (0.01 sec)

Pour plus d'informations sur la conversion des valeurs XID, consultez la page Instructions SQL de la transaction XA sur le site Web de MySQL.