Однажды при выполнении достаточно тривиального запроса...

UPDATE tbl SET val = val + 1 WHERE id IN (1, 2, 3);

... вы получаете ошибку:

ERROR:  deadlock detected
DETAIL:  Process 19260 waits for ShareLock on transaction 550; blocked by process 3112.
Process 3112 waits for ShareLock on transaction 551; blocked by process 19260.
HINT:  See server log for query details.

Но почему? Ведь еще вчера все успешно работало!

И что с этим теперь делать? Давайте разбираться на простых примерах.

RTFM

Сначала обратимся к документации:

Например, если транзакция 1 получает исключительную блокировку таблицы A, а затем пытается получить исключительную блокировку таблицы B, которую до этого получила транзакция 2, в данный момент требующая исключительную блокировку таблицы A, ни одна из транзакций не сможет продолжить работу.

...

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

Но ведь в нашем примере все строки строго упорядочены в условии IN (1, 2, 3) - или нет?..

Посмотрим на план нашего запроса:

EXPLAIN ANALYZE
UPDATE tbl SET val = val + 1 WHERE id IN (1, 2, 3);

Update on tbl  (cost=12.85..19.39 rows=3 width=14) (actual time=0.080..0.080 rows=0 loops=1)
  ->  Bitmap Heap Scan on tbl  (cost=12.85..19.39 rows=3 width=14) (actual time=0.026..0.029 rows=3 loops=1)
        Recheck Cond: (id = ANY ('{1,2,3}'::integer[]))
        Heap Blocks: exact=2
        ->  Bitmap Index Scan on tbl_pkey  (cost=0.00..12.85 rows=3 width=0) (actual time=0.018..0.018 rows=3 loops=1)
              Index Cond: (id = ANY ('{1,2,3}'::integer[]))

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

Понятно, что этот порядок не имеет никакого отношения к значениям полей в самих этих записях, даже если одно из них мы назвали id.

Процедурный цикл

Самый простой способ - заставить PostgreSQL обновлять записи заведомо в нужном нам порядке:

DO $$
DECLARE
  i integer;
BEGIN
  FOR i IN (SELECT unnest('{3,1,2}'::integer[]) ORDER BY 1) LOOP
    RAISE NOTICE 'UPDATE id : %', i;
    UPDATE tbl SET val = val + 1 WHERE id = i;
  END LOOP;
END$$;

NOTICE:  UPDATE id : 1
NOTICE:  UPDATE id : 2
NOTICE:  UPDATE id : 3

Но этот вариант не особо эффективен - ведь план, подобный приведенному выше, будет отдельно исполняться для каждого идентификатора.

Упреждающая блокировка FOR UPDATE

Давайте взглянем на проблему под немного другим углом, разложив на составляющие.

Как поступает пара конкурирующих UPDATE:

txA:            txB:

SEARCH ROW #X
LOCK ROW #X
MODIFY ROW #X

                SEARCH ROW #Y
                LOCK ROW #Y
                MODIFY ROW #Y

SEARCH ROW #Y
LOCK ROW #Y -- wait!
MODIFY ROW #Y

                SEARCH ROW #X
                LOCK ROW #X -- deadlock!!!
                MODIFY ROW #X

Но ведь из этих SEARCH/LOCK/MODIFY только сама операция наложения блокировки должна быть упорядочена, чтобы избежать deadlock'а.

txA:                  txB:

SEARCH ROWS [#X, #Y]
                      SEARCH ROWS [#X, #Y]
LOCK ROW #X
                      LOCK ROW #X -- wait!
LOCK ROW #Y
MODIFY ROWS [#X, #Y]
                      LOCK ROW #Y
                      MODIFY ROWS [#X, #Y]

Можем ли мы собрать такой запрос? Оказывается, вполне - достаточно в явном виде воспользоваться блокировкой на уровне строк, которая отрабатывает уже после сортировки: SELECT ... ORDER BY ... FOR UPDATE.

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

EXPLAIN ANALYZE
UPDATE
  tbl
SET
  val = val + 1
FROM
  (
    SELECT
      ctid
    FROM
      tbl
    WHERE
      id IN (1, 2, 3)
    ORDER BY
      id
    FOR UPDATE -- блокировка
  ) lc
WHERE
  tbl.ctid = lc.ctid; -- поиск по физической позиции записи

Update on tbl  (cost=19.40..31.54 rows=3 width=44) (actual time=0.107..0.107 rows=0 loops=1)
  ->  Nested Loop  (cost=19.40..31.54 rows=3 width=44) (actual time=0.074..0.091 rows=3 loops=1)
        ->  Subquery Scan on lc  (cost=19.40..19.47 rows=3 width=36) (actual time=0.063..0.068 rows=3 loops=1)
              ->  LockRows  (cost=19.40..19.44 rows=3 width=16) (actual time=0.060..0.063 rows=3 loops=1)
                    ->  Sort  (cost=19.40..19.41 rows=3 width=16) (actual time=0.038..0.039 rows=3 loops=1)
                          Sort Key: tbl_1.id
                          Sort Method: quicksort  Memory: 25kB
                          ->  Bitmap Heap Scan on tbl tbl_1  (cost=12.85..19.38 rows=3 width=16) (actual time=0.028..0.031 rows=3 loops=1)
                                Recheck Cond: (id = ANY ('{1,2,3}'::integer[]))
                                Heap Blocks: exact=2
                                ->  Bitmap Index Scan on tbl_pkey  (cost=0.00..12.85 rows=3 width=0) (actual time=0.017..0.017 rows=3 loops=1)
                                      Index Cond: (id = ANY ('{1,2,3}'::integer[]))
        ->  Tid Scan on tbl  (cost=0.00..4.01 rows=1 width=14) (actual time=0.003..0.004 rows=1 loops=3)
              TID Cond: (ctid = lc.ctid)
Planning Time: 0.191 ms
Execution Time: 0.166 ms

Или если воспользоваться визуализацией с помощью explain.tensor.ru, становится еще более наглядно:

• сначала Bitmap Heap Scan вернул 3 найденные записи

• они были отсортированы, и только после этого заблокированы

• затем по каждой из них был осуществлен быстрый поиск Tid Scan

• и найденное - ушло на Update

Собственно, что мы и хотели, и весьма эффективно.