Три темы убивают прод чаще всего: разработчик не понимает, что происходит внутри транзакции, не знает, почему база «встаёт», и делает миграции наугад. Эта статья закрывает все три пробела - с кодом, без воды.
Транзакции: что на самом деле происходит
Транзакция - это не просто BEGIN ... COMMIT. Это контракт: база гарантирует, что либо все изменения применятся, либо не применится ни одно.
Этот контракт описывает ACID:
- Atomicity (атомарность) - всё или ничего
- Consistency (согласованность) - база остаётся корректной до и после
- Isolation (изоляция) - параллельные транзакции не мешают друг другу
- Durability (надёжность) - после COMMIT данные сохраняются даже при падении
Самый частый вопрос новичка: «Зачем явный BEGIN? ORM же сам транзакции делает?» - Да, но неявные транзакции не дают контроля. Если ORM открывает транзакцию на каждый запрос и вы делаете 10 запросов в одной HTTP-ручке, это 10 отдельных транзакций. При ошибке на 7-м шаге первые 6 уже зафиксированы.
BEGIN;
UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
UPDATE accounts SET balance = balance + 1000 WHERE id = 2;
COMMIT;
-- При ошибке - ROLLBACK автоматически
Как MVCC убирает блокировки
PostgreSQL реализует MVCC - Multi-Version Concurrency Control, многоверсионное управление конкурентностью. Механизм простой: вместо блокировки строки при чтении база хранит несколько версий каждой строки.
Когда транзакция А обновляет строку, PostgreSQL создаёт новую версию с новыми значениями. Транзакция Б, запущенная параллельно, видит старую версию - ту, что существовала до старта Б. Читатели не ждут писателей, писатели не ждут читателей.
Каждая строка хранит системные поля xmin и xmax - идентификаторы транзакций, которые создали и удалили эту версию строки. По ним PostgreSQL определяет, какую версию показывать конкретной транзакции. Старые версии убирает фоновый процесс VACUUM.
Авторская ремарка. Когда я впервые увидел
SELECT ... FOR UPDATEв prod-коде без таймаута - транзакция висела 40 секунд и держала блокировку на 12 строках. Весь сервис деградировал. Одна строчкаSET lock_timeout = '3s'решила проблему. Об этом - в разделе про блокировки.
SAVEPOINT: откат внутри транзакции
SAVEPOINT - точка сохранения внутри транзакции. Можно откатиться к ней, не прерывая всю транзакцию.
BEGIN;
INSERT INTO orders (user_id, total) VALUES (1, 500);
SAVEPOINT before_notification;
-- Рискованная операция
INSERT INTO notifications (order_id) VALUES (currval('orders_id_seq'));
-- Что-то пошло не так
ROLLBACK TO SAVEPOINT before_notification;
-- Заказ создан, уведомление - нет
COMMIT;
Это полезно при сложной бизнес-логике, где часть операций - некритична.
Подробнее о SAVEPOINT в официальной документации.
Уровни изоляции: выбираем правильный
PostgreSQL поддерживает четыре уровня изоляции транзакций, определённых стандартом SQL. На практике используют три - READ UNCOMMITTED ведёт себя как READ COMMITTED.
READ COMMITTED - по умолчанию
READ COMMITTED - дефолтный уровень PostgreSQL. Каждый запрос внутри транзакции видит только уже зафиксированные данные. Защищает от грязного чтения (dirty read), но не от неповторяемого (non-repeatable read).
Пример проблемы: транзакция читает баланс - 1000 ₽. Параллельная транзакция списывает 500 ₽ и делает COMMIT. Первая снова читает баланс - уже 500 ₽. Это и есть неповторяемое чтение.
Для большинства CRUD-операций READ COMMITTED - правильный выбор. Он даёт хорошую производительность и достаточную защиту.
REPEATABLE READ и SERIALIZABLE
REPEATABLE READ фиксирует снимок данных на момент старта транзакции. Все последующие чтения внутри транзакции видят одно и то же состояние. Защищает от dirty read и non-repeatable read.
SERIALIZABLE - самый строгий уровень. PostgreSQL реализует его через Serializable Snapshot Isolation (SSI), а не через блокировки. Это означает, что транзакции выполняются так, будто идут последовательно. При конфликте PostgreSQL прерывает одну транзакцию с ошибкой ERROR 40001 (serialization failure) - её нужно повторить.
-- Установить уровень изоляции для текущей транзакции
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1;
-- Никакая другая транзакция не изменит этот баланс до COMMIT
COMMIT;
Таблица аномалий по уровням
| Уровень изоляции | Dirty read | Non-repeatable read | Phantom read |
|---|---|---|---|
| READ COMMITTED | ✅ защита | ❌ возможна | ❌ возможен |
| REPEATABLE READ | ✅ защита | ✅ защита | ✅ защита* |
| SERIALIZABLE | ✅ защита | ✅ защита | ✅ защита |
*В PostgreSQL REPEATABLE READ через MVCC также предотвращает фантомное чтение - это отличие от стандарта SQL, где защита от phantom гарантирована только на SERIALIZABLE.
Подробнее о таблице аномалий в официальной документации postgrespro.ru.
Блокировки: диагностика и устранение
Блокировки - не зло. Зло - это блокировки без таймаута и без мониторинга.
Типы блокировок: строка vs таблица
Строковые блокировки захватываются командами SELECT ... FOR UPDATE и SELECT ... FOR SHARE. Они блокируют только конкретные строки.
-- Читаем и сразу блокируем строку для обновления
SELECT id, balance FROM accounts WHERE id = 1 FOR UPDATE;
-- Другая транзакция будет ждать, пока мы не сделаем COMMIT/ROLLBACK
Табличные блокировки возникают при DDL-командах. AccessExclusiveLock - самый тяжёлый: блокирует любой доступ к таблице. Именно его создаёт ALTER TABLE, обычный CREATE INDEX, DROP TABLE.
Advisory locks - рекомендательные блокировки на уровне приложения. Полезны для распределённых очередей и заданий.
-- Захватить advisory lock с ключом 42
SELECT pg_advisory_lock(42);
-- ... работа ...
SELECT pg_advisory_unlock(42);
Как возникает deadlock и как его избежать
Deadlock (взаимоблокировка) возникает, когда транзакция А ждёт ресурс, который держит транзакция Б, а Б ждёт ресурс, который держит А. PostgreSQL обнаруживает тупик автоматически и откатывает одну транзакцию.
Классический сценарий:
-- Транзакция А:
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1; -- блокирует строку 1
-- ... пауза ...
UPDATE accounts SET balance = balance + 100 WHERE id = 2; -- ждёт строку 2
-- Транзакция Б (параллельно):
BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE id = 2; -- блокирует строку 2
UPDATE accounts SET balance = balance + 50 WHERE id = 1; -- ждёт строку 1 → DEADLOCK
Решение: всегда захватывать строки в одном порядке - сначала строку с меньшим id. Или использовать SELECT ... FOR UPDATE заранее, чтобы сразу захватить все нужные строки.
Дополнительная защита - таймауты:
SET lock_timeout = '3s'; -- прервать, если ждём блокировку дольше 3 сек
SET statement_timeout = '30s'; -- прервать запрос, если он длится дольше 30 сек
Диагностика: pg_locks и pg_stat_activity
Когда база «тормозит» - первым делом смотри активные блокировки:
SELECT
pid,
now() - pg_stat_activity.query_start AS duration,
query,
state
FROM pg_stat_activity
WHERE state != 'idle'
AND (now() - pg_stat_activity.query_start) > interval '5 seconds';
Чтобы найти блокировщика:
SELECT
blocked_locks.pid AS blocked_pid,
blocked_activity.query AS blocked_query,
blocking_locks.pid AS blocking_pid,
blocking_activity.query AS blocking_query
FROM pg_catalog.pg_locks AS blocked_locks
JOIN pg_catalog.pg_stat_activity AS blocked_activity
ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks AS blocking_locks
ON blocking_locks.locktype = blocked_locks.locktype
AND blocking_locks.pid != blocked_locks.pid
AND blocking_locks.granted
JOIN pg_catalog.pg_stat_activity AS blocking_activity
ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;
Состояние idle in transaction в pg_stat_activity - сигнал тревоги. Транзакция открыта, но ничего не делает. Она держит блокировки и мешает VACUUM убирать мёртвые версии строк.
Скрипты диагностики блокировок - platformv.sbertech.ru.
Миграции без аварий на проде
Миграция на проде - это хирургия на работающем сердце. Ошибка стоит дорого.
Идемпотентность и down-скрипты
Каждая миграция должна нормально выполняться повторно. Это идемпотентность:
-- Хорошо
CREATE TABLE IF NOT EXISTS users (
id BIGSERIAL PRIMARY KEY,
email TEXT NOT NULL
);
-- Плохо - упадёт при повторном запуске
CREATE TABLE users (...);
Down-скрипт - это миграция отката. Без него нет возможности быстро откатить релиз при инциденте.
-- up
ALTER TABLE orders ADD COLUMN status TEXT DEFAULT 'new';
-- down
ALTER TABLE orders DROP COLUMN status;
Хорошая практика от Habr/Beget: миграция не должна выполняться дольше 3–5 секунд даже без блокировок. Всё, что дольше - выносить в отдельный фоновый скрипт.
Операции, блокирующие таблицу
Вот что блокирует таблицу AccessExclusiveLock и не должно быть в обычной миграции на загруженном проде:
| Операция | Блокирует? | Альтернатива |
|---|---|---|
CREATE INDEX |
✅ да | CREATE INDEX CONCURRENTLY |
DROP INDEX |
✅ да | DROP INDEX CONCURRENTLY |
ALTER TABLE ADD COLUMN NOT NULL без DEFAULT |
✅ до PG 11 | Добавить с DEFAULT, потом NOT NULL |
ALTER COLUMN TYPE |
✅ да | Expand-Contract паттерн |
ADD CONSTRAINT ... VALIDATE |
✅ да | ADD CONSTRAINT ... NOT VALID, потом VALIDATE отдельно |
CREATE INDEX CONCURRENTLY не требует AccessExclusiveLock, но его нельзя запускать внутри транзакции - это ещё одна ловушка:
-- НЕЛЬЗЯ:
BEGIN;
CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id); -- Ошибка!
COMMIT;
-- МОЖНО (вне транзакции):
CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id);
Expand-Contract: миграция без даунтайма
Expand-Contract - паттерн для изменения схемы без остановки сервиса. Состоит из трёх фаз:
- Фаза 1 - Expand (расширяем): добавляем новый столбец, триггер, деплоим код.
- Фаза 2 - Migrate (переносим данные): батчами копируем данные.
- Фаза 3 - Contract (сжимаем): переключаем код, убираем триггер, удаляем старый столбец.
Пример батчевого обновления:
DO $$
DECLARE
last_id BIGINT := 0;
BEGIN
LOOP
UPDATE orders
SET status_new = status
WHERE id IN (
SELECT id FROM orders WHERE status_new IS NULL AND id > last_id
ORDER BY id LIMIT 1000
)
RETURNING MAX(id) INTO last_id;
EXIT WHEN NOT FOUND OR last_id IS NULL;
PERFORM pg_sleep(0.01); -- небольшая пауза, чтобы не давить реплики
END LOOP;
END $$;
Авторская ремарка. Expand-Contract - единственный способ изменить тип столбца на таблице в 200 млн строк без окна обслуживания. Да, это дольше. Но альтернатива - 30-минутный даунтайм в пятницу вечером.
Инструменты миграций: что выбрать под свой стек
| Инструмент | Язык/Экосистема | Форматы | Особенность |
|---|---|---|---|
| Flyway | JVM (Java, Kotlin, Scala) | SQL, Java | Простой, файловый, популярен в Spring |
| Liquibase | JVM | XML, YAML, JSON, SQL | Гибкий, поддерживает rollback из коробки |
| Alembic | Python (SQLAlchemy) | Python, SQL | Автогенерация по изменениям модели |
| golang-migrate | Go | SQL | CLI + библиотека, минималистичный |
Для Python-бэкенда с SQLAlchemy - Alembic. Для Go - golang-migrate. Для Django - встроенные миграции с флагом --fake для CONCURRENTLY-операций.
Чеклист перед деплоем:
- Миграция идемпотентна (IF NOT EXISTS / проверки)
- Написан down-скрипт и протестирован
- Нет DDL-операций без CONCURRENTLY на больших таблицах
CREATE INDEX CONCURRENTLY- вне транзакции- Долгие операции разбиты на батчи с
LIMIT - Старый код работает после применения миграции (совместимость схемы)
- Проверен план запросов
EXPLAIN ANALYZEдля новых запросов
Шпаргалка по миграциям PostgreSQL - Habr/Beget.
SKIP LOCKED: очередь задач без блокировок
Бонус для тех, кто делает очереди на PostgreSQL. SKIP LOCKED - команда, которая пропускает уже заблокированные строки вместо ожидания:
-- Воркер забирает одну задачу из очереди, не мешая другим воркерам
SELECT id, payload
FROM jobs
WHERE status = 'pending'
ORDER BY created_at
FOR UPDATE SKIP LOCKED
LIMIT 1;
Это стандартный паттерн для конкурентных воркеров без внешней очереди (Redis, Kafka). Работает начиная с PostgreSQL 9.5.
Документация: SELECT FOR UPDATE SKIP LOCKED - postgrespro.ru.
FAQ
Какой уровень изоляции используется в PostgreSQL по умолчанию?
READ COMMITTED. Защищает от грязного чтения, но не от неповторяемого. Подходит для большинства CRUD-операций.
Что такое MVCC и зачем он нужен?
MVCC (Multi-Version Concurrency Control) - механизм хранения нескольких версий строк. Читатели видят снимок данных на момент своей транзакции и не блокируют писателей. PostgreSQL реализует MVCC через системные поля xmin/xmax в каждой строке.
Как избежать deadlock в PostgreSQL?
Всегда захватывать строки в одном порядке (например, по возрастанию id). Дополнительно - устанавливать lock_timeout, чтобы транзакция не висела бесконечно.
Можно ли создать индекс без блокировки таблицы?
Да, через CREATE INDEX CONCURRENTLY. Он не блокирует чтение и запись, но выполняется дольше. Важно: нельзя запускать внутри BEGIN/COMMIT.
Что такое Expand-Contract и когда применять?
Трёхфазный паттерн для изменения схемы без даунтайма: добавить новый столбец → перенести данные батчами → удалить старый. Необходим при изменении типа столбца на больших таблицах в production.
Чем Flyway отличается от Liquibase?
Flyway проще: SQL-файлы, минималистичный API, популярен в Spring. Liquibase гибче: XML/YAML/JSON форматы, rollback из коробки. Оба поддерживают PostgreSQL. Для Python - Alembic, для Go - golang-migrate.
Почему idle in transaction опасен?
Открытая, но бездействующая транзакция держит блокировки и мешает VACUUM убирать мёртвые версии строк. Это приводит к раздуванию таблиц (table bloat). Решение - statement_timeout и явные COMMIT/ROLLBACK при любом исходе.
Источники
Beget / Habr - «Шпаргалка по работе с PostgreSQL для бэкенд-разработчиков» - https://habr.com/ru/companies/beget/articles/920772/
Postgres Pro / postgrespro.ru - Документация PostgreSQL 16: «Введение в MVCC» - https://postgrespro.ru/docs/postgresql/9.6/mvcc-intro.html
Postgres Pro / postgrespro.ru - Документация PostgreSQL: «Изоляция транзакций» - https://postgrespro.ru/docs/postgresql/16/transaction-iso.html
proselyte.net - «Что backend-разработчик должен знать про PostgreSQL» - https://proselyte.net/postgres-for-devs/
Habr / Haulmont - «Инструментарий для рефакторинга баз данных: Flyway и Liquibase» - https://habr.com/ru/companies/haulmont/articles/440696/
.svg.webp){kind=logo}
