Является ли SELECT или INSERT функцией, склонной к условиям гонки?

Я написал функцию для создания сообщений для простого механизма ведения блога:

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';

Является ли это склонным к условиям гонки, когда несколько пользователей удаляют теги и создают сообщения одновременно?
В частности, делают ли транзакции (и, следовательно, функции) такие условия гонки?

Я использую PostgreSQL 9.2.3.

Ответы

Ответ 1

Это повторяющаяся проблема SELECT или INSERT при возможной одновременной загрузке, связанной с (но отличной от) UPSERT (которая является INSERT или UPDATE).

Для Postgres 9.5 или новее

Используя новую реализацию UPSERT INSERT... ON CONFLICT.. DO UPDATE, мы можем в значительной степени упростить. PL/pgSQL для INSERT или SELECT одной строки (тега):

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;

Существует еще крошечное окно для состояния гонки. Чтобы убедиться, что вы получили идентификатор:

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;

Это будет продолжаться до тех пор, пока не будут выполнены INSERT или SELECT. Вызов:

SELECT f_tag_id('possibly_new_tag');

Если последующие команды в одной транзакции полагаются на существование строки, и на самом деле возможно, что другие транзакции обновляют или удаляют ее одновременно, вы можете заблокировать существующую строку в SELECT с помощью FOR SHARE.
Если вместо этого вставлена строка, она будет заблокирована до конца транзакции.

Если большую часть времени вставлена новая строка, начните с INSERT чтобы сделать ее быстрее.

Связанный:

Связанное (чистое SQL) решение для INSERT или SELECT несколько строк (набор) сразу:

Что не так с этим чистым SQL-решением?

Ранее я также предложил эту функцию 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;

Что не совсем неправильно, но он не может запечатать лазейку, как @FunctorSalad, разработанный в его добавленном ответе. Функция может возникнуть с пустым результатом, если одновременная транзакция пытается сделать то же самое в одно и то же время. Все заявления в запросе с CTE фактически выполняются одновременно. Руководство:

Все операторы выполняются с тем же снимком

Если одновременная транзакция вставляет один и тот же новый тег раньше, но еще не совершена,

  • Часть UPSERT становится пустой, ожидая завершения параллельной транзакции. (Если параллельная транзакция должна откат, она по-прежнему вставляет новый тег и возвращает новый идентификатор.)

  • Часть SELECT также пуста, потому что она основана на том же снимке, где новый тег из (пока не завершенной) параллельной транзакции не отображается.

Мы ничего не получаем. Не так, как предполагалось. Это противоречит интуитивной наивной логике (и я попался туда), но то, как работает MVCC модель Postgres - должно работать.

Поэтому не используйте это, если несколько транзакций могут попытаться вставить один и тот же тег одновременно. Или цикл, пока вы не получите строку. Цикл вряд ли когда-либо срабатывает в обычных рабочих нагрузках.

Оригинальный ответ (Postgres 9.4 и старше)

Учитывая эту (слегка упрощенную) таблицу:

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

... практически 100% безопасная функция для вставки нового тега/выбора существующего, может выглядеть так.
Почему не 100%? Рассмотрим примечания в руководстве для соответствующего примера 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;

SQL Fiddle.

объяснение

  • Сначала попробуйте SELECT. Таким образом, вы избегаете значительно более дорогостоящей обработки исключений 99,99% времени.

  • Используйте CTE для минимизации (уже крошечного) временного интервала для состояния гонки.

  • Временное окно между SELECT и INSERT в одном запросе является очень маленьким. Если у вас нет сильной параллельной нагрузки или вы можете жить с исключением один раз в год, вы можете просто проигнорировать это дело и использовать оператор SQL, который выполняется быстрее.

  • Нет необходимости только FETCH FIRST ROW ONLY (= LIMIT 1). Название тега очевидно UNIQUE.

  • Удалите FOR SHARE в моем примере, если у вас обычно нет параллельного DELETE или UPDATE в tag таблицы. Затраты крошечной производительности.

  • Никогда не цитируйте имя языка: "plpgsql". plpgsql - это идентификатор. Цитирование может вызвать проблемы и допускается только для обратной совместимости.

  • Не используйте имена без описательного столбца, такие как id или name. При объединении нескольких таблиц (что вы делаете в реляционной БД) вы получаете несколько идентичных имен и должны использовать псевдонимы.

Встроенная функция

Используя эту функцию, вы можете в значительной степени упростить свой FOREACH LOOP до:

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

Тем не менее, быстрее, как один оператор SQL с unnest():

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

Заменяет весь цикл.

Альтернативное решение

Этот вариант основывается на поведении UNION ALL с предложением LIMIT: как только будет найдено достаточно строк, остальные никогда не выполняются:

Исходя из этого, мы можем передать INSERT в отдельную функцию. Только там нужна обработка исключений. Так же безопасно, как и первое решение.

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;

Используется в основной функции:

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;

  • Это немного дешевле, если для большинства вызовов требуется только SELECT, потому что более дорогой блок с INSERT содержащий предложение EXCEPTION, редко вводится. Запрос также проще.

  • FOR SHARE здесь невозможно (не разрешено в запросе UNION).

  • LIMIT 1 не требуется (проверено в п. 9.4). Postgres выводит LIMIT 1 из INTO _tag_id и выполняется только до тех пор, пока не будет найдена первая строка.

Ответ 2

Есть еще кое-что, на что можно обратить внимание, даже при использовании предложения ON CONFLICT, представленного в Postgres 9.5. Используя ту же самую функцию и таблицу примеров, что и в ответе @Erwin Brandstetter, если мы это сделаем:

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)

Итак f_tag_id возвратил NULL в сеансе 2, что было бы невозможно в однопоточном мире!

Если уровень изоляции транзакции повысится до repeatable read (или более сильного serializable), тогда сеанс 2 выберет ERROR: could not serialize access due to concurrent update. Таким образом, "невозможно" невозможно, но, к сожалению, мы должны быть готовы повторить транзакцию.

Изменить: с помощью repeatable read или serializable, если сеанс 1 вставляет тег a, тогда сеанс 2 вставляет b, тогда сеанс 1 пытается вставить b, а сеанс 2 пытается вставить a, один сеанс обнаруживает тупик:

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

После того, как сеанс, получивший ошибку тупика, откат, второй сеанс продолжается. Так что, я думаю, мы должны относиться к тупику, как и к serialization_failure, и повторять попытку в такой ситуации?

В качестве альтернативы вставьте теги в последовательном порядке, но это непросто, если они не все добавляются в одном месте.

Ответ 3

Я думаю, что существует небольшая вероятность, что, когда тег уже существует, он может быть удален другой транзакцией после того, как ваша транзакция ее найдет. Использование SELECT FOR UPDATE должно решить эту проблему.