Forums PostgreSQL.fr

Ours77

Membre

Recuperation de lignes non bloquées sur une table

Bonjour,
J'ai une table qui contient des millions d’éléments.

J'ai des processus a lancer dessus qui bloque certaines lignes avec la requete
select * from table for update;
qui est elle meme encapsuler dans une transaction;

Exemple:

Begin;
"select * from table limit 1 for update;" Le limit 1 n'est la que pour recupere une ligne a traiter
Mon processus;
"update ..." de la ligne;
End;

Je souhaiterai lancer un nouveau processus qui recupererai la première ligne non bloquée  avec une requête du genre 

select * from table where ligne_pas_bloque"

Si j'utilise la requete  A   "select * from table for share;"
la requete "select * from table"
me renvoi aussi les lignes qui sont prise dans la requete A
Si j'utilise la requete B  "select * from table for update;"
la requete  " select * from table"
ne me renvoi rien tant que B n'est pas terminé ce qui n'est pas optimale pour paralléliser les traitements sur ma table...

J'ai deja essayer de regarder la table pg_locks pour savoir quelle(s) ligne(s) sur la table était(ent) verrouillée(s) mais je ne suis pas sur que cela soit la bonne stratégie...

Pourriez vous m'indiquez comment effectuer une telle requête  soit une stratégie plus appropriée ?

Merci beaucoup

rjuju

Administrateur

Re : Recuperation de lignes non bloquées sur une table

Bonjour.
Il n'y a pas d'autre moyen que pg_locks pour connaitre le verouillage des ligne d'une table.

Vous pouvez sinon utiliser un where  pour essayer paralléliser rapidement les traitements (par ex, s'il y a un champ séquence un modulo avec le nombre de processeurs sur la séquence) ou LIMIT et OFFSET.

Vous pouvez également utiliser l'option NOWAIT du SELECT FOR UPDATE dans une boucle et gérer l'exception en cas d'erreur, ce qui vous permettra de continuer le traitement, mais dans ce cas il sera peut-être dur de s'assurer que chaque ligne n'est traitée qu'une seule fois.

---

Julien.
https://rjuju.github.io/

Ours77

Membre

Re : Recuperation de lignes non bloquées sur une table

Le pb avec la table pg_locks c'est que je ne voit que la transaction qui bloque :
"<IDLE> in transaction" dans la colonne current_query. Je ne vois pas la requete a l'interieur de la transaction (ou alors je ne sais pas comment faire....)
Comment je peux recupere le numero de la ligne qui est bloqué ?

rjuju

Administrateur

Re : Recuperation de lignes non bloquées sur une table

Il faut joindre le transactionid de la vue pg_locks avec le xmax de la table pour avoir la ligne bloquée je pense.

---

Julien.
https://rjuju.github.io/

dverite

Membre

Re : Recuperation de lignes non bloquées sur une table

La stratégie par introspection des locks est très délicate. De mémoire les lignes verrouillées ne sont pas dans pg_locks mais sur les pages disque de la table elle-même, l'intérêt étant qu'on peut verrouiller énormément de lignes sans consommer de mémoire ou d'espace disque ailleurs.

Une stratégie plus sûre serait plutôt d'implémenter la condition "ligne pas bloquée" avec une colonne dédiée à ça dans la table, le traitement s'appropriant la ligne avec un UPDATE conditionnel. Eventuellement cette colonne peut être un ID unique de traitement qui fait référence à une table des traitements pour un contrôle fin.

---

@DanielVerite
http://blog-postgresql.verite.pro/

Ours77

Membre

Re : Recuperation de lignes non bloquées sur une table

Je pense que je vais m'orienter vers votre solution (celle de dverite) parce que naviguer dans les tables "systemes" pour voir ce qui se passe ne me parait pas très "sain" pour un fonction normale.
Ce qui m'étonne c'est que trouver les lignes qui ne sont pas bloquées sur une table ne soient pas gérer "en natif" par PostgreSQL.
Tout les systemes qui font du traitement en paralleles de données doivent avoir ce pb non? Ou chacun le résout avec sa propre méthode?

rjuju

Administrateur

Re : Recuperation de lignes non bloquées sur une table

Je pense que chacun le fait avec sa propre méthode.

Le seul problème d'avoir une colonne indiquant que la ligne est bloquée, c'est qu'il faut que la mise à jour de cette colonne se fasse hors du traitement, sinon la valeur ne sera pas visible hors de la transaction et donc pour les autres traitements, ce qui peut complexifier le programme.

---

Julien.
https://rjuju.github.io/

Ours77

Membre

Re : Recuperation de lignes non bloquées sur une table

Effectivement plutot que

Begin;
"select * from table limit 1 for update;" Le limit 1 n'est la que pour recupere une ligne a traiter
Mon processus;
"update ..." de la ligne;
End;

Cela deviendra

select id from table where a_traiter=true limit 1; 
update table set a_traiter=false where id=10;
Begin;
Mon processus;
"update ..." de la ligne;
End;

gleu

Administrateur

Re : Recuperation de lignes non bloquées sur une table

Un tel traitement a toutes les chances d'échouer avec plusieurs utilisateurs. Des "SELECT id..." peuvent se dérouler en même temps et donc ramener le même id. Il serait préférable de faire un :

update table set a_traiter=false where id=(select id from table where a_traiter=true limit 1) returning id;

---

Guillaume.

Ours77

Membre

Re : Recuperation de lignes non bloquées sur une table

Merci pour la requête.
Je ne savais pas qu'un Update pouvait renvoyer des valeurs

gleu

Administrateur

Re : Recuperation de lignes non bloquées sur une table

i, il peut depuis la version 8.2. Comme le INSERT et le DELETE.

---

Guillaume.

dverite

Membre

Re : Recuperation de lignes non bloquées sur une table

Sinon le même  problème est évoqué sur stackoverflow:
http://stackoverflow.com/questions/3895 … postgresql
avec plusieurs pistes différentes proposées.
Aucune n'utilise pg_locks, ce qui à mon avis confirme ce qui est dit ici, mais certaines utilisent  SELECT FOR UPDATE NOWAIT avec récupération de l'erreur "lock_not_available", intéressant pour se passer du flag+update supplémentaire évoqués plus haut.

---

@DanielVerite
http://blog-postgresql.verite.pro/