Блокировки в PostgreSQL: SELECT FOR UPDATE, SKIP LOCKED и advisory locks

← назад к разделу

Когда два запроса одновременно читают одну строку, принимают решение и оба пытаются её изменить — можно получить противоречивый результат. Блокировки решают именно эту проблему: они позволяют читать строку «с намерением изменить», и никто другой не тронет её, пока вы не закончите.

Как PostgreSQL блокирует строки по умолчанию

UPDATE и DELETE автоматически берут блокировку на каждую затронутую строку. Это происходит незаметно.

-- TX1 обновляет строку
UPDATE orders SET status = 'PAID' WHERE id = 42;

-- TX2 пытается обновить ту же строку одновременно
UPDATE orders SET status = 'CANCELLED' WHERE id = 42;  -- ждёт TX1

При этом обычный SELECT строку не блокирует и не ждёт — это основа MVCC (Multi-Version Concurrency Control): чтение видит «зафиксированную» версию строки на момент начала транзакции и никогда не блокируется записью.

Проблема появляется, когда нужно прочитать, принять решение и обновить — и всё это как одна атомарная операция.

SELECT FOR UPDATE — читаю и собираюсь изменить

Представьте склад: два менеджера одновременно смотрят остаток товара (10 штук) и оба решают «можно продать 8». Оба делают UPDATE. В итоге продано 16 при остатке 10.

SELECT FOR UPDATE блокирует строки уже на этапе чтения:

BEGIN;

SELECT stock FROM product WHERE id = 100 FOR UPDATE;
-- строка заблокирована; второй запрос будет ждать

UPDATE product SET stock = stock - 8 WHERE id = 100;

COMMIT;
-- блокировка снята

Пока первая транзакция не завершится, параллельный SELECT FOR UPDATE той же строки будет ждать. Обычный SELECT (без FOR UPDATE) — не ждёт, видит старое значение.

Важно: FOR UPDATE работает только внутри транзакции. Без @Transactional блокировка снимается сразу, и защиты нет.

Варианты блокировки

В большинстве случаев достаточно FOR UPDATE. Остальные варианты — для специфических ситуаций:

• FOR UPDATE — стандартный вариант. Блокирует строку полностью для любого UPDATE.
• FOR NO KEY UPDATE — мягче: другие транзакции могут делать FOR KEY SHARE на ту же строку. Используется, когда обновляете только не-ключевые поля.
• FOR SHARE — «строка не должна измениться, пока я работаю», но другие могут тоже взять FOR SHARE.
• FOR KEY SHARE — минимальная блокировка, в основном для проверки внешних ключей.

На практике 95% задач решает FOR UPDATE.

SKIP LOCKED — очередь задач из таблицы

Классическая задача: несколько обработчиков параллельно разбирают очередь задач. Как сделать так, чтобы каждый брал свою задачу, не мешая другим?

FOR UPDATE SKIP LOCKED пропускает уже заблокированные строки вместо того чтобы ждать:

BEGIN;

SELECT id, payload
FROM task_queue
WHERE status = 'PENDING'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;
-- каждый обработчик получит свою строку

UPDATE task_queue SET status = 'PROCESSING' WHERE id = :id;

COMMIT;

Несколько обработчиков могут выполнять этот запрос одновременно — каждый захватит свою строку, никто не будет ждать остальных. Это стандартный паттерн для outbox-relay и распределённых очередей.

NOWAIT — лучше ошибка, чем ожидание

Иногда ждать нежелательно: пользователь ожидает мгновенного ответа, и если строка занята — лучше сразу сообщить об этом.

FOR UPDATE NOWAIT не ждёт — сразу бросает ошибку, если строка заблокирована:

SELECT * FROM order_doc WHERE id = :id FOR UPDATE NOWAIT;
-- если строка занята — ошибка, а не ожидание

Приложение ловит исключение и возвращает пользователю «попробуйте через несколько секунд».

jOOQ: как писать блокировки в коде

jOOQ предоставляет удобные методы для всех вариантов:

// FOR UPDATE
ProductRecord product = dsl
    .selectFrom(PRODUCT)
    .where(PRODUCT.ID.eq(productId))
    .forUpdate()
    .fetchOne();

// FOR UPDATE SKIP LOCKED — очередь задач
List<TaskRecord> tasks = dsl
    .selectFrom(TASK_QUEUE)
    .where(TASK_QUEUE.STATUS.eq("PENDING"))
    .orderBy(TASK_QUEUE.CREATED_AT)
    .limit(10)
    .forUpdate()
    .skipLocked()
    .fetch();

// FOR UPDATE NOWAIT
dsl.selectFrom(ORDER_DOC)
    .where(ORDER_DOC.ID.eq(orderId))
    .forUpdate()
    .noWait()
    .fetchOne();

// FOR NO KEY UPDATE
dsl.selectFrom(PRODUCT)
    .where(PRODUCT.ID.eq(productId))
    .forNoKeyUpdate()
    .fetchOne();

Полный пример с обязательным @Transactional:

@Transactional
public void reserveStock(long productId, int quantity) {
    var product = dsl.selectFrom(PRODUCT)
        .where(PRODUCT.ID.eq(productId))
        .forUpdate()
        .fetchOne();

    if (product == null) throw new ProductNotFoundException(productId);
    if (product.getStock() < quantity) throw new InsufficientStockException(productId);

    dsl.update(PRODUCT)
       .set(PRODUCT.STOCK, PRODUCT.STOCK.minus(quantity))
       .where(PRODUCT.ID.eq(productId))
       .execute();
}

Без @Transactional jOOQ закроет соединение после запроса и блокировка снимется немедленно — защита исчезнет.

Pessimistic vs Optimistic: когда что выбрать

SELECT FOR UPDATE — это pessimistic подход: «предполагаю конфликт, блокирую заранее». Он прост в коде, но при большом числе параллельных запросов образует очередь.

Optimistic подход предполагает, что конфликты редки, и проверяет это только в момент записи. Для этого добавляют колонку version:

ALTER TABLE order_doc ADD COLUMN version bigint NOT NULL DEFAULT 0;

Чтение:

SELECT id, status, version FROM order_doc WHERE id = :id;

Обновление с проверкой версии:

UPDATE order_doc
SET status = 'PAID', version = version + 1
WHERE id = :id AND version = :version;
-- если кто-то изменил строку — version другая, 0 rows affected

jOOQ:

int updated = dsl.update(ORDER_DOC)
    .set(ORDER_DOC.STATUS, "PAID")
    .set(ORDER_DOC.VERSION, ORDER_DOC.VERSION.plus(1))
    .where(ORDER_DOC.ID.eq(orderId)
        .and(ORDER_DOC.VERSION.eq(originalVersion)))
    .execute();

if (updated == 0) {
    throw new OptimisticLockException("order " + orderId + " изменён параллельно");
}

Когда что использовать:

• Optimistic — когда конфликты редки (read-heavy): документы, профили, справочники. Меньше нагрузки на базу, выше пропускная способность.
• Pessimistic — когда конфликты часты: финансовые операции, остатки на складе, любые «горячие» строки с высокой конкуренцией.

Advisory locks — блокировка на произвольный ключ

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

Advisory lock — это блокировка на произвольное число (bigint). PostgreSQL хранит её в памяти, а не на строке:

-- транзакционный advisory lock (снимается при COMMIT/ROLLBACK)
SELECT pg_advisory_xact_lock(12345);

-- попытка взять lock без ожидания (возвращает true/false)
SELECT pg_try_advisory_xact_lock(12345);

-- с двумя числами: пространство имён + идентификатор
SELECT pg_advisory_xact_lock(1001, :tenant_id);

Типичные применения:

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

jOOQ:

@Transactional
public void runIfNotAlreadyRunning(long jobKey, Runnable job) {
    Boolean acquired = dsl.select(
        DSL.function("pg_try_advisory_xact_lock", Boolean.class, DSL.val(jobKey))
    ).fetchOne(0, Boolean.class);

    if (Boolean.TRUE.equals(acquired)) {
        job.run();
    } else {
        log.info("job {} уже запущен на другом экземпляре, пропускаю", jobKey);
    }
}

Deadlock — взаимная блокировка

Deadlock возникает, когда две транзакции ждут друг друга:

TX1: блокирует строку с id=1
TX2: блокирует строку с id=2
TX1: пытается заблокировать id=2 — ждёт TX2
TX2: пытается заблокировать id=1 — ждёт TX1
→ тупик

PostgreSQL обнаруживает это через deadlock_timeout (по умолчанию 1 секунда) и прерывает одну из транзакций с ошибкой 40P01.

Главная причина: транзакции берут блокировки в разном порядке.

Решение — всегда блокировать строки в одном порядке, например по возрастанию id:

// Плохо: порядок зависит от аргументов
var from = lockAccount(fromAccountId);
var to   = lockAccount(toAccountId);

// Хорошо: всегда сначала меньший id
long firstId  = Math.min(fromAccountId, toAccountId);
long secondId = Math.max(fromAccountId, toAccountId);
var first  = lockAccount(firstId);
var second = lockAccount(secondId);

Дополнительно: обработка CannotAcquireLockException через повтор запроса:

@Retryable(
    retryFor = CannotAcquireLockException.class,
    maxAttempts = 3,
    backoff = @Backoff(delay = 50, multiplier = 2)
)
@Transactional
public void transferMoney(...) { ... }

1–3 повтора с небольшой паузой между ними решают большинство случаев.

lock_timeout — ограничение времени ожидания

Если транзакция не может получить блокировку за отведённое время — лучше получить ошибку, чем бесконечно висеть в очереди.

SET LOCAL lock_timeout = '5s';
UPDATE order_doc SET ... WHERE id = :id;

Это особенно важно для миграций. ALTER TABLE берёт ACCESS EXCLUSIVE — самую тяжёлую блокировку. Без ограничения времени миграция может часами ждать активных запросов, а за ней выстроится очередь новых:

BEGIN;
SET LOCAL lock_timeout = '3s';
ALTER TABLE orders ADD COLUMN processed_at timestamptz;
COMMIT;

Частые ошибки

• SELECT FOR UPDATE без транзакции — блокировка снимается сразу, защиты нет. Нужен @Transactional.
• FOR UPDATE без индекса по WHERE — база заблокирует всю таблицу вместо нужных строк. Проверяйте план через EXPLAIN.
• Длинная транзакция с блокировкой — строка заблокирована на всё время обработки. Берите блокировку как можно ближе к UPDATE.
• Pessimistic-блокировка на каждое чтение — если конфликты редки, optimistic через version даст меньше нагрузки.
• Блокировки в разном порядке — deadlock. Упорядочивайте по id.
• FOR UPDATE без LIMIT на большой таблице — для очередей задач всегда добавляйте LIMIT.
• ALTER TABLE без lock_timeout — при высокой нагрузке миграция может заблокировать всё. Ставьте SET LOCAL lock_timeout.

Коротко

• UPDATE/DELETE берут row-level блокировку автоматически. Обычный SELECT — нет (MVCC).
• SELECT FOR UPDATE блокирует строки ещё на этапе чтения — для read-modify-write операций. Только внутри @Transactional.
• SKIP LOCKED — пропускает занятые строки: стандартный паттерн для параллельных обработчиков очереди.
• NOWAIT — сразу ошибка вместо ожидания: подходит для API с мгновенным ответом.
• Optimistic (version-колонка) — для редких конфликтов; pessimistic (FOR UPDATE) — для финансов и горячих строк.
• Advisory lock — блокировка на произвольный ключ: для одиночного запуска задания в кластере.
• Deadlock лечится упорядочиванием блокировок по id; Spring @Retryable на CannotAcquireLockException закрывает редкие случаи.
• lock_timeout обязателен для ALTER TABLE — без него миграция может заблокировать весь трафик.

Что почитать дальше

• Уровни изоляции и аномалии — когда SELECT FOR UPDATE недостаточно и нужен SERIALIZABLE.
• Spring @Transactional — propagation, isolation и как транзакция взаимодействует с блокировками.
• Миграции без простоя — lock_timeout и безопасный ALTER TABLE под нагрузкой.