Sélectionner une ligne non verrouillée dans Postgresql

cette fonctionnalité, SELECT ... SKIP LOCKED est implémentée dans Postgres 9.5. http://www.depesz.com/2014/10/10/waiting-for-9-5-implement-skip-locked-for-row-level-locks /

No Nooo :-)

je sais ce que l'auteur veut dire. J'ai une situation similaire et j'ai trouvé une bonne solution. Je commencerai par décrire ma situation. J'ai un tableau qui je stocker les messages qui doivent être envoyés à un moment précis. PG ne supporte pas l'exécution temporelle des fonctions, nous devons donc utiliser des démons (ou cron). J'utilise un script personnalisé qui ouvre plusieurs processus parallèles. Chaque processus sélectionne un ensemble de messages qui doivent être envoyés avec une précision de +1 sec / -1 sec. La table elle-même est dynamiquement mise à jour avec de nouveaux messages.

donc chaque processus a besoin de télécharger un ensemble de lignes. Cet ensemble de lignes ne peut pas être téléchargé par l'autre processus parce qu'il va faire beaucoup de désordre (certaines personnes recevraient des messages de couple alors qu'ils devraient recevoir un seul). C'est pourquoi nous devons verrouiller les rangs. La requête pour télécharger un ensemble de messages avec la serrure:

FOR messages in select * from public.messages where sendTime >= CURRENT_TIMESTAMP - '1 SECOND'::INTERVAL AND sendTime <= CURRENT_TIMESTAMP + '1 SECOND'::INTERVAL AND sent is FALSE FOR UPDATE LOOP
-- DO SMTH
END LOOP;

un procédé avec cette requête est lancée toutes les 0.5 secondes. Il en résulte que la requête suivante attend la première serrure pour déverrouiller les lignes. Cette approche entraîne d'énormes retards. Même lorsque nous utilisons NOWAIT la requête résultera en une Exception que nous ne voulons pas car il pourrait y avoir de nouveaux messages dans la table qui doivent être envoyés. Si l'utilisation simplement pour partager la requête s'exécutera correctement mais quand même cela prendra beaucoup de temps en créant des retards énormes.

pour le faire fonctionner nous faisons un peu magie:

1. changer la requête:

   FOR messages in select * from public.messages where sendTime >= CURRENT_TIMESTAMP - '1 SECOND'::INTERVAL AND sendTime <= CURRENT_TIMESTAMP + '1 SECOND'::INTERVAL AND sent is FALSE AND is_locked(msg_id) IS FALSE FOR SHARE LOOP
   -- DO SMTH
   END LOOP;
   

2. la fonction mystérieuse "is_locked(msg_id)" ressemble à ceci:

   CREATE OR REPLACE FUNCTION is_locked(integer) RETURNS BOOLEAN AS $$
   DECLARE
       id integer;
       checkout_id integer;
       is_it boolean;
   BEGIN
       checkout_id := ;
       is_it := FALSE;
   
       BEGIN
           -- we use FOR UPDATE to attempt a lock and NOWAIT to get the error immediately 
           id := msg_id FROM public.messages WHERE msg_id = checkout_id FOR UPDATE NOWAIT;
           EXCEPTION
               WHEN lock_not_available THEN
                   is_it := TRUE;
       END;
   
       RETURN is_it;
   
   END;
   $$ LANGUAGE 'plpgsql' VOLATILE COST 100;
   

bien sûr, nous pouvons personnaliser cette fonction sur n'importe quelle table vous avez dans votre base de données. À mon avis, il est préférable de créer une fonction de contrôle pour une table. Ajouter plus de choses à cette fonction ne peut le faire plus lent. Je prends plus de temps pour vérifier cette clause de toute façon il n'y a pas besoin de le faire encore plus lent. Pour moi, c'est la solution complète et cela fonctionne parfaitement.

maintenant que mes 50 processus fonctionnent en parallèle, chaque processus a un ensemble unique de nouveaux messages à envoyer. Une fois que les messages sont envoyés, Je ne fais que mettre à jour la ligne avec sent = TRUE et ne jamais y revenir.

j'espère que cette solution fonctionnera aussi pour vous (auteur). Si vous avez des questions laissez-moi savoir: -)

OH, et faites-moi savoir si ça a marché pour vous aussi.

j'utilise quelque chose comme ça:

select  *
into l_sms
from sms
where prefix_id = l_prefix_id
    and invoice_id is null
    and pg_try_advisory_lock(sms_id)
order by suffix
limit 1;

et n'oubliez pas d'appeler pg_advisory_unlock

si vous essayez d'implémenter une file D'attente, jetez un oeil à PGQ, qui a déjà résolu ce problème et d'autres. http://wiki.postgresql.org/wiki/PGQ_Tutorial

il semble que vous essayez de faire quelque chose comme saisir l'élément de la plus haute priorité dans une file qui n'est pas déjà pris en charge par un autre processus.

une solution envisageable serait d'ajouter une clause "where" limitant cette clause aux requêtes non traitées:

select * from queue where flag=0 order by id desc for update;
update queue set flag=1 where id=:id;
--if you really want the lock:
select * from queue where id=:id for update;
...

avec un peu de chance, la deuxième transaction sera bloquée pendant que la mise à jour du drapeau aura lieu, puis elle pourra continuer, mais le drapeau la limitera à la prochaine dans la ligne.

il est également probable qu'en utilisant le niveau d'isolation sérialisable, vous pouvez obtenir le résultat que vous voulez sans toute cette folie.

selon la nature de votre application, il peut y avoir de meilleures façons de mettre en œuvre cela que dans la base de données, comme un tuyau FIFO ou LIFO. De plus, il peut être possible d'Inverser l'ordre dans lequel vous en avez besoin et d'utiliser une séquence pour s'assurer qu'elles sont traitées de façon séquentielle.

cela peut être accompli par SELECT ... NOWAIT; un exemple est ici .

on dirait que vous cherchez un SELECT FOR SHARE.

http://www.postgresql.org/docs/8.3/interactive/sql-select.html#SQL-FOR-UPDATE-SHARE

pour action se comporte de manière similaire, sauf qu'il acquiert une serrure partagée plutôt qu'exclusive sur chaque ligne récupérée. Une serrure partagée empêche les autres transactions d'effectuer des mises à jour, des suppressions ou des sélections de mise à jour sur ces lignes, mais cela ne les empêche pas d'effectuer des mises à jour. SÉLECTIONNEZ POUR PARTAGER.

si des tables spécifiques sont nommées pour la mise à jour ou pour le partage, seules les lignes provenant de ces tables sont verrouillées; toutes les autres tables utilisées dans SELECT sont simplement lues comme d'habitude. A pour la mise à jour ou pour la clause de partage sans Liste de table affecte toutes les tables utilisées dans la commande. Si pour mettre à jour ou pour partager est appliqué à une vue ou sous-requête, il affecte toutes les tables utilisées dans la vue ou sous-requête.

Multiple pour la mise à jour et pour les clauses de partage peut être écrit s'il est nécessaire de spécifier un comportement de verrouillage différent pour différentes tables. Si le même tableau est mentionné (ou implicitement affecté) à la fois pour les clauses de mise à jour et pour les clauses de partage, alors il est traité comme pour la mise à jour. De même, un tableau est traité comme NOWAIT si cela est spécifié dans l'une des clauses le concernant.

pour la mise à jour et pour le partage ne peut pas être utilisé dans les contextes où les lignes retournées ne peuvent pas être clairement identifiées avec des lignes de tableau individuelles; par exemple, ils ne peut être utilisé avec agrégation.

Qu'essayez-vous d'accomplir? Pouvez-vous mieux expliquer pourquoi ni les mises à jour de ligne déverrouillées ni les transactions complètes feront ce que vous voulez?

mieux encore, pouvez-vous empêcher la discorde et tout simplement avoir chaque thread utiliser un offset différent? Cela ne fonctionnera pas bien si la partie pertinente de la table est mise à jour fréquemment; vous aurez toujours des collisions, mais seulement pendant la lourde charge d'insertion.

Select... order by id desc offset THREAD_NUMBER limit 1 for update

comme je n'ai pas encore trouvé de meilleure réponse, j'ai décidé d'utiliser le verrouillage dans mon application pour synchroniser l'accès au code qui fait cette requête.

Que Diriez-vous de ce qui suit? pourrait être traité de manière atomique que les autres exemples, mais doit encore être testés pour s'assurer de mes hypothèses ne sont pas mauvais.

UPDATE users SET flags = 1 WHERE id = ( SELECT id FROM users WHERE flags = 0 ORDER BY id DESC LIMIT 1 ) RETURNING ...;

vous serez probablement toujours bloqué avec le système de verrouillage que postgres utilise en interne pour fournir des résultats SELECT cohérents face à des mises à jour simultanées.

j'ai fait face au même problème dans notre demande et j'ai trouvé une solution qui est très similaire à L'approche de Grant Johnson. Un tuyau FIFO ou LIFO n'était pas une option parce que nous avons un groupe de serveurs d'application qui accèdent à une base de données. Ce que nous faisons est un

SELECT ... WHERE FLAG=0 ... FOR UPDATE

immédiatement suivi d'un

UPDATE ... SET FLAG=1 WHERE ID=:id

dès que possible afin de garder le temps de verrouillage aussi bas que possible. En fonction du nombre de colonnes de la table et des tailles, il peut être utile de ne récupérer L'ID que dans la première sélection. et une fois que vous avez marqué la ligne à récupérer les données restantes. Une procédure stockée peut réduire la quantité d'aller-retours encore plus.

^ ^ ça marche. envisagez d'avoir un statut" immédiat "de"verrouillé".

disons que votre table est comme ça:

id / name | Name / status

et les statuts possibles par exemple sont: 1 = en attente, 2= verrouillé, 3 = traité, 4 = échec, 5 =rejeté

chaque nouvel enregistrement est inséré avec le statut en attente (1)

votre programme fait: "update mytable set status = 2 où id = (sélectionner id from mytable where nom like '%Jean - % "et le statut = 1 limite 1) retourner l'id, le nom, le prénom"

alors votre programme fait son truc et s'il s'enflamme avec la conclusion que ce fil ne devrait pas avoir traité cette rangée du tout, il fait: "update mytable set status = 1 où id=?"

D'autre part, il met à jour les autres statuts.

ma solution est d'utiliser le rapport de mise à jour avec la clause de retour.

Users

-----------------------------------
ID        | Name       |      flags
-----------------------------------
1         |  bob       |        0  
2         |  fred      |        1  
3         |  tom       |        0   
4         |  ed        |        0   

au lieu de SELECT .. FOR UPDATE utiliser

BEGIN; 

UPDATE "Users"
SET ...
WHERE ...;
RETURNING ( column list );

COMMIT;

parce que la déclaration de mise à jour obtient une ligne de verrouillage exclusif sur la table sa mise à jour vous obtenez des mises à jour sérialisées. Les lectures sont toujours autorisées, mais elles ne voient les données qu'avant le début de la transaction de mise à jour.

référence: contrôle de la concurrence chapitre de Pg docs.

utilisé en multi-thread et cluster?

Comment à ce sujet?

START TRANSACTION;

// All thread retrive same task list
// If result count is very big, using cursor 
//    or callback interface provied by ORM frameworks.
var ids = SELECT id FROM tableName WHERE k1=v1;

// Each thread get an unlocked recored to process.
for ( id in ids ) {
   var rec = SELECT ... FROM tableName WHERE id =#id# FOR UPDATE NOWAIT;
   if ( rec != null ) {
    ... // do something
   }
}

COMMIT;