sql Est-ce que SELECT ou INSERT est une fonction sujette aux conditions de course?



sql having multiple conditions (3)

J'ai écrit une fonction pour créer des messages pour un moteur de blog simple:

CREATE FUNCTION CreatePost(VARCHAR, TEXT, VARCHAR[])
RETURNS INTEGER AS $$
    DECLARE
        InsertedPostId INTEGER;
        TagName VARCHAR;
    BEGIN
        INSERT INTO Posts (Title, Body)
        VALUES ($1, $2)
        RETURNING Id INTO InsertedPostId;

        FOREACH TagName IN ARRAY $3 LOOP
            DECLARE
                InsertedTagId INTEGER;
            BEGIN
                -- I am concerned about this part.
                BEGIN
                    INSERT INTO Tags (Name)
                    VALUES (TagName)
                    RETURNING Id INTO InsertedTagId;
                EXCEPTION WHEN UNIQUE_VIOLATION THEN
                    SELECT INTO InsertedTagId Id
                    FROM Tags
                    WHERE Name = TagName
                    FETCH FIRST ROW ONLY;
                END;

                INSERT INTO Taggings (PostId, TagId)
                VALUES (InsertedPostId, InsertedTagId);
            END;
        END LOOP;

        RETURN InsertedPostId;
    END;
$$ LANGUAGE 'plpgsql';

Est-ce sujet aux conditions de concurrence lorsque plusieurs utilisateurs suppriment des balises et créent des publications en même temps?
Spécifiquement, les transactions (et donc les fonctions) empêchent-elles de telles conditions de course?
J'utilise PostgreSQL 9.2.3.


Answer #1

C'est le problème récurrent de SELECT ou INSERT sous un éventuel chargement en écriture simultané, lié à (mais différent de) UPSERT (qui est INSERT ou UPDATE ).

Pour Postgres 9.5 ou version ultérieure

En utilisant la nouvelle implémentation UPSERT INSERT ... ON CONFLICT .. DO UPDATE , nous pouvons largement simplifier. Fonction PL / pgSQL pour INSERT ou SELECT une seule ligne (tag):

CREATE OR REPLACE FUNCTION f_tag_id(_tag text, OUT _tag_id int) AS
$func$
BEGIN
   SELECT tag_id  -- only if row existed before
   FROM   tag
   WHERE  tag = _tag
   INTO   _tag_id;

   IF NOT FOUND THEN
      INSERT INTO tag AS t (tag)
      VALUES (_tag)
      ON     CONFLICT (tag) DO NOTHING
      RETURNING t.tag_id
      INTO   _tag_id;
   END IF;
END
$func$ LANGUAGE plpgsql;

Il y a encore une petite fenêtre pour une condition de course. Pour être absolument sûr que vous obtenez un identifiant:

CREATE OR REPLACE FUNCTION f_tag_id(_tag text, OUT _tag_id int) AS
$func$
BEGIN
LOOP
   SELECT tag_id
   FROM   tag
   WHERE  tag = _tag
   INTO   _tag_id;

   EXIT WHEN FOUND;

   INSERT INTO tag AS t (tag)
   VALUES (_tag)
   ON     CONFLICT (tag) DO NOTHING
   RETURNING t.tag_id
   INTO   _tag_id;

   EXIT WHEN FOUND;
END LOOP;
END
$func$ LANGUAGE plpgsql;

Cela continue à boucler jusqu'à ce que INSERT ou SELECT réussisse. Appel:

SELECT f_tag_id('possibly_new_tag');

Si les commandes suivantes de la même transaction reposent sur l'existence de la ligne et qu'il est possible que d'autres transactions soient mises à jour ou supprimées simultanément, vous pouvez verrouiller une ligne existante dans l' SELECT avec FOR SHARE .
Si la ligne est insérée à la place, elle est néanmoins verrouillée jusqu'à la fin de la transaction.

Si une nouvelle ligne est insérée la plupart du temps, commencez par INSERT pour le rendre plus rapide.

En relation:

Solution associée (pure SQL) à INSERT ou SELECT plusieurs lignes (un ensemble) à la fois:

Qu'est-ce qui ne va pas avec cette solution SQL pure?

J'avais déjà suggéré cette fonction SQL:

CREATE OR REPLACE FUNCTION f_tag_id(_tag text, OUT _tag_id int) AS
$func$
   WITH ins AS (
      INSERT INTO tag AS t (tag)
      VALUES (_tag)
      ON     CONFLICT (tag) DO NOTHING
      RETURNING t.tag_id
      )
   SELECT tag_id FROM ins
   UNION  ALL
   SELECT tag_id FROM tag WHERE tag = _tag
   LIMIT  1
$func$ LANGUAGE sql;

Ce qui n’est pas totalement faux, mais il ne parvient pas à sceller une faille, comme @FunctorSalad dans sa réponse supplémentaire . La fonction peut générer un résultat vide si une transaction simultanée tente de faire la même chose en même temps. Toutes les instructions d'une requête avec des CTE sont exécutées virtuellement en même temps. Le manuel:

Toutes les instructions sont exécutées avec le même instantané

Si une transaction simultanée insère la même nouvelle balise un moment plus tôt, mais ne l'a pas encore fait:

  • La partie UPSERT est vide après avoir attendu la fin de la transaction simultanée. (Si la transaction simultanée doit être annulée, elle insère toujours la nouvelle balise et renvoie un nouvel ID.)

  • La partie SELECT apparaît également vide, car elle est basée sur le même instantané, où la nouvelle balise de la transaction simultanée (non encore validée) n'est pas visible.

Nous n'obtenons rien . Pas comme prévu. C'est contre-intuitif à la logique naïve (et je me suis fait prendre là), mais c'est comme ça que fonctionne le modèle MVCC de Postgres.

Donc, ne l'utilisez pas si plusieurs transactions peuvent essayer d'insérer la même étiquette en même temps. Ou boucle jusqu'à ce que vous obteniez réellement une ligne. La boucle ne sera presque jamais déclenchée dans les charges de travail courantes.

Réponse originale (Postgres 9.4 ou plus ancien)

Compte tenu de ce tableau (légèrement simplifié):

CREATE table tag (
  tag_id serial PRIMARY KEY
, tag    text   UNIQUE
);

... une fonction pratiquement sécurisée pour insérer une nouvelle balise / sélectionner une balise existante pourrait ressembler à ceci.
Pourquoi pas 100%? Considérez les notes dans le manuel pour l'exemple UPSERT :

CREATE OR REPLACE FUNCTION f_tag_id(_tag text, OUT tag_id int) AS
$func$
BEGIN

LOOP
   BEGIN

   WITH sel AS (SELECT t.tag_id FROM tag t WHERE t.tag = _tag FOR SHARE)
      , ins AS (INSERT INTO tag(tag)
                SELECT _tag
                WHERE  NOT EXISTS (SELECT 1 FROM sel)  -- only if not found
                RETURNING tag.tag_id)  -- qualified so no conflict with param
   SELECT sel.tag_id FROM sel
   UNION  ALL
   SELECT ins.tag_id FROM ins
   INTO   tag_id;

   EXCEPTION WHEN UNIQUE_VIOLATION THEN     -- insert in concurrent session?
      RAISE NOTICE 'It actually happened!'; -- hardly ever happens
   END;

   EXIT WHEN tag_id IS NOT NULL;            -- else keep looping
END LOOP;

END
$func$ LANGUAGE plpgsql;

Violon SQL

Explication

  • Essayez d'abord le SELECT . De cette façon, vous évitez les exceptions considérablement plus coûteuses en gérant 99,99% du temps.

  • Utilisez un CTE pour minimiser le créneau horaire (déjà minuscule) pour la condition de course.

  • La fenêtre de temps entre SELECT et INSERT dans une requête est super minuscule. Si vous n'avez pas beaucoup de charge concurrente ou si vous pouvez vivre avec une exception une fois par an, vous pouvez simplement ignorer la casse et utiliser l'instruction SQL, qui est plus rapide.

  • Pas besoin de FETCH FIRST ROW ONLY (= LIMIT 1 ). Le nom de l'étiquette est évidemment UNIQUE .

  • Supprimez FOR SHARE dans mon exemple si vous n'avez généralement pas simultanément DELETE ou UPDATE sur la tag table. Coûte un tout petit peu de performance.

  • Ne jamais citer le nom de la langue: 'plpgsql' . plpgsql est un identifiant . La citation peut causer des problèmes et n'est tolérée que pour la rétrocompatibilité.

  • N'utilisez pas de noms de colonne non descriptifs comme id ou name . Lorsque vous rejoignez deux tables ( ce que vous faites dans une base de données relationnelle), vous vous retrouvez avec plusieurs noms identiques et devez utiliser des alias.

Intégré dans votre fonction

En utilisant cette fonction, vous pouvez grandement simplifier votre FOREACH LOOP pour:

...
FOREACH TagName IN ARRAY $3
LOOP
   INSERT INTO taggings (PostId, TagId)
   VALUES   (InsertedPostId, f_tag_id(TagName));
END LOOP;
...

Plus rapide, cependant, en une seule instruction SQL avec unnest() :

INSERT INTO taggings (PostId, TagId)
SELECT InsertedPostId, f_tag_id(tag)
FROM   unnest($3) tag;

Remplace la boucle entière.

Solution alternative

Cette variante s'appuie sur le comportement de UNION ALL avec une clause LIMIT : dès que suffisamment de lignes sont trouvées, le reste n'est jamais exécuté:

Sur cette base, nous pouvons externaliser l' INSERT dans une fonction distincte. Seulement là nous avons besoin de la gestion des exceptions. Tout aussi sûr que la première solution.

CREATE OR REPLACE FUNCTION f_insert_tag(_tag text, OUT tag_id int)
  RETURNS int AS
$func$
BEGIN
INSERT INTO tag(tag) VALUES (_tag) RETURNING tag.tag_id INTO tag_id;

EXCEPTION WHEN UNIQUE_VIOLATION THEN  -- catch exception, NULL is returned
END
$func$ LANGUAGE plpgsql;

Qui est utilisé dans la fonction principale:

CREATE OR REPLACE FUNCTION f_tag_id(_tag text, OUT _tag_id int) AS
$func$
BEGIN
   LOOP
      SELECT tag_id FROM tag WHERE tag = _tag
      UNION  ALL
      SELECT f_insert_tag(_tag)  -- only executed if tag not found
      LIMIT  1  -- not strictly necessary, just to be clear
      INTO   _tag_id;

      EXIT WHEN _tag_id IS NOT NULL;  -- else keep looping
   END LOOP;
END
$func$ LANGUAGE plpgsql;
  • C'est un peu moins cher si la plupart des appels ne nécessitent que SELECT , car le bloc plus coûteux avec INSERT contenant la clause EXCEPTION est rarement entré. La requête est également plus simple.

  • FOR SHARE n'est pas possible ici (non autorisé dans la requête UNION ).

  • LIMIT 1 ne serait pas nécessaire (testé en pg 9.4). Postgres dérive LIMIT 1 de INTO _tag_id et s'exécute uniquement jusqu'à ce que la première ligne soit trouvée.


Answer #2

Je pense qu'il y a une petite chance que le tag existe déjà, il pourrait être supprimé par une autre transaction une fois que votre transaction l'a trouvé. L'utilisation d'un SELECT FOR UPDATE devrait résoudre ce problème.


Answer #3

Il y a encore quelque chose à surveiller, même en utilisant la clause ON CONFLICT introduite dans Postgres 9.5. En utilisant la même fonction et le même tableau que dans @Erwin Brandstetter, si nous faisons:

Session 1: begin;

Session 2: begin;

Session 1: select f_tag_id('a');
 f_tag_id 
----------
       11
(1 row)

Session 2: select f_tag_id('a');
[Session 2 blocks]

Session 1: commit;

[Session 2 returns:]
 f_tag_id 
----------
        NULL
(1 row)

Donc f_tag_id retourné NULL dans la session 2, ce qui serait impossible dans un monde à thread unique!

Si nous élevons le niveau d'isolation de transaction en repeatable read (ou serializable plus fort), la session 2 renvoie ERROR: could not serialize access due to concurrent update . Donc, aucun résultat "impossible" au moins, mais malheureusement, nous devons maintenant être prêts à réessayer la transaction.

Edit: avec une repeatable read ou une serializable repeatable read , si la session 1 insère la balise a , alors la session 2 insère b , la session 1 essaie d'insérer b et la session 2 essaie d'insérer a , une session détecte un blocage:

ERROR:  deadlock detected
DETAIL:  Process 14377 waits for ShareLock on transaction 1795501; blocked by process 14363.
Process 14363 waits for ShareLock on transaction 1795503; blocked by process 14377.
HINT:  See server log for query details.
CONTEXT:  while inserting index tuple (0,3) in relation "tag"
SQL function "f_tag_id" statement 1

Après la session qui a reçu l'erreur de blocage est annulée, l'autre session continue. Donc, je suppose que nous devrions traiter les interblocages comme serialization_failure et réessayer, dans une situation comme celle-ci?

Alternativement, insérez les balises dans un ordre cohérent, mais ce n'est pas facile si elles ne sont pas toutes ajoutées au même endroit.





upsert