Atomique de mise à JOUR .. SÉLECTIONNEZ dans Postgres

Je suis en train de construire un mécanisme de files d'attente de toutes sortes. Il y a des lignes de données qui ont besoin de traitement, et un indicateur d'état. Je suis à l'aide d'un update .. returning clause de le gérer:

UPDATE stuff
SET computed = 'working'
WHERE id = (SELECT id from STUFF WHERE computed IS NULL LIMIT 1)
RETURNING *

Est imbriquée sélectionner une partie de la même verrouillage de la mise à jour, ou puis-je avoir une condition de course ici? Si oui, ne l'intérieure sélectionnez besoin d'être un select for update?

  • Si vous essayez de construire un message de la file d'attente en sql, et qu'il est raisonnable de grand volume de tâches, je suis en supposant que vous êtes finalement va vouloir supprimer les lignes les travaux terminés. Ces tuyau de vos indices, alors assurez-vous de passer l'aspirateur analyser chaque fois que vous nettoyez les tâches terminées ou votre performance d'analyse. Vous pourriez envisager de l'aide d'un message de la file d'attente (rabbitmq, zeromq, activemq, etc).
InformationsquelleAutor kolosy | 2012-07-18
  1. 34

    Tout Erwin suggestion est peut-être le plus simple façon d'obtenir le comportement correct (tant que vous recommencez l'opération si vous obtenez une exception avec SQLSTATE de 40001), de file d'attente des demandes de par leur nature, ont tendance à mieux travailler avec les demandes de blocage pour une chance de prendre leur tour dans la file d'attente qu'avec PostgreSQL mise en œuvre de SERIALIZABLE transactions, ce qui permet d'accroître la concurrence et est un peu plus "optimiste" sur les chances de collision.

    L'exemple de la requête dans la question, telle qu'elle est, par défaut dans le READ COMMITTED niveau d'isolation de transaction permettrait à deux (ou plus) de connexions simultanées à la fois "réclamation" de la même ligne à partir de la file d'attente. Ce qui va arriver, c'est ceci:

    • T1 commence et obtient d'aussi loin que le verrouillage de la ligne dans la UPDATE phase.
    • T2 chevauche T1 en temps d'exécution et les tentatives de mise à jour de la ligne. Il bloque en attendant la COMMIT ou ROLLBACK de T1.
    • T1 s'engage, après le succès de "la demande" de la ligne.
    • T2 tente de mettre à jour la ligne, trouve que T1 a déjà, semble pour la nouvelle version de la ligne, constate qu'il répond toujours aux critères de sélection (qui est juste que id matches), et aussi des "revendications" de la ligne.

    Il peut être modifié pour fonctionner correctement (si vous utilisez une version de PostgreSQL, ce qui permet à l' FOR UPDATE clause dans une sous-requête). Juste ajouter FOR UPDATE à la fin de la sous-requête qui sélectionne l'id, et ce qui va se passer:

    • T1 commence et maintenant les verrous de la ligne avant sélection l'id.
    • T2 chevauche T1 en temps d'exécution et de blocs tout en essayant de sélectionner un id, dans l'attente de la COMMIT ou ROLLBACK de T1.
    • T1 s'engage, après le succès de "la demande" de la ligne.
    • Par le temps T2 est en mesure de lire la ligne pour voir le code, il voit qu'il a été revendiquée, de sorte qu'il trouve le prochain id.

    À la REPEATABLE READ ou SERIALIZABLE niveau d'isolation de transaction, le conflit d'écriture jeter une erreur, que vous pouvez les attraper et de les déterminer est une erreur de sérialisation basé sur le SQLSTATE, et réessayer.

    Si vous voulez des transactions SÉRIALISABLES, mais vous voulez éviter tentatives dans les files d'attente de la zone, vous pourriez être en mesure d'accomplir que par l'aide d'un consultatif de verrouillage.

    • Serait-ajout de la redondante clause where AND computed IS NULL à l'extérieur de mise à JOUR de faire cette requête particulière de se comporter correctement? Ou serait-T2 juste de venir à vide, après T1 a "annulé" la ligne sélectionnée à mi-chemin dans l'opération?
    • Dans READ COMMITTED il permettrait d'éviter la double affectation, mais dans le cas de chevauchement des opérations retourne un jeu de résultats vide, même si il y avait d'autres lignes disponibles. Au plus strict des niveaux d'isolement, il ne serait pas faire une différence, mais peut-être bien de toute façon.
    • Merci encore. J'ai mis au point une solution alternative basée sur cela.
    • Notez que les applications utilisant cette stratégie doit avoir un moyen de découvrir quand quelqu'un a demandé une rangée, puis s'est écrasé ou autrement pas réussi à faire le travail.
    • fait un bon point. Où j'ai eu besoin de faire quelque chose d'un peu semblable à cela, j'ai inclus des colonnes pour un identificateur de qui, a affirmé la ligne et si. Cela permet le nettoyage de l'abandon de créances et l'analyse de la cause.
    • Pouvez vous s'il vous plaît faire votre réponse claire et écrire la requête qui en résulte? Vous dites, "il suffit d'ajouter que pour la sous-requête qui sélectionne l'id", et je n'ai aucune idée de ce que "cela". Le Code est plus facile à comprendre que de parler. Merci!
    • J'ai reformulé, plutôt que de fournir un exemple qui a été presque exactement comme l'original -- il me semblait que les gens de perdre trop de temps à aller d'avant en arrière pour voir quelles étaient les différences. Subjectif, je sais, mais cela semblait plus propre à moi.
    • la déclaration doit être enveloppé dans un COMMENCER...s'engagent à tenir le POUR verrou de mise à JOUR? il y a certains rapports que cela ne fonctionne pas sinon: github.com/collectiveidea/delayed_job_active_record/pull/79
    • la plupart des écluses sont libérés à la fin de la transaction, qui s'acquiert (à l'exception de certains consultatif des verrous). Sans BEGIN/COMMIT bloc (ou l'équivalent de l'aide d'autres syntaxe ou une fonction englobante), chaque instruction crée sa propre transaction qui est automatiquement validée ou annulée à la clôture de l'instruction.
    • quand vous dites, "À la REPEATABLE READ ou SERIALIZABLE niveau d'isolation de transaction, le conflit d'écriture jeter une erreur", tu veux dire qu'il y aurait un conflit d'écriture moins FOR UPDATE est utilisé, non?
    • Mise à JOUR n'empêche pas l'erreur de sérialisation, mais si vous utilisez du Erwin exemple (9,5 et plus tard) qui utilise FOR UPDATE SKIP LOCKED avec le REPEATABLE READ niveau d'isolation, vous obtenez exactement ce que vous voulez sans l'erreur de sérialisation. Il y a quelques idées flottant autour pour obtenir des performances similaires pour SERIALIZABLE des transactions si la mise à jour n'a pas ORDER BY clause et comprend un LIMIT, mais c'est juste de parler à ce point.

    InformationsquelleAutor kgrittn
  2. 19

    Si vous êtes le seul utilisateur, la requête doit être fine. En particulier, il n'y a pas de condition de course ou de blocage au sein de la requête elle-même (entre la requête externe et la sous-requête). Je cite le manuel ici:

    Cependant, une opération jamais en conflit avec lui-même.

    Pour l'utilisation simultanée, la question peut être plus compliqué. Vous être sur le côté sûr, avec SERIALIZABLE mode de transaction:

    BEGIN ISOLATION LEVEL SERIALIZABLE;
UPDATE stuff
SET    computed = 'working'
WHERE  id = (SELECT id FROM stuff WHERE computed IS NULL LIMIT 1)
RETURNING * 
COMMIT;

    Vous devez vous préparer pour la sérialisation des échecs et recommencez votre requête dans un tel cas.

    Mais je ne suis pas entièrement sûr si ce n'est pas exagéré. Je vais demander à @kgrittn arrêter .. il est la expert avec la concurrence d'accès et des transactions sérialisables ..

    Et il l'a fait. 🙂

    Meilleur des deux mondes

    Exécuter la requête de transaction par défaut en mode READ COMMITTED.

    Pour Postgres 9.5 ou de les utiliser plus tard FOR UPDATE SKIP LOCKED. Voir:

    Pour les anciennes versions de vérifier à nouveau la condition computed IS NULL explicitement à l'extérieur UPDATE:

    UPDATE stuff
SET    computed = 'working'
WHERE  id = (SELECT id FROM stuff WHERE computed IS NULL LIMIT 1)
AND   computed IS NULL;

    Comme @kgrittn est conseillé dans le commentaire de sa réponse, cette requête pourrait venir vides, sans avoir fait quoi que ce soit, dans le (rare) cas où il a obtenu liée à une transaction simultanée.

    Donc, ce serait un peu comme la première variante du mode de transaction SERIALIZABLE, vous devez réessayer - juste sans perte de performance.

    Le seul problème: Alors que le conflit est très peu probable parce que la fenêtre d'opportunité est tellement minuscule, il peut se produire sous une lourde charge. Vous ne pourriez pas dire à coup sûr si il y a finalement pas plus de lignes à gauche.

    Si ce n'est pas grave (comme dans votre cas), vous avez fait ici.

    Si elle le fait, pour être absolument sûr, démarrez une requête avec explicite de verrouillage après que vous obtenez un résultat vide. Si cela vient à vide, vous avez terminé. Si non, continuer.

    Dans plpgsql il pourrait ressembler à ceci:

    LOOP
   UPDATE stuff
   SET    computed = 'working'
   WHERE  id = (SELECT id FROM stuff WHERE computed IS NULL
                LIMIT 1 FOR UPDATE SKIP LOCKED);  -- pg 9.5+
   -- WHERE  id = (SELECT id FROM stuff WHERE computed IS NULL LIMIT 1)
   -- AND    computed IS NULL; -- pg 9.4-

   CONTINUE WHEN FOUND;  -- continue outside loop, may be a nested loop

   UPDATE stuff
   SET    computed = 'working'
   WHERE  id = (SELECT id FROM stuff WHERE computed IS NULL
                LIMIT 1 FOR UPDATE);

   EXIT WHEN NOT FOUND;  -- exit function (end)
END LOOP;

    Qui devrait vous donner le meilleur des deux mondes: la performance et fiabilité.

    • SERIALIZABLE transactions en effet provoquer des correct comportement, mais les commentaires sur la nouvelle mise en œuvre, de sa mise en œuvre dans PostgreSQL 9.1 (et plus tard) suggère que les files d'attente des demandes sont le "pire scénario". Alors que la technique (appel Serializable Isolement d'Instantané ou SSI) est beaucoup plus rapide que d'utiliser des verrous de blocage pour la plupart des charges de travail, j'ai un rapport de 20% de performances pour un particulier de files d'attente de l'application, et le journaliste (l'un des principaux PostgreSQL contributeur) a pu intentionnellement ingénieur pire. Donc, vous pouvez l'essayer, mais vous pourriez trouver explicite de verrouillage fonctionne mieux.
    • J'hésite à faire une déclaration au sujet de la performance sans réellement l'analyse comparative, mais je m'attends à ce que marginalement mieux de la performance (probablement assez petit pour être difficile à mesurer) par juste de commencer avec la UPDATE dans le deuxième bloc. Il y a des optimisations pour une connexion re-verrouillage d'une ligne qu'il a déjà verrouillé, et je ne pense pas que ce bloc, sauf si vous êtes dans une situation où vous auriez besoin de le recommencer de toute façon.
    • dans mon cas précis, ce n'est pas nécessaire. lorsque le processus arrive à la fin de la file d'attente (dans la mesure où il est concerné), il dort pendant 10 minutes et essaie de nouveau. si un enregistrement tombe dans la fenêtre suivante, ce n'est pas un problème. merci pour la compilation de bien.
    • La version simple, avec la répétition des AND computed IS NULL dans la requête externe devrait être la solution optimale pour un tel cas.
    InformationsquelleAutor Erwin Brandstetter