Блокировки в PostgreSQL: FOR UPDATE, deadlock и как с ними работать

Блокировки — механизм, который позволяет нескольким транзакциям работать с данными одновременно без конфликтов. Без понимания блокировок легко получить дедлок в продакшене или потерять данные при параллельных обновлениях.

Зачем нужны блокировки

Представь: два пользователя одновременно покупают последний товар на складе. Без блокировки оба прочитают quantity = 1, оба решат что товар есть, оба уменьшат количество — и получится -1 на складе.

-- Проблемная версия (без блокировки)
BEGIN;
SELECT quantity FROM products WHERE id = 42;  -- оба читают: 1
-- ... пауза, в это время второй тоже читает ...
UPDATE products SET quantity = quantity - 1 WHERE id = 42;
COMMIT;
-- Результат: quantity = -1, два успешных заказа

SELECT FOR UPDATE

FOR UPDATE блокирует строки на время транзакции. Другие транзакции, пытающиеся заблокировать те же строки, будут ждать.

-- Правильная версия
BEGIN;
-- Блокируем строку для обновления
SELECT quantity FROM products WHERE id = 42 FOR UPDATE;
-- Другие транзакции с FOR UPDATE будут ждать здесь

-- Теперь безопасно проверить и обновить
UPDATE products
SET quantity = quantity - 1
WHERE id = 42 AND quantity > 0;

COMMIT;

SELECT FOR UPDATE устанавливает строчную блокировку (row-level lock). Конкурирующие SELECT без FOR UPDATE не блокируются — PostgreSQL использует MVCC и они видят последний committed-снимок.

FOR SHARE

FOR SHARE — более мягкая блокировка. Позволяет другим транзакциям тоже читать с FOR SHARE, но блокирует FOR UPDATE.

-- FOR SHARE: несколько транзакций могут удерживать одновременно
BEGIN;
SELECT * FROM orders WHERE id = 100 FOR SHARE;
-- Другие FOR SHARE — ок
-- FOR UPDATE — ждёт
COMMIT;

Используется когда нужно убедиться что строка не будет изменена, пока ты на неё смотришь, но ты сам не собираешься её менять.

FOR UPDATE SKIP LOCKED — паттерн очереди

SKIP LOCKED пропускает уже заблокированные строки вместо ожидания. Идеально для реализации очереди задач:

-- Таблица задач
CREATE TABLE tasks (
    id SERIAL PRIMARY KEY,
    payload JSONB,
    status TEXT DEFAULT 'pending'
);

-- Worker берёт следующую незаблокированную задачу
BEGIN;
SELECT id, payload
FROM tasks
WHERE status = 'pending'
ORDER BY id
LIMIT 1
FOR UPDATE SKIP LOCKED;

-- Если нашли задачу — обрабатываем и обновляем
UPDATE tasks SET status = 'processing' WHERE id = :found_id;
COMMIT;

Без SKIP LOCKED несколько воркеров выстраивались бы в очередь за одной задачей. С SKIP LOCKED каждый воркер мгновенно берёт свою задачу без ожидания.

Уровни блокировок таблиц

Помимо строчных блокировок существуют табличные. PostgreSQL автоматически устанавливает нужный уровень:

ALTER TABLE устанавливает самую жёсткую блокировку и блокирует даже SELECT. На нагруженных таблицах это проблема — миграция может подвесить приложение.

-- Посмотреть текущие блокировки
SELECT
    pid,
    relation::regclass AS table_name,
    mode,
    granted,
    query
FROM pg_locks l
JOIN pg_stat_activity a USING (pid)
WHERE relation IS NOT NULL;

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

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

-- Транзакция A                    -- Транзакция B
BEGIN;                              BEGIN;
UPDATE accounts SET balance = ...   UPDATE accounts SET balance = ...
WHERE id = 1;  -- блокирует id=1    WHERE id = 2;  -- блокирует id=2

UPDATE accounts SET balance = ...   UPDATE accounts SET balance = ...
WHERE id = 2;  -- ждёт B            WHERE id = 1;  -- ждёт A
-- Дедлок!

PostgreSQL обнаруживает дедлоки автоматически и завершает одну из транзакций с ошибкой:

ERROR: deadlock detected
DETAIL: Process 1234 waits for ShareLock on transaction 5678;
        blocked by process 5678.
        Process 5678 waits for ShareLock on transaction 1234;
        blocked by process 1234.

Как предотвратить дедлоки

Правило 1: всегда блокируй ресурсы в одном порядке.

-- Всегда обновляем по возрастанию id
-- Транзакция A                    -- Транзакция B
UPDATE accounts WHERE id = 1;      UPDATE accounts WHERE id = 1;  -- ждёт A
UPDATE accounts WHERE id = 2;      -- A завершится, B продолжит
                                   UPDATE accounts WHERE id = 2;
-- Дедлока нет

Правило 2: минимизируй время удержания блокировок — держи транзакции короткими.

Правило 3: используй lock_timeout чтобы не ждать вечно:

-- Установить таймаут на ожидание блокировки
SET lock_timeout = '3s';

BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- Если не получит блокировку за 3 секунды — ошибка, не зависание

Мониторинг блокировок

-- Найти ожидающие транзакции
SELECT
    pid,
    now() - pg_stat_activity.query_start AS duration,
    query,
    state
FROM pg_stat_activity
WHERE state = 'active'
  AND now() - query_start > interval '5 seconds'
ORDER BY duration DESC;

-- Найти кто кого блокирует
SELECT
    blocked.pid AS blocked_pid,
    blocked.query AS blocked_query,
    blocking.pid AS blocking_pid,
    blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking
    ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE cardinality(pg_blocking_pids(blocked.pid)) > 0;

-- Завершить зависший процесс
SELECT pg_terminate_backend(pid) FROM pg_stat_activity
WHERE pid = <зависший_pid>;

Практические советы

1. SELECT FOR UPDATE — для атомарного «прочитать и обновить»
2. SKIP LOCKED — для очередей задач с несколькими воркерами
3. Порядок блокировок — всегда в одном направлении, чтобы избежать дедлоков
4. lock_timeout — устанавливай в приложении, чтобы не получать вечные зависания
5. Короткие транзакции — чем короче транзакция, тем меньше конкуренция за блокировки
6. pg_locks + pg_stat_activity — диагностируй проблемы в продакшене

Попрактикуйся с транзакциями и блокировками в нашем тренажёре.