Транзакции, MVCC и конкурентный доступ

Транзакции, MVCC и конкурентный доступ

Главная идея

Как это выглядит на практике

1. Система онлайн-платежей: пользователь нажимает 'оплатить' два раза быстро — два запроса приходят параллельно. Оба читают баланс (1000 руб), оба проверяют достаточность для оплаты 800 руб, оба списывают. Итого: баланс -600 руб. Классическая race condition без транзакций.
2. Решение: BEGIN; SELECT balance FOR UPDATE; проверка; UPDATE; COMMIT. SELECT FOR UPDATE блокирует строку — второй параллельный запрос ждёт пока первый завершится. Баланс корректен.
3. Появляется очередь фоновых задач: email-рассылка, генерация отчётов. 3 воркера пытаются брать задачи. Без SKIP LOCKED: все три lock'ают одну строку, два ждут, потом все три обрабатывают одно и то же — дублирование.
4. Добавляешь FOR UPDATE SKIP LOCKED: каждый воркер атомарно берёт следующую незаблокированную задачу. Три воркера параллельно обрабатывают три разные задачи без конфликтов.
5. Инцидент: мониторинг показывает что запросы к таблице orders стали медленными. SELECT из pg_stat_activity показывает процесс с wait_event_type = 'Lock' — кто-то держит блокировку. Запускаешь диагностический запрос — видишь что транзакция 'idle in transaction' держит lock уже 5 минут.
6. Добавляешь в postgresql.conf: idle_in_transaction_session_timeout = '30s'. Теперь транзакции без активности автоматически завершаются через 30 секунд.
7. Новое требование: SERIALIZABLE транзакции для финансового отчёта — нужна абсолютная консистентность. Добавляешь BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE. В нагрузочном тесте замечаешь ошибки serialization_failure. Добавляешь retry-логику в приложении.
8. Задача: одновременно два процесса могут обновлять кеш статистики — нужен мьютекс без таблицы. Используешь pg_try_advisory_lock с числовым ключом. Первый процесс получает lock, второй получает false и пропускает работу.
9. Deadlock: транзакция A блокирует строку пользователя 1 затем пользователя 2, транзакция B — наоборот. PostgreSQL обнаруживает и откатывает одну. Решение: всегда блокировать строки в одном порядке (ORDER BY id).
10. Мониторинг: запрос в pg_stat_user_tables показывает огромный n_dead_tup в таблице orders. Оказывается, долгая аналитическая транзакция (REPEATABLE READ) держит старый snapshot — VACUUM не может убирать мёртвые туплы. Ограничиваешь время транзакций через statement_timeout.
11. Table bloat: таблица events занимает 50GB, из них 30GB — мёртвые туплы. VACUUM не справляется. Ищешь долгие транзакции в pg_stat_activity, находишь ETL-процесс с open транзакцией на 2 часа. Исправляешь ETL — добавляешь явные COMMIT между батчами.
12. Проектируешь систему с оптимистичным локом: версионирование через столбец version INT. При UPDATE: WHERE id = $1 AND version = $old_version; проверяешь rows_affected = 1. Если 0 — конфликт, retry. Это альтернатива FOR UPDATE для low-contention сценариев.

Примеры кода

Уровни изоляции и аномалии

-- Демонстрация non-repeatable read при READ COMMITTED
-- Транзакция A (READ COMMITTED — дефолт):
BEGIN;
SELECT balance FROM accounts WHERE id = 1;
-- Результат: 1000

-- В это время Транзакция B:
-- UPDATE accounts SET balance = 500 WHERE id = 1; COMMIT;

SELECT balance FROM accounts WHERE id = 1;
-- Результат: 500 (другое значение!) — non-repeatable read
COMMIT;

-- Решение: REPEATABLE READ — один snapshot на всю транзакцию
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1;
-- Результат: 1000

-- Транзакция B изменяет и коммитит...

SELECT balance FROM accounts WHERE id = 1;
-- Результат: 1000 (тот же snapshot) — non-repeatable read не возможен
COMMIT;

-- Безопасный перевод денег с оптимистичной логикой
-- Паттерн: SELECT FOR UPDATE чтобы заблокировать строки для изменения
BEGIN;
SELECT balance FROM accounts WHERE id IN (1, 2)
ORDER BY id  -- ВАЖНО: всегда один порядок блокировки, иначе deadlock!
FOR UPDATE;

-- Проверяем достаточность средств
-- Выполняем перевод
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;
COMMIT;

FOR UPDATE SKIP LOCKED: очередь задач без дедлоков

-- Таблица очереди задач
CREATE TABLE jobs (
  id          BIGSERIAL PRIMARY KEY,
  type        TEXT NOT NULL,
  payload     JSONB,
  status      TEXT DEFAULT 'pending' CHECK (status IN ('pending', 'processing', 'done', 'failed')),
  created_at  TIMESTAMPTZ DEFAULT NOW(),
  started_at  TIMESTAMPTZ,
  attempts    INT DEFAULT 0
);

-- Воркер атомарно берёт задачу (atomic dequeue)
BEGIN;

WITH next_job AS (
  SELECT id
  FROM jobs
  WHERE status = 'pending'
    AND attempts < 3
  ORDER BY created_at ASC
  LIMIT 1
  FOR UPDATE SKIP LOCKED  -- пропускаем уже заблокированные строки
)
UPDATE jobs
SET
  status     = 'processing',
  started_at = NOW(),
  attempts   = attempts + 1
WHERE id = (SELECT id FROM next_job)
RETURNING id, type, payload;

-- Если возвращено NULL — нет доступных задач
-- Обрабатываем задачу...
-- При успехе:
UPDATE jobs SET status = 'done' WHERE id = $job_id;
COMMIT;

-- При ошибке:
UPDATE jobs SET status = 'failed' WHERE id = $job_id;
COMMIT;

-- Мониторинг очереди
SELECT
  status,
  COUNT(*),
  MIN(created_at) AS oldest,
  AVG(EXTRACT(EPOCH FROM (NOW() - created_at))) AS avg_age_sec
FROM jobs
GROUP BY status;

Advisory locks и диагностика блокировок

-- Advisory lock: распределённый мьютекс без таблицы
-- Полезно для 'только один процесс должен делать X'
SELECT pg_try_advisory_lock(12345);
-- Возвращает TRUE если блокировка получена, FALSE если уже занята

-- Паттерн: try-or-skip для cron-задач
DO $$
BEGIN
  IF pg_try_advisory_lock(42) THEN
    -- Выполняем работу: обновление статистики, отправка email и т.д.
    PERFORM expensive_operation();
    PERFORM pg_advisory_unlock(42);
  ELSE
    RAISE NOTICE 'Another process is running, skipping';
  END IF;
END $$;

-- Транзакционный advisory lock: освобождается автоматически при COMMIT/ROLLBACK
SELECT pg_try_advisory_xact_lock(99);

-- Диагностика: кто кого блокирует?
SELECT
  blocked.pid                                AS blocked_pid,
  blocked.query                              AS blocked_query,
  blocking.pid                               AS blocking_pid,
  blocking.query                             AS blocking_query,
  now() - blocked.query_start               AS blocked_duration,
  blocked.wait_event_type,
  blocked.wait_event
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking
  ON blocking.pid = ANY(
    SELECT pid FROM pg_locks l
    WHERE NOT l.granted
      AND l.pid = blocked.pid
  )
WHERE NOT blocked.granted
  AND blocked.pid != pg_backend_pid();

-- Найти idle in transaction (опасные долгоживущие транзакции)
SELECT
  pid,
  usename,
  now() - xact_start AS transaction_age,
  state,
  left(query, 80) AS last_query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND xact_start < NOW() - INTERVAL '30 seconds'
ORDER BY transaction_age DESC;

Что происходит под капотом

• MVCC внутри: каждая строка (tuple) в heap файле содержит t_xmin (xid транзакции-создателя) и t_xmax (xid транзакции, пометившей строку как удалённую). UPDATE создаёт новую версию строки с новым t_xmin и помечает старую через t_xmax. Visibility check при каждом SELECT: строка видима если t_xmin <= snapshot_xmin AND (t_xmax = 0 OR t_xmax > snapshot_xmin).
• Table bloat: UPDATE/DELETE не удаляют данные физически — они создают мёртвые версии строк (dead tuples). VACUUM проходит по таблице и помечает пространство dead tuples как доступное для повторного использования (не возвращает в OS). VACUUM FULL дефрагментирует таблицу но блокирует её полностью.
• Dead tuples не убираются пока есть хотя бы одна транзакция, чей snapshot старше t_xmax мёртвых строк. Поэтому долгая транзакция (idle in transaction) блокирует VACUUM для всей базы данных, не только для таблиц, которые она использует.
• Row-level locking: FOR UPDATE добавляет запись в pg_locks. При конфликте второй запрос ждёт. FOR NO KEY UPDATE — слабее FOR UPDATE, не блокирует FOR KEY SHARE (полезно при FK constraints). FOR SHARE — блокирует изменения но позволяет другим FOR SHARE.
• Deadlock detection: PostgreSQL периодически (каждые deadlock_timeout мс, дефолт 1s) проверяет граф ожидания блокировок. При обнаружении цикла откатывает транзакцию с ERROR: deadlock detected. Приложение должно обрабатывать эту ошибку и retry.
• SERIALIZABLE использует SSI (Serializable Snapshot Isolation): отслеживает зависимости чтение-запись между транзакциями через predicate locks. При обнаружении потенциального конфликта откатывает одну транзакцию с ERROR: could not serialize access due to concurrent update. Нужна retry-логика.
• Advisory locks хранятся в общей памяти PostgreSQL (shared memory). Сессионные advisory locks освобождаются при завершении сессии или явном pg_advisory_unlock. Транзакционные (pg_advisory_xact_lock) — при COMMIT/ROLLBACK. Ключ — int8 или два int4 — можно кодировать тип операции в старших битах.
• XID wraparound — критическая проблема: transaction ID (32-bit) может переполниться через ~2 миллиарда транзакций. PostgreSQL запускает aggressive autovacuum при приближении к порогу. Мониторь: SELECT age(datfrozenxid) FROM pg_database — опасно если > 1.5 миллиарда.

Термины урока

Мини-разбор реальной ситуации

Email-рассылка: таблица email_jobs, 5 воркеров. Первая реализация без правильного лока: SELECT WHERE status = 'pending' LIMIT 1, затем UPDATE status = 'processing'. При высокой нагрузке одно письмо отправляется 3-4 раза — воркеры берут одну строку до UPDATE. Вторая попытка: BEGIN; SELECT FOR UPDATE; UPDATE; — дедлоки при попытке взять следующую задачу. Финальное решение: WITH next AS (SELECT id FROM email_jobs WHERE status = 'pending' ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED) UPDATE email_jobs SET status = 'processing' WHERE id = (SELECT id FROM next) RETURNING id. Нулевые дедлоки, нулевые дубликаты, пять воркеров параллельно обрабатывают пять разных задач.