АТОМНОЕ ОБНОВЛЕНИЕ.. ВЫБРАТЬ в Postgres

Я создаю механизм очередей. Есть строки данных, которые нуждаются в обработке, и флаг состояния. Я использую update .. returning пункт, чтобы управлять им:

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

Является ли вложенная выбранная часть той же блокировкой, что и обновление, или у меня есть условие гонки? Если это так, должен ли внутренний выбор быть select for update?

Источник

2 ответа

Решение

Хотя предложение Эрвина, возможно, является самым простым способом получения правильного поведения (при условии, что вы повторите попытку транзакции, если получите исключение с SQLSTATE 40001), приложения по очереди по своей природе, как правило, лучше работают с блокировкой запросов, чтобы получить возможность занять свою очередь в очереди, чем с реализацией PostgreSQL SERIALIZABLE транзакции, которая допускает более высокий уровень параллелизма и несколько более "оптимистично" относится к вероятности столкновения.

Пример запроса в вопросе, в том виде, в каком он есть, по умолчанию READ COMMITTED Уровень изоляции транзакции позволил бы двум (или более) одновременным соединениям одновременно "требовать" одну и ту же строку из очереди. Что произойдет, это:

  • T1 запускается и доходит до блокировки строки в UPDATE фаза.
  • T2 перекрывает T1 во время выполнения и пытается обновить эту строку. Это блокирует в ожидании COMMIT или же ROLLBACK Т1.
  • T1 фиксирует, успешно "заявив" строку.
  • T2 пытается обновить строку, находит, что T1 уже имеет, ищет новую версию строки, находит, что она все еще удовлетворяет критериям выбора (что только id совпадений), а также "претензий" на ряд.

Его можно изменить, чтобы он работал правильно (если вы используете версию PostgreSQL, которая позволяет FOR UPDATE пункт в подзапросе). Просто добавь FOR UPDATE до конца подзапроса, который выбирает идентификатор, и это произойдет:

  • T1 запускается и теперь блокирует строку перед выбором идентификатора.
  • T2 перекрывает T1 во время выполнения и блокирует при попытке выбрать идентификатор в ожидании COMMIT или же ROLLBACK Т1.
  • T1 фиксирует, успешно "заявив" строку.
  • К тому времени, когда T2 может прочитать строку, чтобы увидеть идентификатор, он видит, что он был востребован, поэтому он находит следующий доступный идентификатор.

На REPEATABLE READ или же SERIALIZABLE На уровне изоляции транзакции конфликт записи вызовет ошибку, которую вы можете перехватить и определить, была ли ошибка сериализации на основе SQLSTATE, и повторите попытку.

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

Источник

Если вы единственный пользователь, запрос должен быть в порядке. В частности, в самом запросе нет условия гонки или тупика (между внешним запросом и подзапросом). Я цитирую руководство здесь:

Однако транзакция никогда не конфликтует сама с собой.

Для одновременного использования, вопрос может быть более сложным. Вы были бы в безопасности с SERIALIZABLE режим транзакции:

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

Вам нужно подготовиться к ошибкам сериализации и повторить запрос в этом случае.

Но я не совсем уверен, что это не перебор. Я попрошу @kgrittn остановиться.. он эксперт по параллелизму и сериализуемым транзакциям..

И он сделал. :)

Лучшее из обоих миров

Запустите запрос в режиме транзакции по умолчанию READ COMMITTED,

Для Postgres 9.5 или новее FOR UPDATE SKIP LOCKED, Увидеть:

Для более старых версий перепроверьте условие computed IS NULL явно во внешнем UPDATE:

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

Как советовал @ kgrittn в комментарии к своему ответу, этот запрос может оказаться пустым, ничего не сделав, в (маловероятном) случае, когда он переплетается с параллельной транзакцией.

Таким образом, он будет работать так же, как первый вариант в режиме транзакции SERIALIZABLE, вам придется повторить попытку - просто без потери производительности.

Единственная проблема: хотя конфликт очень маловероятен, потому что окно возможностей просто крошечно, оно может возникнуть при большой нагрузке. Вы не могли бы точно сказать, не осталось ли, наконец, больше строк.

Если это не имеет значения (как в вашем случае), вы сделали здесь.
Если это произойдет, чтобы быть абсолютно уверенным, запустите еще один запрос с явной блокировкой после получения пустого результата. Если это произойдет пустым, вы сделали. Если нет, продолжайте.
В plpgsql это может выглядеть так:

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;

Это должно дать вам лучшее из обоих миров: производительность и надежность.

Источник
Другие вопросы по тегам