PostgreSQL блокировки и дедлоки: диагностика и устранение в production

Пятница, 18:30. В Slack пишет заказчик: "Сайт завис, никто не может сделать заказ." Открываем мониторинг — PostgreSQL connections: 450/500 (max_connections = 500), все в статусе idle in transaction или waiting. Причина: кто-то запустил ALTER TABLE orders ADD COLUMN delivery_notes TEXT на таблице с 5 миллионами строк. Запрос завис, потому что ждал AccessExclusiveLock. Все остальные запросы к таблице встали в очередь за ним. Очередь из 400 соединений, каждое держит транзакцию открытой. Классический lock queue cascade.

Это была наша самая болезненная встреча с блокировками PostgreSQL. Потом мы разобрались как это работает и перестали наступать на эти грабли.

Уровни блокировок: что реально блокирует что

PostgreSQL имеет несколько уровней гранулярности блокировок. Самые важные для понимания:

Блокировки таблиц (Table-level locks) — 8 режимов, от самого мягкого к самому жёсткому:

Режим	Что его берёт	Совместим с
ACCESS SHARE	SELECT	Всё кроме ACCESS EXCLUSIVE
ROW SHARE	SELECT FOR UPDATE/SHARE	Всё кроме двух последних
ROW EXCLUSIVE	INSERT, UPDATE, DELETE	ACCESS SHARE, ROW SHARE, ROW EXCLUSIVE
SHARE UPDATE EXCLUSIVE	VACUUM, CREATE INDEX CONCURRENTLY	Первые три
SHARE	CREATE INDEX (не concurrent)	ACCESS SHARE, ROW SHARE
SHARE ROW EXCLUSIVE	Редко используется	Только ACCESS SHARE
EXCLUSIVE	Редко	Только ACCESS SHARE
ACCESS EXCLUSIVE	ALTER TABLE, DROP TABLE, TRUNCATE	Ничто

ACCESS EXCLUSIVE — это ядерная боеголовка. Ни один другой запрос не может работать с таблицей пока этот режим активен.

Блокировки строк (Row-level locks) — берутся неявно при UPDATE/DELETE, или явно через SELECT FOR ....

Как читать pg_locks и pg_stat_activity

-- Главный диагностический запрос: кто кого блокирует прямо сейчас
SELECT
    blocked.pid                          AS blocked_pid,
    blocked_activity.usename             AS blocked_user,
    blocking.pid                         AS blocking_pid,
    blocking_activity.usename            AS blocking_user,
    blocked_activity.query               AS blocked_query,
    blocking_activity.query              AS blocking_query,
    blocked_activity.wait_event_type     AS wait_type,
    blocked_activity.wait_event          AS wait_event,
    NOW() - blocked_activity.query_start AS blocked_duration,
    NOW() - blocking_activity.query_start AS blocking_duration
FROM pg_catalog.pg_locks AS blocked
JOIN pg_catalog.pg_stat_activity AS blocked_activity
    ON blocked.pid = blocked_activity.pid
JOIN pg_catalog.pg_locks AS blocking
    ON blocking.relation = blocked.relation
    AND blocking.pid != blocked.pid
    AND blocking.granted = true
    AND blocked.granted = false
JOIN pg_catalog.pg_stat_activity AS blocking_activity
    ON blocking.pid = blocking_activity.pid
ORDER BY blocked_duration DESC;

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

-- Все активные блокировки с деталями
SELECT
    l.pid,
    l.locktype,
    l.relation::regclass AS table_name,
    l.mode,
    l.granted,
    a.usename,
    a.query,
    a.state,
    a.wait_event_type,
    a.wait_event,
    NOW() - a.query_start AS query_age,
    NOW() - a.state_change AS state_age
FROM pg_locks l
JOIN pg_stat_activity a ON l.pid = a.pid
WHERE l.relation IS NOT NULL
ORDER BY l.granted, query_age DESC NULLS LAST;

-- Запросы ожидающие дольше 30 секунд
SELECT
    pid,
    usename,
    state,
    wait_event_type,
    wait_event,
    NOW() - query_start AS duration,
    LEFT(query, 200) AS query_preview
FROM pg_stat_activity
WHERE state != 'idle'
    AND wait_event_type = 'Lock'
    AND NOW() - query_start > INTERVAL '30 seconds'
ORDER BY duration DESC;

-- Убить конкретный процесс (сначала мягко, потом жёстко)
SELECT pg_cancel_backend(pid);   -- Отменяет запрос, транзакция откатывается
SELECT pg_terminate_backend(pid); -- Убивает соединение полностью

AccessExclusiveLock: почему ALTER TABLE страшен в production

Вернёмся к инциденту с пятницей. Вот что произошло шаг за шагом:

1. ALTER TABLE orders ADD COLUMN delivery_notes TEXT запросил ACCESS EXCLUSIVE lock
2. В этот момент выполнялись обычные SELECT запросы — они держат ACCESS SHARE lock
3. ACCESS EXCLUSIVE несовместим с ACCESS SHARE → ALTER встал в очередь ожидания
4. Ключевая деталь: пока ALTER ждёт в очереди — ВСЕ последующие запросы к таблице тоже встают в очередь за ALTER'ом
5. 400 новых соединений, каждое открыло транзакцию и ждёт, application pool заполнен

Это "lock queue cascade" — один ожидающий ACCESS EXCLUSIVE парализует всю таблицу.

Решение для non-nullable столбцов: в PostgreSQL 11+ добавление столбца с default значением больше не требует перезаписи таблицы:

-- PostgreSQL 11+: мгновенная операция, НЕ перезаписывает данные
ALTER TABLE orders ADD COLUMN delivery_notes TEXT DEFAULT '';

-- PostgreSQL < 11: СНАЧАЛА добавляем nullable (мгновенно)
ALTER TABLE orders ADD COLUMN delivery_notes TEXT;
-- ПОТОМ заполняем батчами
UPDATE orders SET delivery_notes = '' WHERE delivery_notes IS NULL AND id BETWEEN 0 AND 100000;
UPDATE orders SET delivery_notes = '' WHERE delivery_notes IS NULL AND id BETWEEN 100000 AND 200000;
-- И только потом добавляем NOT NULL
ALTER TABLE orders ALTER COLUMN delivery_notes SET NOT NULL;

Безопасный ALTER TABLE с таймаутом:

-- Устанавливаем таймаут на получение lock — лучше упасть быстро, чем висеть
SET lock_timeout = '2s';
SET statement_timeout = '30s';

BEGIN;
ALTER TABLE orders ADD COLUMN delivery_notes TEXT;
COMMIT;

Если lock не получен за 2 секунды — запрос падает с ошибкой. Это лучше чем висеть и блокировать остальных.

FOR UPDATE vs FOR NO KEY UPDATE vs FOR SHARE

Row-level locking при явных запросах:

-- FOR UPDATE: блокирует строки для обновления
-- Несовместим с другими FOR UPDATE / FOR NO KEY UPDATE / FOR SHARE
SELECT * FROM orders WHERE id = 42 FOR UPDATE;

-- FOR NO KEY UPDATE: блокирует строки, но разрешает операции которые
-- не трогают первичный ключ (например, обновление статуса)
-- Менее агрессивен, разрешает FOR KEY SHARE
SELECT * FROM orders WHERE id = 42 FOR NO KEY UPDATE;

-- FOR SHARE: разрешает другим транзакциям читать, но не изменять
SELECT * FROM orders WHERE id = 42 FOR SHARE;

-- FOR KEY SHARE: минимальная блокировка, только защита от DELETE
-- Используется при FK constraints
SELECT * FROM orders WHERE id = 42 FOR KEY SHARE;

Практический пример: обновление баланса пользователя:

-- Сессия 1
BEGIN;
SELECT balance FROM wallets WHERE user_id = 42 FOR UPDATE;
-- balance = 1000

-- Сессия 2 (одновременно)
BEGIN;
SELECT balance FROM wallets WHERE user_id = 42 FOR UPDATE;
-- ← ЖДЁТ, пока Сессия 1 не закоммитит или откатится

-- Сессия 1 продолжает
UPDATE wallets SET balance = balance - 100 WHERE user_id = 42;
COMMIT;

-- Теперь Сессия 2 получает lock и видит уже обновлённый баланс = 900
UPDATE wallets SET balance = balance - 50 WHERE user_id = 42;
COMMIT;
-- Итог: 850. Правильно!

Без FOR UPDATE обе сессии прочитали бы 1000, и обе записали бы 900 или 950. Классическое race condition.

Дедлоки: воспроизводим и исправляем

Дедлок возникает когда две транзакции ждут друг друга. PostgreSQL обнаруживает дедлоки автоматически (через граф ожидания) и откатывает одну из транзакций.

Классический пример дедлока:

-- Транзакция A (выполняется в одном соединении)
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1; -- Блокирует строку 1
-- (небольшая пауза)
UPDATE accounts SET balance = balance + 100 WHERE id = 2; -- Ждёт строку 2

-- Транзакция B (одновременно в другом соединении)
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 2; -- Блокирует строку 2
-- (небольшая пауза)
UPDATE accounts SET balance = balance + 100 WHERE id = 1; -- Ждёт строку 1
-- ← DEADLOCK: A ждёт B, B ждёт A
-- PostgreSQL откатит одну из них с ошибкой:
-- ERROR: deadlock detected
-- DETAIL: Process 12345 waits for ShareLock on transaction 67890

Как воспроизвести дедлок для тестирования:

-- Создаём тестовую таблицу
CREATE TABLE accounts (id INT PRIMARY KEY, balance DECIMAL);
INSERT INTO accounts VALUES (1, 1000), (2, 1000);

-- В psql сессия 1:
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- (не коммитим, переключаемся в сессию 2)

-- В psql сессия 2:
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 2;
UPDATE accounts SET balance = balance + 100 WHERE id = 1; -- Ждёт сессию 1

-- Возвращаемся в сессию 1:
UPDATE accounts SET balance = balance + 100 WHERE id = 2; -- DEADLOCK!

Исправление: всегда обновляйте строки в одном порядке:

// В коде: сортируем ID перед обновлением
async function transferMoney(fromId: number, toId: number, amount: number) {
    // Всегда блокируем строки в порядке возрастания ID
    const [firstId, secondId] = fromId < toId ? [fromId, toId] : [toId, fromId];
    
    await db.transaction(async (trx) => {
        // Блокируем обе строки в одном запросе — нет возможности для дедлока
        const accounts = await trx('accounts')
            .whereIn('id', [firstId, secondId])
            .orderBy('id', 'asc')
            .forUpdate()
            .select('*');
        
        const fromAccount = accounts.find(a => a.id === fromId);
        const toAccount = accounts.find(a => a.id === toId);
        
        if (!fromAccount || fromAccount.balance < amount) {
            throw new Error('Insufficient balance');
        }
        
        await trx('accounts').where('id', fromId).update({
            balance: trx.raw('balance - ?', [amount])
        });
        await trx('accounts').where('id', toId).update({
            balance: trx.raw('balance + ?', [amount])
        });
    });
}

Retry при дедлоке — потому что PostgreSQL откатывает одну транзакцию, её нужно повторить:

async function withDeadlockRetry<T>(
    operation: () => Promise<T>,
    maxRetries = 3
): Promise<T> {
    for (let attempt = 0; attempt < maxRetries; attempt++) {
        try {
            return await operation();
        } catch (error: unknown) {
            const pgError = error as { code?: string };
            // 40P01 = deadlock_detected
            if (pgError?.code === '40P01' && attempt < maxRetries - 1) {
                const delay = Math.random() * 100 * (attempt + 1); // jitter
                console.warn(`Deadlock detected, retry ${attempt + 1}/${maxRetries} in ${delay}ms`);
                await new Promise(resolve => setTimeout(resolve, delay));
                continue;
            }
            throw error;
        }
    }
    throw new Error('Max retries exceeded');
}

// Использование
await withDeadlockRetry(() => transferMoney(fromId, toId, amount));

SELECT FOR UPDATE SKIP LOCKED: паттерн для job queues

Это один из моих любимых паттернов в PostgreSQL — надёжная очередь задач без внешних зависимостей:

-- Таблица задач
CREATE TABLE job_queue (
    id           BIGSERIAL PRIMARY KEY,
    job_type     TEXT NOT NULL,
    payload      JSONB NOT NULL,
    status       TEXT NOT NULL DEFAULT 'pending', -- pending, processing, done, failed
    attempts     INT NOT NULL DEFAULT 0,
    max_attempts INT NOT NULL DEFAULT 3,
    created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    scheduled_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    started_at   TIMESTAMPTZ,
    completed_at TIMESTAMPTZ,
    error        TEXT
);

CREATE INDEX idx_job_queue_pickup 
    ON job_queue (scheduled_at, status) 
    WHERE status = 'pending';

-- Worker берёт задачу: атомарно, без дублирования между workers
WITH next_job AS (
    SELECT id
    FROM job_queue
    WHERE status = 'pending'
        AND scheduled_at <= NOW()
        AND attempts < max_attempts
    ORDER BY scheduled_at
    LIMIT 1
    FOR UPDATE SKIP LOCKED  -- Пропускаем заблокированные другими workers строки
)
UPDATE job_queue
SET 
    status = 'processing',
    attempts = attempts + 1,
    started_at = NOW()
WHERE id = (SELECT id FROM next_job)
RETURNING *;

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

class PostgreSQLJobQueue {
    constructor(private db: Knex) {}
    
    async dequeue(jobTypes: string[]): Promise<Job | null> {
        const result = await this.db.raw(`
            WITH next_job AS (
                SELECT id
                FROM job_queue
                WHERE status = 'pending'
                    AND job_type = ANY(?)
                    AND scheduled_at <= NOW()
                    AND attempts < max_attempts
                ORDER BY scheduled_at
                LIMIT 1
                FOR UPDATE SKIP LOCKED
            )
            UPDATE job_queue
            SET status = 'processing', attempts = attempts + 1, started_at = NOW()
            WHERE id = (SELECT id FROM next_job)
            RETURNING *
        `, [jobTypes]);
        
        return result.rows[0] ?? null;
    }
    
    async complete(jobId: number): Promise<void> {
        await this.db('job_queue')
            .where('id', jobId)
            .update({ status: 'done', completed_at: this.db.fn.now() });
    }
    
    async fail(jobId: number, error: string): Promise<void> {
        await this.db('job_queue')
            .where('id', jobId)
            .update({ 
                status: this.db.raw(`
                    CASE WHEN attempts >= max_attempts THEN 'failed' ELSE 'pending' END
                `),
                error,
                scheduled_at: this.db.raw(`NOW() + INTERVAL '5 minutes'`) // retry backoff
            });
    }
    
    async runWorker(jobTypes: string[], handler: (job: Job) => Promise<void>) {
        while (true) {
            const job = await this.dequeue(jobTypes);
            
            if (!job) {
                await new Promise(resolve => setTimeout(resolve, 1000)); // poll interval
                continue;
            }
            
            try {
                await handler(job);
                await this.complete(job.id);
            } catch (error) {
                await this.fail(job.id, String(error));
            }
        }
    }
}

Этот паттерн хорошо работает до ~1,000 jobs/sec. Для большего throughput нужен настоящий message broker (BullMQ + Redis, или Kafka). Но для большинства приложений PostgreSQL job queue — это всё что нужно, без дополнительной инфраструктуры.

Advisory locks: именованные блокировки приложения

Иногда нужно заблокировать не строку, а бизнес-сущность. Advisory locks — встроенный механизм для этого:

-- Блокировка по числовому ID (например, user_id)
-- Автоматически освобождается при конце сессии/транзакции
SELECT pg_try_advisory_lock(42);  -- true если получили, false если уже занято

-- В транзакции: освобождается при COMMIT/ROLLBACK
SELECT pg_try_advisory_xact_lock(42);

-- Пример: обработка платежа пользователя без race condition
BEGIN;
SELECT pg_advisory_xact_lock(user_id) FROM users WHERE id = 42;
-- Теперь никто другой не может получить lock на user 42
-- Делаем операцию...
UPDATE wallets SET balance = balance - 100 WHERE user_id = 42;
COMMIT; -- Lock автоматически освобождается

// В коде: advisory lock для критических операций
async function processPaymentWithLock(userId: number, amount: number) {
    return await db.transaction(async (trx) => {
        // Получаем advisory lock на этого пользователя
        // Если другой процесс уже обрабатывает — ждём
        await trx.raw('SELECT pg_advisory_xact_lock(?)', [userId]);
        
        // Теперь гарантированно единственный обработчик для этого userId
        const wallet = await trx('wallets').where('user_id', userId).first();
        
        if (wallet.balance < amount) {
            throw new Error('Insufficient funds');
        }
        
        await trx('wallets')
            .where('user_id', userId)
            .update({ balance: trx.raw('balance - ?', [amount]) });
        
        // Lock освободится при COMMIT
    });
}

Итог: чеклист для работы с блокировками

1. pg_stat_activity и pg_locks — ваши первые инструменты при проблемах
2. ALTER TABLE в production — только с lock_timeout и в непиковые часы
3. Обновляйте строки в детерминированном порядке → нет дедлоков
4. Retry при 40P01 (deadlock) — обязателен в приложении
5. SELECT FOR UPDATE SKIP LOCKED — надёжная job queue без внешних зависимостей
6. Advisory locks — для бизнес-логики которая не привязана к конкретным строкам

---

Столкнулись с проблемами производительности или блокировками в PostgreSQL? Aunimeda проводит аудит баз данных и оптимизацию. Расскажите о вашей ситуации.