PostgreSQL: блокировки и как использовать их с jOOQ

PostgreSQL даёт три уровня блокировок: table-level (на DDL и LOCK TABLE), row-level (на строки через SELECT FOR ...), advisory (произвольные, на ваш bigint-ключ). В прикладной разработке нужны последние два. Эта статья — что и когда брать, как делать через jOOQ, и какие ловушки ждут на бою.

1. Базовая модель: что блокируется без вашего участия

PG-L-001 UPDATE / DELETE уже берут row-level lock автоматически. Параллельный UPDATE той же строки ждёт. Параллельный SELECT без FOR UPDATE — НЕ ждёт (читает старую версию через MVCC).

TX1: UPDATE orders SET status='PAID' WHERE id=42;
TX2: UPDATE orders SET status='CANCELLED' WHERE id=42;   ← ждёт, пока TX1 закоммитит
TX2: SELECT status FROM orders WHERE id=42;              ← НЕ ждёт, видит старую (committed)

Это «оптимистичная» картина MVCC: чтение никогда не ждёт запись. Поэтому фантомов между чтением и решением «надо ли менять» — навалом, и SELECT FOR UPDATE существует именно для них.

2. SELECT FOR UPDATE — главный инструмент

PG-L-010 Используй SELECT FOR UPDATE, когда читаешь строку, чтобы потом её изменить, и нужна гарантия, что между чтением и записью никто другой не вмешается.

Классический сценарий — резервирование товара:

BEGIN;
SELECT stock FROM product WHERE id = 100 FOR UPDATE;  -- блокирует строку
-- решаем: stock=5, заказ на 3, ок, можем зарезервировать
UPDATE product SET stock = stock - 3 WHERE id = 100;
COMMIT;

Без FOR UPDATE два параллельных запроса прочитали бы stock=5, оба решили «можем», оба сделали UPDATE — продали 6 единиц при остатке 5.

PG-L-011 FOR UPDATE блокирует только до конца транзакции. Без транзакции (autocommit) лок отпускается сразу — толку нет. В Spring это значит: @Transactional обязателен.

3. Варианты FOR ... — что выбрать

Команда	Что делает	Когда брать
FOR UPDATE	Полная блокировка строки на запись	Резерв остатков, изменение статуса, любой UPDATE-after-SELECT
FOR NO KEY UPDATE	Слабее, не блокирует FK-операции	UPDATE полей, которые не PK/UK (HOT update friendly)
FOR SHARE	Несколько TX могут держать одновременно, но никто не может UPDATE	«Эта строка не должна измениться, пока я работаю» (редко нужно)
FOR KEY SHARE	Самый слабый	Удержать строку для FK-проверки

PG-L-012 В 95% случаев нужен FOR UPDATE. Остальные — оптимизации, не дёргай без понимания.

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

PG-L-020 FOR UPDATE SKIP LOCKED пропускает уже залоченные строки. Идеальный паттерн для очереди задач в БД.

-- worker берёт следующую невзятую задачу
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', worker_id = ? WHERE id = ?;
COMMIT;

-- (обработка)
-- ...

-- завершение
UPDATE task_queue SET status = 'DONE' WHERE id = ?;

Несколько worker-ов могут одновременно дёргать SELECT FOR UPDATE SKIP LOCKED — каждый получит свою строку, никто не ждёт. Это и есть простая распределённая очередь без Kafka/Redis.

PG-L-021 Используется в outbox-relay: каждый instance сервиса берёт строки SKIP LOCKED, чтобы при многих инстансах не было дублирующей публикации.

5. NOWAIT — fail-fast вместо ожидания

PG-L-030 FOR UPDATE NOWAIT падает с ошибкой, если строка занята, вместо ожидания.

SELECT * FROM order_doc WHERE id = ? FOR UPDATE NOWAIT;

Полезно, когда:

• Контролируем таймауты на уровне приложения, не хотим зависать на дефолтном lock_timeout.
• Лучше отказаться, чем ждать — например, в API-эндпоинте «ну, попробуйте через минуту».

6. jOOQ: как написать всё это правильно

PG-L-040 jOOQ умеет все варианты блокировок через DSL.

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

// FOR UPDATE SKIP LOCKED
List<TaskRecord> tasks = ctx
    .selectFrom(TASK_QUEUE)
    .where(TASK_QUEUE.STATUS.eq("PENDING"))
    .orderBy(TASK_QUEUE.CREATED_AT)
    .limit(10)
    .forUpdate()
    .skipLocked()
    .fetch();

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

// FOR NO KEY UPDATE
ctx.selectFrom(ORDER_DOC)
    .where(ORDER_DOC.ID.eq(orderId))
    .forNoKeyUpdate()
    .fetchOne();

PG-L-041 Lock запрос обязан быть внутри @Transactional-метода Spring. Иначе jOOQ откроет, выполнит, закроет соединение — лок отпустится мгновенно.

@Transactional
public void reserveStock(long productId, int quantity) {
    ProductRecord 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();
}

7. Pessimistic vs Optimistic locking

PG-L-050 Два подхода. Выбирай по нагрузке и характеру конфликтов.

Pessimistic (SELECT FOR UPDATE)

• Берёшь лок заранее, никто другой не лезет.
• Простой код.
• При высокой конкуренции — очередь TX, троттлинг.

Optimistic (через version-колонку)

• Не блокируешь заранее. На UPDATE проверяешь, что версия не изменилась с момента чтения.
• Если изменилась — OptimisticLockException, перечитывай и повторяй.
• Лучше масштабируется при низкой реальной конкуренции.

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

-- READ
SELECT id, status, version FROM order_doc WHERE id = ?;

-- UPDATE с проверкой версии
UPDATE order_doc
SET status = 'PAID', version = version + 1
WHERE id = ? AND 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 + " modified concurrently");
}

PG-L-051 Optimistic для read-heavy, pessimistic для write-heavy и денежных операций. Когда конфликты редки (раз в 1000 запросов) — optimistic дешевле. Когда часто (десяток конкурентов на одну строку) — pessimistic не даёт «10 retry в цикле».

8. Advisory locks — блокировка не на строку, а на абстрактный ключ

PG-L-060 Advisory lock — собственная блокировка на произвольный bigint-ключ. Не привязан к таблице.

Полезно, когда нужно «провести операцию X в одном экземпляре сервиса», но не на строку БД:

• Запуск scheduled-job'а только одним инстансом из кластера.
• Глобальная очередь.
• Предотвращение двух одновременных миграций.

Два варианта:

-- сессионный (отпускается при отключении)
SELECT pg_advisory_lock(12345);

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

Try-вариант (не ждёт):

SELECT pg_try_advisory_xact_lock(12345);  -- true/false

В 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 {} already running on another instance, skip", jobKey);
    }
}

PG-L-061 Используй два bigint-аргумента, если лок логически разделяется на две части: pg_advisory_lock(class_id, object_id). Например, (1001, tenant_id) — лок на tenant_id в namespace «refresh-cache».

9. Deadlock — что это и как ловить

PG-L-070 Deadlock возникает, когда две транзакции берут локи в РАЗНОМ порядке.

TX1: UPDATE orders WHERE id=1   -- блокирует строку 1
TX2: UPDATE orders WHERE id=2   -- блокирует строку 2
TX1: UPDATE orders WHERE id=2   -- ждёт TX2
TX2: UPDATE orders WHERE id=1   -- ждёт TX1
                                -- DEADLOCK

PostgreSQL детектит deadlock через deadlock_timeout (default 1s) и убивает одну из TX с ошибкой 40P01 deadlock detected.

PG-L-071 Лечится упорядочением блокировок. Всегда брать строки в одном и том же порядке.

// плохо: порядок зависит от пользовательского ввода
public void transferMoney(long fromAccountId, long toAccountId, BigDecimal amount) {
    var from = lockAccount(fromAccountId);
    var to   = lockAccount(toAccountId);
    // если параллельно идёт обратный transfer (toId → fromId), будет deadlock
}

// хорошо: всегда блокируем в порядке возрастания id
public void transferMoney(long fromAccountId, long toAccountId, BigDecimal amount) {
    long firstId  = Math.min(fromAccountId, toAccountId);
    long secondId = Math.max(fromAccountId, toAccountId);
    var first  = lockAccount(firstId);
    var second = lockAccount(secondId);
    // оба перевода блокируют в одном порядке → нет deadlock
}

PG-L-072 Для deadlockов в коде Spring — обработайorg.springframework.dao.CannotAcquireLockException` (PG код 40P01) с retry. Это не баг, это нормальный исход в high-conc OLTP. 1–3 retry с экспоненциальной паузой обычно решают.

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

10. lock_timeout и statement_timeout

PG-L-080 Поставь lock_timeout для критичных операций — не жди вечно.

SET LOCAL lock_timeout = '5s';
UPDATE order_doc ... ;

В Spring это можно через @Transactional-настройки или через entityManager.createNativeQuery("SET LOCAL lock_timeout = '5s'") в начале метода.

Особенно важно для миграций: ALTER TABLE берёт ACCESS EXCLUSIVE — если хоть кто-то читает таблицу с активной транзакцией, миграция заблокирована, а за ней — все новые запросы (из-за lock queue).

-- безопасный паттерн миграции
BEGIN;
SET LOCAL lock_timeout = '3s';
ALTER TABLE orders ADD COLUMN ...;
COMMIT;

11. Антипаттерны

PG-L-090 SELECT FOR UPDATE без транзакции — лок мгновенно отпускается, проверка бесполезна.

PG-L-091 SELECT FOR UPDATE со сложным WHERE, который индекс не покрывает — может залочить кучу строк через seq scan. Проверь EXPLAIN.

PG-L-092 Длинная транзакция с локом → блокирует всех ждущих и копит WAL (см. WAL §8). Лок брать как можно ближе к UPDATE, отпускать сразу.

PG-L-093 Pessimistic lock на каждое чтение — превращает БД в очередь. Используй optimistic, где конфликты редки.

PG-L-094 Брать локи в разном порядке в разных методах — deadlock. Зафиксируй правило (например, по возрастанию PK) и придерживайся.

PG-L-095 SELECT FOR UPDATE на таблице с миллионами строк без LIMIT — внезапно блокирует все строки. Всегда LIMIT N для очередей.

---

Чек-лист «нужна блокировка»

1. Read-modify-write в одной TX по одной строке? → SELECT FOR UPDATE.
2. Очередь задач, несколько worker-ов? → SELECT FOR UPDATE SKIP LOCKED LIMIT N.
3. Не хочешь ждать, лучше упасть? → FOR UPDATE NOWAIT.
4. Конфликты редки, важна пропускная способность? → optimistic с version-колонкой.
5. Гарантировать, что job выполняется в одном экземпляре кластера? → pg_try_advisory_xact_lock.
6. ALTER TABLE на проде? → SET LOCAL lock_timeout + CONCURRENTLY для индексов.
7. Перевод денег между счетами? → блокировать в порядке возрастания id + retry на deadlock.

---

Связанные

• WAL — длинные транзакции с локами блокируют WAL и autovacuum.
• Composite-индексы — индекс под условие FOR UPDATE обязателен.
• Антипаттерны.