Comment puis-je identifier ce qui bloque une requête sur une instance de base de données exécutant Amazon RDS PostgreSQL ou Aurora PostgreSQL ?
Date de la dernière mise à jour : 07/11/2019
J'ai essayé d'exécuter une requête sur une instance de base de données qui exécute Amazon Relational Database Service (Amazon RDS) PostgreSQL ou Amazon Aurora PostgreSQL. Malheureusement, la requête a été bloquée. Pourtant, aucune autre requête n'était exécutée en même temps. Pourquoi la requête a-t-elle été bloquée et comment résoudre ce problème ?
Résolution
Le plus souvent, les requêtes sont bloquées en raison de transactions non validées. Les transactions non validées peuvent entraîner le blocage, la mise en veille et l'échec de nouvelles requêtes lorsqu'elles dépassent le délai d'attente de verrouillage ou le délai d'attente de l'instruction. Pour résoudre ce problème, identifiez d'abord la transaction responsable du blocage, puis arrêtez-la.
1. Identifiez l'état actuel de la transaction bloquée en exécutant la requête suivante sur la table pg_stat_activity :
SELECT * FROM pg_stat_activity WHERE query iLIKE '%TABLE NAME%' ORDER BY state;Remarque : remplacez TABLE NAME par votre propre nom de table ou condition.
Si la valeur de la colonne wait_event_type est « Lock » (Verrou), c'est que la requête est bloquée par d'autres transactions ou requêtes. Si la colonne wait_event_type a une autre valeur, c'est qu'il y a un goulot d'étranglement avec les ressources telles que le processeur, le stockage ou la capacité réseau. Pour résoudre le problème de goulots d'étranglement des performances, ajustez les performances de votre base de données, notamment en ajoutant des index, en réécrivant des requêtes ou en exécutant la tâche vider et analyser. Pour plus d'informations, consultez Bonnes pratiques pour utiliser PostgreSQL.
Si vous avez activé Analyse des performances sur votre instance de base de données, vous pouvez également identifier les transactions bloquées en affichant la charge de base de données qui est regroupée par événement d'attente, hôtes, requêtes SQL ou utilisateurs. Pour plus d'informations, consultez Utilisation d'Amazon RDS Performance Insights.
2. Si la valeur de la colonne wait_event_type est « Lock » (Verrou), vous pouvez identifier la cause de la transaction bloquée en exécutant la commande 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;3. Passez en revue les colonnes qui ont un préfixe de blocage . Dans l'exemple de tableau suivant qui a été généré par cette requête, vous pouvez voir que la transaction bloquée s'exécute sur l'hôte 27.0.3.146 et utilise psql:
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;Astuce : utilisez blocking_user, blocking_user_addr, et blocking_client_port pour identifier les sessions responsables du blocage des transactions.
Important : avant de mettre fin aux transactions, évaluez l'impact potentiel de chaque transaction sur l'état de votre base de données et de votre application.
4. Après avoir examiné l'impact potentiel de chaque transaction, arrêtez les transactions en exécutant la requête suivante :
SELECT pg_terminate_backend(PID);Remarque : remplacez PID par blocking_pid du processus que vous avez identifié à l'étape 3.
Informations connexes
Documentation PostgreSQL pour l'affichage des verrous
Documentation PostgreSQL pour les fonctions de signalement de serveur
Documentation PostgreSQL pour la description de wait_event
Wiki PostgreSQL pour la Surveillance des verrous
Cet article vous a-t-il été utile ?
Besoin d'aide pour une question technique ou de facturation ?