Comment puis-je identifier ce qui bloque une requête sur une instance de base de données qui exécute Amazon RDS PostgreSQL ou Aurora PostgreSQL ?

Résolution

Les transactions non validées peuvent entraîner le blocage, la mise en veille et l'échec des nouvelles requêtes en cas de dépassement du délai de verrouillage ou du délai d'expiration des instructions. Pour résoudre ce problème, identifiez et arrêtez la transaction qui bloque la requête.

1.    Exécutez la requête suivante sur la vue pg_stat_activity pour identifier l’état actuel de la transaction bloquée :

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 est Lock, cela signifie que la requête est bloquée par d'autres transactions ou requêtes. Si la colonne wait_event_type contient une autre valeur, cela signifie qu'il existe un goulot d'étranglement affectant les performances lié à des ressources telles que l’UC, 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 Meilleures pratiques d'utilisation de PostgreSQL.

Si vous avez activé Performance Insights, consultez la charge de base de données regroupée par événements d'attente, hôtes, requêtes SQL ou utilisateurs, afin d'identifier les transactions bloquées. Pour en savoir plus, consultez la page Surveillance de la charge de base de données avec Performance Insights sur Amazon RDS.

2.    Si la valeur de la colonne wait_event_type est Lock, exécutez la commande suivante pour identifier la cause de la transaction bloquée :

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;

3.    Vérifiez les colonnes qui comportent un préfixe bloquant. Dans la table d'exemple suivante, vous pouvez voir que la transaction bloquée s'exécute sur l'hôte 27.0.3.146 et utilise psql. Utilisez blocking_user, blocking_user_addr et blocking_client_port pour identifier les sessions qui bloquent les transactions.

blocked_pid                           | 9069
blocked_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;

Important : 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.

4.    Exécutez la requête suivante pour arrêter les transactions :

SELECT pg_terminate_backend(PID);

Remarque : Remplacez PID par la valeur blocking_pid du processus que vous avez identifié à l'étape précédente.

Informations connexes

Documentation PostgreSQL pour l'affichage des verrous

Documentation PostgreSQL pour les fonctions de signalisation du serveur

Documentation PostgreSQL pour la description de wait_event

Wiki PostgreSQL pour la surveillance des verrous

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