Postgres UPDATE с ORDER BY, как это сделать?

Насколько я знаю, это невозможно сделать напрямую с помощью оператора UPDATE; единственный способ гарантировать порядок блокировки - это явно получить блокировки с помощью SELECT ... ORDER BY ID FOR UPDATE, например:

UPDATE Balances
SET Balance = 0
WHERE ID IN (
  SELECT ID FROM Balances
  WHERE ID IN (SELECT ID FROM some_function())
  ORDER BY ID
  FOR UPDATE
)

Недостатком этого является повторение поиска индекса ID в таблице Balances. В вашем простом примере вы можете избежать этих накладных расходов, извлекая физический адрес строки (представленный ctid системный столбец) во время запроса блокировки и использовать его для управления UPDATE:

UPDATE Balances
SET Balance = 0
WHERE ctid = ANY(ARRAY(
  SELECT ctid FROM Balances
  WHERE ID IN (SELECT ID FROM some_function())
  ORDER BY ID
  FOR UPDATE
))

(Будьте осторожны при использовании ctids, так как значения являются временными. Здесь мы в безопасности, так как блокировки будут блокировать любые изменения.)

К сожалению, планировщик будет использовать ctid только в узком наборе случаев (вы можете определить, работает ли он, найдя узел «Tid Scan» в выводе EXPLAIN). Чтобы обрабатывать более сложные запросы в рамках одного оператора UPDATE, например. если ваш новый баланс был возвращен some_function() вместе с идентификатором, вам нужно будет вернуться к поиску на основе идентификатора:

UPDATE Balances
SET Balance = Locks.NewBalance
FROM (
  SELECT Balances.ID, some_function.NewBalance
  FROM Balances
  JOIN some_function() ON some_function.ID = Balances.ID
  ORDER BY Balances.ID
  FOR UPDATE
) Locks
WHERE Balances.ID = Locks.ID

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

DO $$
DECLARE
  c CURSOR FOR
    SELECT Balances.ID, some_function.NewBalance
    FROM Balances
    JOIN some_function() ON some_function.ID = Balances.ID
    ORDER BY Balances.ID
    FOR UPDATE;
BEGIN
  FOR row IN c LOOP
    UPDATE Balances
    SET Balance = row.NewBalance
    WHERE CURRENT OF c;
  END LOOP;
END
$$

В общем, параллелизм затруднен. Особенно с 200 операторами (я предполагаю, что вы не только делаете запрос = SELECT) или даже транзакциями (на самом деле каждый выпущенный оператор включается в транзакцию, если он еще не находится в транзакции).

Общие концепции решения (комбинация) следующие:

1. Чтобы знать, что взаимоблокировки могут возникать, отловите их в приложении, проверьте Коды ошибок для class 40 или 40P01 и повторите транзакцию.

2. Резервные замки. Используйте SELECT ... FOR UPDATE. Избегайте явных блокировок как можно дольше. Блокировки заставят другие транзакции ждать освобождения блокировки, что вредит параллелизму, но может предотвратить взаимоблокировку транзакций. Проверьте пример взаимоблокировок в главе 13. Особенно тот, в котором транзакция A ожидает B, а B ждет A (эта штука с банковским счетом).

3. Выберите другой Уровень изоляции, например более слабый, например READ COMMITED, если можно. Помните о LOST UPDATEs в режиме READ COMMITED. Предотвратите их с помощью REPEATABLE READ.

Пишите свои операторы с блокировками в одном и том же порядке в КАЖДОЙ транзакции, например, по имени таблицы в алфавитном порядке.

LOCK / USE A  -- Transaction 1 
LOCK / USE B  -- Transaction 1
LOCK / USE C  -- Transaction 1
-- D not used -- Transaction 1

-- A not used -- Transaction 2
LOCK / USE B  -- Transaction 2
-- C not used -- Transaction 2
LOCK / USE D  -- Transaction 2

с общим порядком блокировки A B C D. Таким образом, транзакции могут чередоваться в любом относительном порядке и по-прежнему иметь хорошие шансы не зайти в тупик (хотя в зависимости от ваших утверждений у вас могут возникнуть другие проблемы с сериализацией). Операторы транзакций будут выполняться в указанном ими порядке, но может случиться так, что транзакция 1 запустит свои первые 2, затем xact 2 запустит первую, затем 1 завершится и, наконец, завершится xact 2.

Кроме того, вы должны понимать, что оператор, включающий несколько строк, не выполняется атомарно в параллельной ситуации. Другими словами, если у вас есть два оператора A и B, включающие несколько строк, их можно выполнить в следующем порядке:

a1 b1 a2 a3 a4 b2 b3     

но НЕ как блок a, за которым следуют b. То же самое относится к оператору с подзапросом. Вы смотрели планы запросов, используя EXPLAIN ?

В вашем случае можно попробовать

UPDATE BALANCES WHERE ID IN (
 SELECT ID FROM some_function() FOR UPDATE  -- LOCK using FOR UPDATE 
 -- other transactions will WAIT / BLOCK temporarily on conc. write access
);

Если возможно, то, что вы хотите сделать, вы также можете использовать SELECT ... FOR UPDATE SKIP LOCK, который пропустит уже заблокированные данные, чтобы вернуть параллелизм, который теряется из-за ОЖИДАНИЯ другой транзакции для снятия блокировки (FOR UPDATE). Но это не применит ОБНОВЛЕНИЕ к заблокированным строкам, что может потребоваться логике вашего приложения. Так что запустите это позже (см. пункт 1).

Также прочитайте ПОТЕРЯННОЕ ОБНОВЛЕНИЕ о LOST UPDATE и ПРОПУСТИТЬ ЗАБЛОКИРОВАНО о SKIP LOCKED. Очередь может быть идеей в вашем случае, что прекрасно объяснено в справочнике SKIP LOCKED, хотя реляционные СУБД не предназначены для очередей.

ХТН