Postgres, порядок обновления и блокировки

Да. Я думаю, что основная проблема здесь заключается в том, что IN проверяет принадлежность к указанному набору, но не присваивает никакого порядка UPDATE, что, в свою очередь, означает, что никакой конкретный порядок не присваивается порядку блокировки.

Предложение WHERE в операторе UPDATE по существу ведет себя так же, как и в SELECT. Например, я часто имитирую UPDATE, используя SELECT, чтобы проверить, что будет обновлено, чтобы убедиться, что это то, что я ожидал.

Имея это в виду, следующий пример с использованием SELECT демонстрирует, что IN сам по себе не определяет порядок:

Учитывая эту схему/данные:

create table foo
(
  id serial,
  val text
);

insert into foo (val)
values ('one'), ('two'), ('three'), ('four');

Следующие запросы:

select *
from foo
where id in (1,2,3,4);


select *
from foo
where id in (4,3,2,1);

дают точно такие же результаты - строки в порядке от id 1-4.

Даже это не гарантировано, так как я не использовал ORDER BY при выборе. Скорее, без него Postgres использует тот порядок, который сервер считает самым быстрым (см. пункт 8 о ORDER BY в Postgres SELECT doc). Если таблица довольно статична, то часто это тот же порядок, в котором она была вставлена ​​(как в данном случае). Однако ничто не гарантирует этого, и если в таблице много оттока (много мертвых кортежей, удаленных строк и т. д.), это менее вероятно.

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

sqlfiddle с приведенным выше кодом.

Возможные исправления/обходные пути:

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

Другой вариант, который по-прежнему допускает параллелизм, — это явное перебор элементов с использованием динамического SQL, скажем, в Python. Таким образом, у вас будет набор однострочных обновлений, которые всегда происходят в одном и том же порядке, и, поскольку вы можете обеспечить постоянный порядок, обычная блокировка Postgres должна иметь возможность обрабатывать параллелизм без тупик.

Это не будет работать так же хорошо, как пакетное обновление в чистом SQL, но должно решить проблему с блокировкой. Одно из предложений по повышению производительности состоит в том, чтобы только COMMIT время от времени, а не после каждой отдельной строки - это экономит много накладных расходов.

Другой вариант — выполнить цикл в функции Postgres, написанной на PL/pgSQL. Затем эту функцию можно было бы вызвать извне, скажем, в Python, но зацикливание будет выполняться (также явно) на стороне сервера, что может сэкономить некоторые накладные расходы, поскольку зацикливание и UPDATEs выполняются полностью на стороне сервера без необходимости переходить по проводам на каждой итерации цикла.

В команде UPDATE нет ORDER BY.
Но есть для SELECT. Используйте блокировку на уровне строк< /a> с FOR UPDATE предложение в подзапросе:

UPDATE foo f
SET    a = 1
FROM (
   SELECT b FROM foo
   WHERE  b IN (1,2,3,4)
   ORDER BY b
   FOR   UPDATE
   ) upd
WHERE f.b = upd.b;

Конечно, b должно быть UNIQUE, или вам нужно добавить больше выражений в предложение ORDER BY, чтобы сделать его недвусмысленным.

И вам нужно применить один и тот же порядок для всех операторов UPDATE, DELETE и SELECT .. FOR UPDATE в таблице.

Связано, с более подробной информацией:

• ОБНОВЛЕНИЕ Postgres… ОГРАНИЧЕНИЕ 1
• Избежание взаимоблокировок PostgreSQL при выполнении массового обновления и удалить операции
• Оптимизация одновременных обновлений в Postgres