Traqueur, problème de verrouillage et transactions préparées

Téléchargement du traqueur pour PostgreSQL 11, 12, 13, 14 et 15
Téléchargement du traqueur pour PostgreSQL 10
Téléchargement du traqueur pour PostgreSQL 9.6
Téléchargement du traqueur pour PostgreSQL 9.5
Téléchargement du traqueur pour PostgreSQL 9.4
Téléchargement du traqueur pour PostgreSQL 9.3
Journal des changements
Signalement de bugs via le forum
Licence identique à celle de PostgreSQL, open source type BSD
Dossier des versions

      Dans cet article concernant les problèmes de verrouillage, certaines transactions étaient bloquées par d'autres transactions qui, elles, n'étaient bloquées par aucune transaction. L'information concernant les sessions comprenant les transactions bloquantes finales était donnée via un tableau d'entiers, chaque entier étant le numéro du processus serveur (PID) de la session.
      Parfois, cependant, ce ne sont pas des transactions liées à des sessions qui bloquent et le PID n'est plus pertinent.
      Démonstration avec PostgreSQL 10 beta :

select version(); version ----------------------------------------------------------------------------------------------------- PostgreSQL 10beta3 on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18) 6.3.0 20170516, 64-bit show max_prepared_transactions; max_prepared_transactions --------------------------- 100 create table t1(c1 int); CREATE TABLE insert into t1 values(1),(2),(3); INSERT 0 3 -- SESSION 1 start transaction; START TRANSACTION select pg_backend_pid(); pg_backend_pid ---------------- 12459 update t1 set c1 = 11 where c1 = 1; UPDATE 1 -- SESSION B start transaction; START TRANSACTION select pg_backend_pid(); pg_backend_pid ---------------- 12494 update t1 set c1 = 22 where c1 = 2; UPDATE 1 prepare transaction 'patanok'; PREPARE TRANSACTION -- SESSION C start transaction; START TRANSACTION select pg_backend_pid(); pg_backend_pid ---------------- 12521 update t1 set c1 = 33 where c1 = 3; UPDATE 1 prepare transaction 'nouka'; PREPARE TRANSACTION \q -- SESSION D select pg_backend_pid(); pg_backend_pid ---------------- 12565 truncate table t1; -- EN ATTENTE -- SESSION E select pg_backend_pid(); pg_backend_pid ---------------- 12590 select * from t1; -- EN ATTENTE ./traqueur.sh -d 1 traqueur 0.10.03beta - outil de diagnostic performance pour PostgreSQL 9.6, 10 INFORMATION, pas d'options de connexions indiquees, utilisation de la base traqueur detectee ... INFORMATION, preparation de la collecte ... INFORMATION, execution de la collecte et presentation des resultats ... busy_pc | distinct_exe | db | pid | pg_user | client_ip | application | query | wait_event_type | blockers ---------+--------------+----------+-------+----------+-----------+-------------+--------------------+-----------------+----------- 100 | 1 / 10 | postgres | 12565 | postgres | | psql | truncate table t1; | Lock | {0,12459} 100 | 1 / 10 | postgres | 12590 | postgres | | psql | select * from t1; | Lock | {0,12459} -- SESSION admin select pg_terminate_backend(12459); pg_terminate_backend ---------------------- t -- les sessions D et E attendent toujours ./traqueur.sh -d 1 traqueur 0.10.03beta - outil de diagnostic performance pour PostgreSQL 9.6, 10 INFORMATION, pas d'options de connexions indiquees, utilisation de la base traqueur detectee ... INFORMATION, preparation de la collecte ... INFORMATION, execution de la collecte et presentation des resultats ... busy_pc | distinct_exe | db | pid | pg_user | client_ip | application | query | wait_event_type | blockers ---------+--------------+----------+-------+----------+-----------+-------------+--------------------+-----------------+---------- 100 | 1 / 10 | postgres | 12565 | postgres | | psql | truncate table t1; | Lock | {0} 100 | 1 / 10 | postgres | 12590 | postgres | | psql | select * from t1; | Lock | {0} -- SESSION admin select pg_terminate_backend(12494); pg_terminate_backend ---------------------- t -- les sessions D et E attendent toujours ./traqueur.sh -d 1 traqueur 0.10.03beta - outil de diagnostic performance pour PostgreSQL 9.6, 10 INFORMATION, pas d'options de connexions indiquees, utilisation de la base traqueur detectee ... INFORMATION, preparation de la collecte ... INFORMATION, execution de la collecte et presentation des resultats ... busy_pc | distinct_exe | db | pid | pg_user | client_ip | application | query | wait_event_type | blockers ---------+--------------+----------+-------+----------+-----------+-------------+--------------------+-----------------+---------- 100 | 1 / 10 | postgres | 12565 | postgres | | psql | truncate table t1; | Lock | {0} 100 | 1 / 10 | postgres | 12590 | postgres | | psql | select * from t1; | Lock | {0} -- SESSION admin select * from pg_prepared_xacts; transaction | gid | prepared | owner | database -------------+---------+-------------------------------+----------+---------- 2452 | patanok | 2017-08-28 14:32:46.015907+02 | postgres | postgres 2453 | nouka | 2017-08-28 14:34:16.135918+02 | postgres | postgres rollback prepared 'patanok'; ROLLBACK PREPARED -- les sessions D et E attendent toujours -- SESSION admin commit prepared 'nouka'; COMMIT PREPARED -- SESSION D ... TRUNCATE TABLE -- SESSION E ... c1 ---- (0 ligne) ./traqueur.sh -d 1 traqueur 0.10.03beta - outil de diagnostic performance pour PostgreSQL 9.6, 10 INFORMATION, pas d'options de connexions indiquees, utilisation de la base traqueur detectee ... INFORMATION, preparation de la collecte ... INFORMATION, execution de la collecte et presentation des resultats ... busy_pc | distinct_exe | db | pid | pg_user | client_ip | application | query | wait_event_type | blockers ---------+--------------+----+-----+---------+-----------+-------------+-------+-----------------+---------- (0 ligne)

      Les sessions D et E étaient bloquées par 3 coupables, la session A (process serveur 12459) et les transactions préparées patanok et nouka. Le traqueur indique dans ce cas le ou les identifants des process bloquants et, depuis la 0.10.03, un 0 si une ou plusieurs transactions préparées sont bloquantes.
      Lorsqu'une transaction est préparée, elle est enregistrée sur disque, prête à être validée et n'est plus reliée à une session. Terminer la session d'origine, volontairement ou en tuant le processus, ne libère donc pas les verrous de la transaction préparée et ne résout pas le problème de blocage.
      Si une telle situation se présente avec des transactions préparées orphelines au sens applicatif, il convient pour le DBA de les annuler ou de les valider afin de débloquer la situation. Interroger pg_locks et réaliser une jointure avec pg_prepared_xacts sur le numéro de transaction permet de cibler les transactions préparées bloquantes.
      Afin de faciliter la recherche et la prise de décision concernant les bloqueurs finaux, une future version du traqueur donnera peut-être des informations complémentaires sur ces bloqueurs via une colonne supplémentaire, que les bloqueurs soient des transactions habituelles liées à une session ou des transactions préparées.

Mise à jour : 29/08/2017