Открытая транзакция PostgreSQL только для чтения может блокировать DROP TABLE, потому что её SELECT удерживает табличную блокировку ACCESS SHARE до завершения транзакции. Для удаления требуется конфликтующая блокировка ACCESS EXCLUSIVE, поэтому операция ждёт — даже после завершения запроса. PostgreSQL: явные блокировки

В этом руководстве показано, как воспроизвести такое ожидание на Mac, определить блокирующий сеанс и корректно завершить транзакцию в node-postgres. Используются только синтетические строки статей. SQL предназначен для PostgreSQL 18 — версии, описанной в официальной актуальной документации, полученной для этой статьи; перед применением настроек, зависящих от версии, сверьтесь с руководством для вашей версии сервера.

Что означает режим только для чтения для блокировок PostgreSQL?

READ ONLY ограничивает команды, которые может выполнять транзакция. Например, он запрещает изменять обычные таблицы и выполнять такие команды, как DROP TABLE. Однако чтение всё равно сопровождается блокировками таблиц. В этом сценарии читающая транзакция и сеанс, пытающийся удалить таблицу, — разные транзакции. PostgreSQL: SET TRANSACTION

Обычный SELECT автоматически получает блокировку ACCESS SHARE на таблицы, на которые ссылается запрос. Для DROP TABLE требуется ACCESS EXCLUSIVE, конфликтующая с этой блокировкой. Обычно блокировки действуют до конца транзакции; документированное исключение — откат к точке сохранения, созданной до получения блокировки. В примере ниже точки сохранения не используются. PostgreSQL: явные блокировки

Главное — различать завершение запроса и завершение транзакции. Если в терминале появились строки статей, значит, запрос вернул результат. Проверьте, завершилась ли и окружающая транзакция. В psql обычно включён режим автокоммита, но явный BEGIN оставляет транзакцию открытой, пока вы её не завершите. PostgreSQL: psql

Как воспроизвести блокировку удаления на Mac?

Используйте существующую локальную тестовую базу PostgreSQL 18 и убедитесь, что psql уже доступна в Терминале Mac. Откройте три вкладки терминала, подключённые к одной и той же базе. Назовём их A, B и C. Это должны быть отдельные подключения; отдельные панели редактора сами по себе этого не обеспечивают.

Следующая команда подключения предполагает, что локальная тестовая база называется postgres. При необходимости подставьте имя своей существующей тестовой базы. Выполните команду в каждой вкладке. Параметр -X не позволяет загружать личный файл запуска psql. PostgreSQL: psql

psql -X -d postgres

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

\set AUTOCOMMIT on
SHOW server_version;

Создайте синтетическую таблицу статей

Вкладка C: выполните настройку один раз. Используйте имя таблицы, которое ещё не занято: если public.article_lock_demo уже существует, остановитесь и выберите другое имя, последовательно используя его во всех примерах. Настройка намеренно не удаляет существующую таблицу.

CREATE TABLE public.article_lock_demo (
    id integer PRIMARY KEY,
    title text NOT NULL
);

INSERT INTO public.article_lock_demo (id, title)
VALUES
    (1, 'Choosing a notebook'),
    (2, 'Organizing a reading list');

Оставьте транзакцию читателя открытой

Вкладка A: выполните этот блок и оставьте подключение в ожидании приглашения командной строки. Пока не выполняйте коммит.

SET application_name = 'article_reader_demo';
BEGIN READ ONLY;
SHOW transaction_read_only;
SELECT id, title
FROM public.article_lock_demo
ORDER BY id;

В результате transaction_read_only должно отображаться как on, а затем должны появиться две синтетические строки. Чтение завершилось, но явная транзакция остаётся открытой.

Попробуйте удалить таблицу из другого подключения

Вкладка B: выполните следующий блок. Отправьте DROP TABLE как последнюю команду, не добавляя после неё другие команды.

SET application_name = 'article_drop_demo';
BEGIN;
SET LOCAL lock_timeout = '60s';
DROP TABLE public.article_lock_demo;

Удаление должно ожидать, пока A остаётся открытой. Указанное для примера ограничение в 60 секунд даёт время изучить ожидание; это не универсальная рекомендуемая настройка. lock_timeout прерывает команду, если она слишком долго ожидает получения блокировки, и применяется отдельно к каждой попытке получить блокировку. PostgreSQL: значения по умолчанию для подключений клиентов

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

Как доказать, какой сеанс блокирует удаление таблицы?

Пока B ожидает, выполните следующий запрос в C. Он определяет два демонстрационных подключения и запрашивает у PostgreSQL, какие сеансы блокируют запрос блокировки для каждого подключения. pg_blocking_pids() возвращает массив идентификаторов блокирующих процессов или пустой массив, если процесс не заблокирован. PostgreSQL: функции и операторы для получения системной информации

SELECT
    pid,
    application_name,
    state,
    xact_start,
    wait_event_type,
    wait_event,
    pg_blocking_pids(pid) AS blocking_pids,
    query
FROM pg_stat_activity
WHERE datname = current_database()
  AND application_name IN (
      'article_reader_demo',
      'article_drop_demo'
  )
ORDER BY application_name;

Для A ожидаемое состояние — idle in transaction: транзакция открыта, но запрос сейчас не выполняется. B во время ожидания может отображаться как active с типом события ожидания Lock. PostgreSQL указывает, что состояние выполнения и события ожидания независимы, поэтому одного лишь active недостаточно, чтобы установить, что команда продвигается. PostgreSQL: система накопительной статистики

Затем изучите фактические запросы блокировок таблицы. Выполните этот запрос в C, прежде чем завершить транзакцию A.

SELECT
    a.application_name,
    l.pid,
    l.mode,
    l.granted
FROM pg_locks AS l
JOIN pg_stat_activity AS a ON a.pid = l.pid
WHERE l.locktype = 'relation'
  AND l.database = (
      SELECT oid
      FROM pg_database
      WHERE datname = current_database()
  )
  AND l.relation = 'public.article_lock_demo'::regclass
ORDER BY a.application_name, l.mode;

Ожидаемая картина ниже приведена для примера, это не результаты реального выполнения. В pg_locks значение granted = true означает, что процесс удерживает блокировку; false означает, что процесс ожидает её получения. PostgreSQL: pg_locks

Сеанс Режим блокировки granted Интерпретация
article_reader_demo AccessShareLock true Читатель удерживает блокировку таблицы
article_drop_demo AccessExclusiveLock false Удаление ожидает блокировку таблицы

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

Если время ожидания B уже истекло, его ожидающий запрос блокировки больше не будет отображаться. Выполните ROLLBACK; в B, повторите блок транзакции B и быстро проверьте состояние, пока A ещё открыта.

Как освободить читателя и завершить демонстрацию?

В A завершите транзакцию только для чтения:

COMMIT;

Если B всё ещё ожидает, а A была единственным блокирующим сеансом, удаление теперь может завершиться. В B сразу отмените демонстрационное удаление, чтобы синтетическая таблица осталась доступной:

ROLLBACK;

Если время ожидания B истекло до того, как вы зафиксировали транзакцию A, сначала выполните ROLLBACK; в B. После завершения A повторите блок B, а затем снова выполните ROLLBACK;, когда удаление завершится успешно. Так вы корректно обработаете оба возможных исхода и не оставите транзакцию B незавершённой.

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

Наблюдение Следующее действие
Для A указано idle in transaction, а массив блокирующих PID для B содержит PID A Завершите транзакцию A через подключение A
A завершилась, но B по-прежнему сообщает о блокирующих PID Изучите оставшиеся блокирующие сеансы
B сообщает о тайм-ауте блокировки Перед повторной попыткой отмените явную транзакцию B
B сообщает об ошибке зависимости Изучите зависимости, а не считайте это ожиданием блокировки
B возвращает результат сразу Проверьте, оставалась ли предполагаемая транзакция читателя открытой

IF EXISTS обрабатывает ситуацию, когда таблица отсутствует, а CASCADE управляет удалением зависимых объектов. Ни один из этих параметров не задаёт режим ожидания блокировки. В этом упражнении оставьте поведение для зависимостей по умолчанию; в официальном синтаксисе DROP TABLE нет варианта CONCURRENTLY. PostgreSQL: DROP TABLE

Как в node-postgres закрыть транзакцию только для чтения?

В node-postgres используйте один полученный из пула клиент для BEGIN, всех команд транзакции и завершающей команды COMMIT или ROLLBACK. В официальной документации по транзакциям отдельно предупреждается, что для транзакции не следует использовать pool.query(), поскольку команды могут быть отправлены разным клиентам. node-postgres: транзакции

Считайте завершение транзакции и освобождение клиента двумя отдельными шагами приложения. client.release() возвращает полученный из пула клиент обратно в пул; сначала завершите транзакцию. Если откат не удался, API пула позволяет уничтожить клиент, передав аргумент со значением true в release(). node-postgres: API пула

Для этой полной программы используйте синтетическую таблицу из пошагового примера. Возьмите установленную среду Node.js с поддержкой модулей ES и AggregateError и установите pg в рабочем каталоге проекта:

npm install pg

Сохраните следующий код в файле read-articles.mjs. Настройте подключение к той же локальной тестовой базе, что использовалась для psql. node-postgres поддерживает переменные окружения для подключения к PostgreSQL, включая PGDATABASE. node-postgres: подключение

import pg from 'pg';

const { Pool } = pg;
const pool = new Pool({ connectionTimeoutMillis: 5000 });

async function main() {
  try {
    const client = await pool.connect();
    let discard = false;

    try {
      await client.query('BEGIN READ ONLY');
      const result = await client.query(
        'SELECT id, title FROM public.article_lock_demo ORDER BY id'
      );
      await client.query('COMMIT');
      console.table(result.rows);
    } catch (error) {
      try {
        await client.query('ROLLBACK');
      } catch (rollbackError) {
        discard = true;
        throw new AggregateError(
          [error, rollbackError],
          'Query and rollback failed'
        );
      }
      throw error;
    } finally {
      client.release(discard);
    }
  } finally {
    await pool.end();
  }
}

main().catch((error) => {
  console.error(error);
  process.exitCode = 1;
});

PGDATABASE=postgres node read-articles.mjs

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

Для одного независимого чтения в документации рекомендован удобный метод pool.query(), который избавляет от необходимости вручную получать клиента из пула. Используйте явную транзакцию, когда этого требует задача, и не включайте в её время жизни несвязанную обработку файлов или взаимодействие с рабочим столом. node-postgres: API пула

Какой тайм-аут подходит для незавершённой транзакции читателя?

Выбирайте тайм-аут в зависимости от того, что ожидает. lock_timeout ограничивает ожидание блокировки при её получении командой удаления. statement_timeout ограничивает выполнение команды, поэтому он сам по себе не завершает транзакцию, если приложение перестало отправлять команды после успешного чтения. idle_in_transaction_session_timeout специально завершает сеансы, оставшиеся бездействующими внутри открытой транзакции. Это документированные параметры PostgreSQL 18; выбирайте длительность с учётом ожидаемой работы приложения. PostgreSQL: значения по умолчанию для подключений клиентов

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

Источники