Comment identifier ce qui a bloqué une requête sur mon instance de base de données Amazon RDS pour PostgreSQL ou Aurora compatible avec PostgreSQL ?

Résolution

Les transactions non validées peuvent bloquer les nouvelles requêtes, les mettre en veille ou provoquer leur échec lorsqu'elles dépassent le délai d'attente du verrouillage ou le délai d'expiration de l’instruction. Pour résoudre ce problème, identifiez et arrêtez les transactions non validées.

Identifier les transactions bloquées

Pour identifier les transactions bloquées, activez Performance Insights et utilisez Database Insights. Utilisez le graphique de charge de la base de données pour consulter les dimensions de charge de la base de données pour Waits, Host, SQL ou User. Pour plus d’informations, consultez la section Surveillance de la charge de base de données avec Performance Insights sur Amazon RDS.

Pour identifier l'état actuel de la transaction bloquée, exécutez l'instruction suivante :

SELECT * FROM pg_stat_activity WHERE query iLIKE '%TABLE NAME%' ORDER BY state;

Remarque : remplacez TABLE NAME par le nom ou la condition de votre table.

Si la valeur de la colonne wait_event_type n’est pas Verrouiller, cela signifie qu'il existe un goulot d'étranglement affectant les performances lié à des ressources telles que le processeur, le stockage ou la capacité du réseau. Pour résoudre les problèmes de goulots d'étranglement qui affectent les performances, ajustez celles de votre base de données. Par exemple, vous pouvez ajouter des index, réécrire des requêtes ou exécuter des commandes vacuum et analyze. Pour en savoir plus, consultez la page Bonnes pratiques d'utilisation de PostgreSQL.

Si la valeur de la colonne wait_event_type est Verrouiller, d'autres transactions ou requêtes bloquent la requête. Pour identifier la cause de la transaction bloquée, exécutez l'instruction suivante :

SELECT blocked_locks.pid     AS blocked_pid,       blocked_activity.usename  AS blocked_user,  
       blocked_activity.client_addr as blocked_client_addr,  
       blocked_activity.client_hostname as blocked_client_hostname,  
       blocked_activity.client_port as blocked_client_port,  
       blocked_activity.application_name as blocked_application_name,  
       blocked_activity.wait_event_type as blocked_wait_event_type,  
       blocked_activity.wait_event as blocked_wait_event,  
       blocked_activity.query    AS blocked_statement,  
       blocking_locks.pid     AS blocking_pid,  
       blocking_activity.usename AS blocking_user,  
       blocking_activity.client_addr as blocking_user_addr,  
       blocking_activity.client_hostname as blocking_client_hostname,  
       blocking_activity.client_port as blocking_client_port,  
       blocking_activity.application_name as blocking_application_name,  
       blocking_activity.wait_event_type as blocking_wait_event_type,  
       blocking_activity.wait_event as blocking_wait_event,  
       blocking_activity.query   AS current_statement_in_blocking_process  
 FROM  pg_catalog.pg_locks         blocked_locks  
    JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid  
    JOIN pg_catalog.pg_locks         blocking_locks   
        ON blocking_locks.locktype = blocked_locks.locktype  
        AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE  
        AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation  
        AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page  
        AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple  
        AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid  
        AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid  
        AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid  
        AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid  
        AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid  
        AND blocking_locks.pid != blocked_locks.pid  
    JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid  
   WHERE NOT blocked_locks.granted ORDER BY blocked_activity.pid;

Pour identifier les sessions qui bloquent les transactions, utilisez blocking_user, blocking_user_addr et blocking_client_port. Dans l'exemple de sortie suivant, l'utilisateur principal utilise psql pour bloquer la transaction exécutée sur l'hôte 27.0.3.146. Le blocking_pid est 8740.

blocked_pid                           | 9069blocked_user                          | master  
blocked_client_addr                   | 27.0.3.146  
blocked_client_hostname               |  
blocked_client_port                   | 50035  
blocked_application_name              | psql  
blocked_wait_event_type               | Lock  
blocked_wait_event                    | transactionid  
blocked_statement                     | UPDATE test_tbl SET name = 'Jane Doe' WHERE id = 1;  
blocking_pid                          | 8740  
blocking_user                         | master  
blocking_user_addr                    | 27.0.3.146  
blocking_client_hostname              |  
blocking_client_port                  | 26259  
blocking_application_name             | psql  
blocking_wait_event_type              | Client  
blocking_wait_event                   | ClientRead  
current_statement_in_blocking_process | UPDATE tset_tbl SET name = 'John Doe' WHERE id = 1;

Arrêter les transactions

Avant de mettre fin à des transactions, évaluez l'effet potentiel de chaque transaction sur l'état de votre base de données et de votre application.

Pour arrêter les transactions, exécutez l'instruction suivante :

SELECT pg_terminate_backend(PID);

Remarque : remplacez le PID par les transactions blocking_pid.

Informations connexes

Événements d'attente liés à Amazon Aurora PostgreSQL