SELECT или INSERT в функции склонны к условиям гонки?

Question

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.

33

sql postgresql concurrency plpgsql upsert

Источник

11 апр '13 в 02:51

3 ответа

Решение

Есть еще что-то, на что стоит обратить внимание даже при использовании 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 во второй сессии, что было бы невозможно в однопоточном мире!

Если мы повысим уровень изоляции транзакции до 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 и повторить попытку в такой ситуации?

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

5

Источник

user68694 20 ноя '17 в 22:16

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

-1

Источник

user568635 11 апр '13 в 04:53

Другие вопросы по тегам sql postgresql concurrency plpgsql upsert

user939860 11 апр '13 в 13:39 2013-04-11 13:39 · Accepted Answer · 2013-04-11 13:39

Это повторяющаяся проблема 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;

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

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 несколько строк (набор) одновременно:

Как использовать RETURNING с ON CONFLICT в PostgreSQL?

Что не так с этим чистым решением 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 также появляется пустой, потому что она основана на том же снимке, где новый тег из (пока не принятой) параллельной транзакции не виден.

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

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

Оригинальный ответ (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 предложение: как только найдено достаточное количество строк, остальные не выполняются:

Способ попробовать несколько SELECT, пока не будет доступен результат?

Опираясь на это, мы можем 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 и выполняется только до тех пор, пока не будет найден первый ряд.