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

Это руководство предназначено разработчику, работающему в Терминале macOS и которому нужно полностью обновить две параллельные партии синтетических строк статей. В примере используются актуальная документация PostgreSQL, где указана версия PostgreSQL 18, и актуальная документация node-postgres. Рассматривается известный список существующих ID статей, которые не меняются во время параллельной работы. PostgreSQL: явная блокировка

Почему при пересекающихся обновлениях возможна взаимоблокировка

Рассмотрим две транзакции: A обновляет статью 1, затем статью 2; B обновляет статью 2, затем статью 1. Если каждая получает блокировку первой строки, прежде чем запросить блокировку второй, обе ожидают блокировку, удерживаемую другой транзакцией. PostgreSQL обнаруживает цикл и прерывает одну из транзакций; приложение не может выбрать, какую именно. Такая ситуация может возникнуть при обычных обновлениях без явных команд блокировки. PostgreSQL: явная блокировка

Важно различать пересечение и цикл. Пересечение может привести к ожиданию. Цикл не позволяет продолжить ни одной из транзакций. PostgreSQL рекомендует всем приложениям получать блокировки в одном и том же порядке, причём сначала получать блокировку с самым строгим из необходимых режимов. PostgreSQL: явная блокировка

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

Перед обновлением заблокируйте весь целевой набор

Сортировка массива JavaScript не указывает массовой команде SQL, в каком порядке обрабатывать строки. В синтаксисе PostgreSQL UPDATE нет прямого предложения ORDER BY. В документации показана блокировка в заданном порядке с помощью запроса на выборку и объясняется, что единый порядок может предотвратить взаимоблокировки между операциями обновления. PostgreSQL: UPDATE

Выполните отдельную команду блокировки и дождитесь её завершения внутри транзакции. Эта иллюстративная SQL-транзакция затрагивает статьи 1, 2 и 3; таблица создаётся в следующем разделе.

BEGIN ISOLATION LEVEL READ COMMITTED;

SELECT id
FROM deadlock_demo_articles
WHERE id = ANY(ARRAY[3, 1, 2]::integer[])
ORDER BY id
FOR UPDATE;

UPDATE deadlock_demo_articles
SET revision = revision + 1
WHERE id = ANY(ARRAY[3, 1, 2]::integer[]);

COMMIT;

Первая команда задаёт порядок получения блокировок. Затем обновление работает со строками, уже заблокированными этой транзакцией. Обычно блокировки строк сохраняются до конца транзакции. Сведите этот период к минимуму: подготовьте входные данные до начала транзакции и не ждите действий пользователя за компьютером, пока удерживаются блокировки. PostgreSQL: явная блокировка

Контракт порядка для этого примера включает следующие условия:

  • Все конкурирующие партии используют порядок возрастания id статей.
  • ID не меняются во время параллельной работы.
  • Каждая партия определяет весь целевой набор до начала блокировки.
  • Ни одна более ранняя команда уже не заблокировала строки статей в другом порядке.
  • В таблице примера нет триггеров и операций со связанными таблицами, которые создают дополнительные зависимости блокировок.

Это необходимые условия для применения описанных рассуждений к данной нагрузке. Транзакция, которая сначала блокирует статью 9, а затем запрашивает статьи с 1 по 3, уже нарушила контракт, даже если в её последнем запросе есть ORDER BY id.

Запустите полный пример node-postgres на Mac

Для работы понадобятся запущенный локальный сервер PostgreSQL, существующая тестовая база данных, Node.js с npm и доступная в Терминале команда psql. Настройте подключение к существующей базе с помощью переменных окружения PostgreSQL. Пул node-postgres и psql используют одни и те же переменные подключения; при необходимости явно задайте PGHOST, чтобы обе команды подключались к одному серверу. node-postgres: подключение

В новой рабочей папке установите зависимость:

npm init -y
npm install pg

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

BEGIN;

CREATE TABLE deadlock_demo_articles (
    id integer PRIMARY KEY,
    title text NOT NULL,
    revision integer NOT NULL DEFAULT 0
);

INSERT INTO deadlock_demo_articles (id, title)
VALUES
    (1, 'Paper Shapes'),
    (2, 'Color Notes'),
    (3, 'Desk Sketches');

COMMIT;

psql -v ON_ERROR_STOP=1 -f setup.sql

Сохраните следующую полную программу в файле update-articles.mjs. Все команды одной попытки выполняются через клиент, возвращённый вызовом pool.connect(). В документации node-postgres прямо указано, что на протяжении всей транзакции нужно использовать один клиент; выполнение команд транзакции через pool.query() этому требованию не соответствует. node-postgres: транзакции

import pg from 'pg';
import { setTimeout as sleep } from 'node:timers/promises';

const { Pool } = pg;
const pool = new Pool({ max: 2, connectionTimeoutMillis: 5000 });
pool.on('error', error => {
  console.error('Idle database client error:', error.message);
  process.exitCode = 1;
});

function normalizeIds(input) {
  if (!Array.isArray(input) || input.length === 0 ||
      input.some(id => !Number.isInteger(id) ||
        id < 1 || id > 2147483647)) {
    throw new Error('Use a nonempty array of positive PostgreSQL integer IDs');
  }
  return [...new Set(input)].sort((a, b) => a - b);
}

async function incrementRevisions(input) {
  const ids = normalizeIds(input);
  const maxAttempts = 3;

  for (let attempt = 1; attempt <= maxAttempts; attempt += 1) {
    const client = await pool.connect();
    let discard = false;

    try {
      await client.query('BEGIN ISOLATION LEVEL READ COMMITTED');

      const locked = await client.query(
        `SELECT id
         FROM deadlock_demo_articles
         WHERE id = ANY($1::integer[])
         ORDER BY id
         FOR UPDATE`,
        [ids]
      );

      if (locked.rows.length !== ids.length) {
        throw new Error('A requested article is missing; batch cancelled');
      }

      const updated = await client.query(
        `UPDATE deadlock_demo_articles
         SET revision = revision + 1
         WHERE id = ANY($1::integer[])
         RETURNING id, revision`,
        [ids]
      );

      if (updated.rowCount !== ids.length) {
        throw new Error('Unexpected update count; batch cancelled');
      }

      await client.query('COMMIT');
      return updated.rows.sort((a, b) => a.id - b.id);
    } catch (error) {
      try {
        await client.query('ROLLBACK');
      } catch (rollbackError) {
        discard = true;
        throw new Error('Rollback failed; connection discarded', {
          cause: rollbackError
        });
      }

      const retryable = error.code === '40P01' || error.code === '40001';
      if (!retryable || attempt === maxAttempts) throw error;
    } finally {
      client.release(discard);
    }

    // Illustrative bounded backoff, after releasing the client.
    await sleep(50 * 2 ** (attempt - 1) + Math.floor(Math.random() * 50));
  }
}

try {
  const outcomes = await Promise.allSettled([
    incrementRevisions([3, 1, 2]),
    incrementRevisions([3, 2])
  ]);

  for (const outcome of outcomes) {
    if (outcome.status === 'rejected') throw outcome.reason;
  }

  const result = await pool.query(
    'SELECT id, title, revision FROM deadlock_demo_articles ORDER BY id'
  );
  console.table(result.rows);
} catch (error) {
  console.error(error);
  process.exitCode = 1;
} finally {
  await pool.end();
}

node update-articles.mjs

Массив передаётся одним параметром с явным приведением типа к integer[]; в документации node-postgres описано преобразование массивов JavaScript в значения массивов PostgreSQL. Удаление дубликатов позволяет считать количество запрошенных строк по числу уникальных статей. Порядок получения блокировок задаёт ORDER BY в базе данных, а не порядок элементов входного массива. node-postgres: запросы

В только что созданной таблице, если других записывающих операций нет и обе партии выполнены успешно, ожидаемые значения ревизии составляют 1 для статьи 1 и 2 для статей 2 и 3. Эти значения определяются составом партий и не зависят от того, какая из них завершится первой. При последующих запусках скрипта существующие значения будут снова увеличиваться. Если запрошенный ID отсутствует, партия отменяется, а не обновляет молча только найденные строки.

Каждая партия выполняется в отдельной транзакции. Если одна завершится успешно, а другая окончательно завершится ошибкой, успешная партия останется зафиксированной. Promise.allSettled() позволяет дождаться завершения обеих попыток, прежде чем программа закроет пул. Последний вызов pool.query() выполняется вне обеих транзакций. В документации API пула описаны освобождение занятых клиентов, уничтожение клиента при необходимости и закрытие пула после завершения работы скрипта. node-postgres: пул

Проверки нормализации и иллюстративные расчёты значений ревизий были выполнены с помощью Node.js. В исследовательской среде PostgreSQL был недоступен, поэтому SQL и поведение базы данных при параллельном выполнении сверялись с документацией, но не запускались.

Сохраняйте требуемый для задачи уровень изоляции

Для обновлений по фиксированным ID программа явно задаёт уровень READ COMMITTED. На этом уровне команда блокировки может дождаться завершения операции обновления, а затем заблокировать обновлённую версию строки, если она по-прежнему соответствует условию. Для каждой команды создаётся новый снимок данных. Поэтому строка, удалённая до получения блокировки, может отсутствовать; программа обрабатывает это с помощью проверки количества строк. PostgreSQL: изоляция транзакций

Не понижайте уровень изоляции существующей транзакции только ради использования этого примера. При уровне REPEATABLE READ попытка заблокировать или обновить строку, изменённую после создания снимка транзакции, может привести к ошибке сериализации. Уровень SERIALIZABLE также требует обработки повторных попыток и предотвращает аномалии сериализации зафиксированных транзакций; при этом он не отменяет необходимости получать обычные блокировки строк в одном и том же порядке. PostgreSQL: изоляция транзакций

Блокировка известных строк не гарантирует, что состав строк, соответствующих условию, останется прежним. Например, этот пример не реализует задачу «обновить все статьи, заголовок которых соответствует условию, включая добавленные параллельно». Это другая задача с точки зрения согласованности. Прежде чем адаптировать рецепт для фиксированных ID, определите требуемый для неё уровень изоляции и способ координации.

Кроме того, столбец, задающий порядок, должен оставаться неизменным. В документации PostgreSQL указано, что при уровне READ COMMITTED строки сортируются до блокировки; если значения меняются, пока команда ожидает, возвращённые строки могут оказаться не в порядке сортировки. Неизменяемые ID позволяют избежать этой проблемы. PostgreSQL: SELECT

Повторяйте транзакции целиком и выясняйте причины повторных ошибок

PostgreSQL присваивает ошибкам взаимоблокировки код SQLSTATE 40P01, а ошибкам сериализации — 40001. Повторяйте транзакцию целиком, включая все зависящие от базы данных решения, которые определяют команды или значения. Повтор одного лишь последнего обновления не отвечает этому требованию. PostgreSQL: обработка ошибок сериализации

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

При адаптации реализации используйте эту памятку:

Ситуация Действие
Другая партия уже удерживает блокировку запрошенной строки При необходимости обновить все запрошенные строки дождитесь получения блокировок в заданном порядке.
Запрошенный ID отсутствует Откатите транзакцию и сообщите об отсутствии целевой строки.
Код SQLSTATE — 40P01 или 40001 Откатите транзакцию и повторите её целиком в пределах заданного числа попыток, если ошибка допускает повтор.
Ошибки продолжаются после блокировки в заданном порядке Проверьте предыдущие команды, другие операции записи, триггеры и дополнительные таблицы на предмет нарушений контракта порядка.
Операция затрагивает несколько таблиц Задайте единый порядок обработки таблиц и строк для всех конкурирующих путей выполнения.
Партия должна фиксироваться атомарно Оставьте одну транзакцию; фиксация отдельных частей по отдельности изменит задачу.

Не добавляйте SKIP LOCKED в эту партию, которая должна обработать все целевые строки: он пропускает строки, которые нельзя заблокировать немедленно. PostgreSQL описывает этот режим как полезный для потребителей очередей и неподходящий для получения общего согласованного представления. NOWAIT выдаёт ошибку вместо ожидания конфликтующей блокировки строки; это меняет поведение при ожидании, но не позволяет обработать партию целиком. PostgreSQL: SELECT

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

Источники