Блокировки в базах данных

Блокировки — механизм управления конкурентным доступом к данным. Понимание блокировок критично для проектирования систем с высокой нагрузкой и для объяснения проблем производительности на интервью.

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

Без блокировок параллельные транзакции могут испортить данные:

T1: SELECT balance FROM accounts WHERE id = 1;  -- reads 1000
T2: SELECT balance FROM accounts WHERE id = 1;  -- reads 1000
T1: UPDATE accounts SET balance = 1000 - 100 = 900 WHERE id = 1;
T2: UPDATE accounts SET balance = 1000 - 200 = 800 WHERE id = 1;
-- Final: 800, but should be 700 (lost update!)

Уровни блокировок

Row-Level Locks (Блокировки строк)

Самые гранулярные блокировки. Блокируют только конкретные строки.

-- Exclusive row lock (FOR UPDATE)
-- Prevents other transactions from UPDATE/DELETE/FOR UPDATE on these rows
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;

-- Share lock (FOR SHARE)
-- Prevents UPDATE/DELETE, but allows other FOR SHARE
SELECT * FROM accounts WHERE id = 1 FOR SHARE;

-- Skip locked rows (non-blocking)
SELECT * FROM tasks WHERE status = 'pending' LIMIT 10 FOR UPDATE SKIP LOCKED;

-- Fail immediately if can't lock
SELECT * FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;

Table-Level Locks (Блокировки таблиц)

Блокируют всю таблицу. Используются автоматически для DDL операций.

-- Explicit table lock
LOCK TABLE accounts IN SHARE MODE;        -- allows reads, blocks writes
LOCK TABLE accounts IN EXCLUSIVE MODE;    -- blocks reads and writes
LOCK TABLE accounts IN ROW EXCLUSIVE MODE; -- allows row locks, blocks table locks

Пессимистичное блокирование (Pessimistic Locking)

Идея: «кто-то другой точно хочет изменить эти данные, заблокирую заранее».

SELECT FOR UPDATE

-- Classic pattern: read, then update with lock
BEGIN;
    -- Lock the row immediately
    SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
    -- No other transaction can UPDATE this row until COMMIT/ROLLBACK

    -- Safe to compute and update
    UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

Где применяется

-- Queue processing: grab a task exclusively
BEGIN;
    SELECT id, payload FROM job_queue
    WHERE status = 'pending'
    ORDER BY created_at
    LIMIT 1
    FOR UPDATE SKIP LOCKED;  -- skip tasks locked by other workers

    UPDATE job_queue SET status = 'processing', worker_id = $1
    WHERE id = :selected_id;
COMMIT;

-- Inventory reservation: prevent overselling
BEGIN;
    SELECT qty FROM inventory WHERE product_id = 5 FOR UPDATE;
    -- qty = 3

    IF qty >= requested_qty THEN
        UPDATE inventory SET qty = qty - requested_qty WHERE product_id = 5;
        INSERT INTO reservations ...;
        COMMIT;
    ELSE
        ROLLBACK;
    END IF;

Недостатки пессимистичного подхода

• Снижение конкурентности — транзакции ждут друг друга
• Риск дедлока — если два процесса блокируют в разном порядке
• Долгие блокировки — при медленных транзакциях остальные ждут

Оптимистичное блокирование (Optimistic Locking)

Идея: «конфликты редки, поэтому не блокируем заранее — проверяем при сохранении».

Реализация через version/timestamp

-- Schema: add version column
ALTER TABLE products ADD COLUMN version INTEGER NOT NULL DEFAULT 0;

-- Read (no lock)
SELECT id, price, version FROM products WHERE id = 5;
-- Returns: id=5, price=100, version=3

-- Update: check that version hasn't changed
UPDATE products
SET price = 150, version = version + 1
WHERE id = 5 AND version = 3;  -- conditional on version

-- Check if update was applied (rowsAffected == 0 means conflict)

// Application logic
function updateProductPrice(int $id, float $newPrice): void {
    $product = $db->query("SELECT id, price, version FROM products WHERE id = ?", [$id]);

    // Do some business logic...
    $calculatedPrice = $newPrice * 1.2;

    $affected = $db->execute(
        "UPDATE products SET price = ?, version = version + 1 WHERE id = ? AND version = ?",
        [$calculatedPrice, $id, $product['version']]
    );

    if ($affected === 0) {
        throw new OptimisticLockException("Product was modified by another transaction. Please retry.");
    }
}

Реализация через updated_at

-- Using timestamp instead of version
SELECT id, price, updated_at FROM products WHERE id = 5;
-- Returns: updated_at = '2024-01-15 10:30:00'

UPDATE products
SET price = 150, updated_at = NOW()
WHERE id = 5 AND updated_at = '2024-01-15 10:30:00';

Когда выбрать оптимистичный подход

• Конфликты редки (большинство пользователей редактируют разные данные)
• Операции короткие (время между чтением и записью мало)
• Высокая конкурентность важнее гарантии немедленной записи

Когда выбрать пессимистичный подход

• Конфликты часты (например, несколько кассиров бронируют место на мероприятии)
• Нельзя допустить retry (платёжные операции)
• Операция требует эксклюзивного доступа (списание денег)

Advisory Locks (Рекомендательные блокировки)

Специфика PostgreSQL: приложение само управляет блокировками, СУБД их только хранит.

-- Session-level (released when connection closes)
SELECT pg_advisory_lock(12345);         -- blocks until acquired
SELECT pg_try_advisory_lock(12345);     -- returns false if can't acquire
SELECT pg_advisory_unlock(12345);

-- Transaction-level (released on COMMIT/ROLLBACK)
SELECT pg_advisory_xact_lock(12345);
SELECT pg_try_advisory_xact_lock(12345);

Практическое применение

-- Distributed mutex: only one process does expensive operation
BEGIN;
    -- Try to get advisory lock for "generate_monthly_report"
    SELECT pg_try_advisory_xact_lock(hashtext('generate_monthly_report'));

    IF NOT FOUND THEN
        -- Another instance is already generating the report
        RETURN;
    END IF;

    -- Safe to generate report — only one process here
    PERFORM generate_report();
COMMIT;  -- lock released automatically

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

-- See all current locks
SELECT pid, locktype, relation::regclass, mode, granted
FROM pg_locks
WHERE NOT granted;  -- waiting locks

-- Find blocking queries
SELECT blocked.pid, blocked_query.query AS blocked_query,
       blocking.pid AS blocking_pid, blocking_query.query AS blocking_query
FROM pg_stat_activity blocked_query
JOIN pg_locks blocked ON blocked_query.pid = blocked.pid AND NOT blocked.granted
JOIN pg_locks blocking ON blocked.locktype = blocking.locktype
    AND blocked.relation = blocking.relation
    AND blocking.granted
JOIN pg_stat_activity blocking_query ON blocking_query.pid = blocking.pid
WHERE blocked_query.wait_event_type = 'Lock';

-- Kill a blocking query
SELECT pg_terminate_backend(pid);

Типичные вопросы на интервью

Q: «В чём разница между оптимистичным и пессимистичным блокированием?»

Ответ: «Пессимистичное — блокируем данные до начала работы с ними (SELECT FOR UPDATE). Оптимистичное — не блокируем, но при сохранении проверяем, не изменил ли данные кто-то другой (через version-поле). Пессимистичное безопаснее при частых конфликтах, оптимистичное — при редких, так как не снижает конкурентность.»

Q: «Что делает SELECT FOR UPDATE SKIP LOCKED?»

Ответ: «Пропускает строки, заблокированные другими транзакциями, вместо ожидания их освобождения. Классическое применение — очередь задач: несколько воркеров конкурируют за задачи, SKIP LOCKED позволяет каждому сразу взять свободную задачу.»

Q: «Как понять, что запрос заблокирован другой транзакцией?»

Ответ: «pg_stat_activity показывает wait_event_type = 'Lock'. pg_locks с NOT granted — незавершённые ожидания. Есть готовые запросы для поиска блокирующей транзакции через JOIN этих двух представлений.»