Транзакции и MVCC на собеседовании Data Engineer

Зачем спрашивают на собесе DE

DE отвечает за надёжность пайплайна: вставил батч, упал, перезапустил — что произошло с данными? Без понимания транзакций и изоляции пайплайн ломает прод-таблицы тихо, а вы об этом узнаёте через неделю от аналитика.

На собесе обязательно: «расскажи про ACID», «что такое phantom read», «зачем нужен MVCC». Senior-уровень — «как реализован Repeatable Read в Postgres и почему это не Serializable».

ACID коротко

• A — Atomicity. Всё или ничего: либо вся транзакция применилась, либо ни одной строки.
• C — Consistency. Транзакция переводит БД из одного валидного состояния в другое (constraints, FK, чек-констрейнты).
• I — Isolation. Параллельные транзакции не «видят» друг друга, как будто они выполняются последовательно. Степень — настраиваемая, см. уровни.
• D — Durability. После COMMIT данные сохранены, даже если сервер упадёт через секунду. Реализуется через WAL (write-ahead log).

В DE-контексте чаще всего болит Isolation — на ETL-пайплайнах с параллельной записью.

Аномалии

Стандарт SQL описывает 4 аномалии, которые могут возникать при параллельных транзакциях:

• Dirty read — T1 читает данные, которые T2 ещё не закоммитила. Если T2 откатится — T1 видела «грязное» значение.
• Non-repeatable read — T1 читает строку, T2 апдейтит и коммитит, T1 читает снова — другое значение.
• Phantom read — T1 делает SELECT WHERE x > 10, T2 вставляет новую строку с x = 15, T1 повторяет SELECT — появилась «фантомная» строка.
• Lost update — T1 и T2 читают одно значение, обе апдейтят — апдейт одной из них теряется.

Postgres дополнительно имеет serialization anomaly — результат серии транзакций не эквивалентен ни одному порядку их последовательного выполнения.

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

Стандарт SQL-92:

Важно для DE: в Postgres Read Uncommitted фактически не существует — он работает как Read Committed. И Postgres Repeatable Read не пускает phantom read (за счёт snapshot isolation), хотя стандарт это разрешает.

-- задать уровень изоляции на транзакцию
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- ваши SELECT/UPDATE
COMMIT;

В пайплайне дефолт Read Committed обычно ОК. Если делаешь длинную аналитическую транзакцию с несколькими SELECT — Repeatable Read, чтобы не ловить расхождения.

MVCC в Postgres

MVCC (Multi-Version Concurrency Control) — как Postgres реализует изоляцию без блокировок на чтение.

Идея: каждая строка существует в нескольких версиях. У каждой версии есть xmin (транзакция, создавшая) и xmax (транзакция, удалившая/обновившая). Транзакция видит только те версии, которые «существовали» на момент её snapshot.

до UPDATE:    [user_id=1, name='Anna', xmin=100, xmax=null]

после UPDATE: [user_id=1, name='Anna', xmin=100, xmax=200]   -- старая
              [user_id=1, name='Bob',  xmin=200, xmax=null]  -- новая

Что отсюда следует:

• Reader не блокирует writer и наоборот — снапшот разрешает конфликт.
• UPDATE физически — это DELETE + INSERT, поэтому блоат таблицы реален.
• Старые версии чистит VACUUM. Без него таблица растёт, индексы пухнут, запросы тормозят.
• Long-running transaction блокирует уборку (нельзя удалить версию, которую кто-то ещё видит). Aborted transaction — та же проблема.

В ClickHouse и Greenplum — другие модели. CH вообще без полноценных транзакций (есть лёгкие на батч), GP — с MVCC, но distributed-транзакции дороже.

Deadlocks

T1 захватила row A, ждёт row B. T2 захватила row B, ждёт row A. Цикл — deadlock. БД детектит и убивает одну из транзакций (ERROR: deadlock detected).

-- T1
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- T2 в этот момент:
-- UPDATE accounts SET balance = balance - 50 WHERE id = 2;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;  -- ждёт T2
-- T2 пробует UPDATE id=1 — deadlock

Лечение:

• Захватывать строки в одинаковом порядке во всех транзакциях (по id ASC например).
• Сократить время удержания блокировок: тяжёлые вычисления — до BEGIN.
• SELECT FOR UPDATE для явного локирования при чтении.
• Ретраи на стороне приложения (deadlock — нормальная ошибка, не баг).

Что важно для пайплайнов DE

Идемпотентность важнее изоляции. ETL может перезапуститься. Лучше MERGE/INSERT ... ON CONFLICT с натуральным ключом, чем «надежда на изоляцию».

Long-running transaction = блокировка vacuum. В DWH важно: не делайте транзакцию на 4 часа, иначе мёртвые версии копятся, IO растёт.

Bulk load обычно отдельная транзакция. COPY в Postgres — атомарно, но в одной транзакции на гигабайты. Лучше батчами по 100k-1M строк.

Сериализация по партициям, не по таблице. Если пишете в партиционированную таблицу — параллельные writers в разные партиции не блокируют друг друга на уровне строк, но advisory lock'и иногда нужны.

Auto-commit в Python. psycopg2 по умолчанию открывает транзакцию на каждый execute. Для bulk insert поставь autocommit = True или явно управляй commit() — иначе случайно держишь транзакцию открытой между батчами.

Частые ошибки

Сказать «Postgres Read Uncommitted разрешает dirty read». Не разрешает — фактически работает как Read Committed.

Думать, что SERIALIZABLE блокирует. В Postgres это SSI (Serializable Snapshot Isolation) — без блокировок, но с откатом «несериализуемых» транзакций. Готовь приложение к ретраям.

Считать MVCC бесплатным. Bloat реален — VACUUM нужен, autovacuum может не справиться на high-write таблицах.

UPDATE без WHERE без BEGIN. Auto-commit включён — таблица улетит. Перед опасными запросами BEGIN; ... ROLLBACK; для проверки.

Long transaction в пайплайне. Открыли транзакцию, запустили Spark на 2 часа, забыли закоммитить — autovacuum простаивает, дисковое пространство идёт в минус.

Использовать transactional Kafka producer без понимания. Транзакционность Kafka != транзакции БД, это другая семантика (atomic publish across partitions).

Уровень изоляции на сессию вместо транзакции. SET SESSION ISOLATION LEVEL зацепит все следующие транзакции — лучше SET TRANSACTION точечно.

Связанные темы

• Идемпотентность пайплайна для DE
• SQL для Data Engineer: собеседование
• ACID и уровни изоляции на собесе SA
• Greenplum на собесе DE
• Подготовка к собесу Data Engineer

FAQ

Зачем DE знать MVCC, если он не пишет приложение?

Длинный SELECT в DWH блокирует cleanup мёртвых версий, таблица пухнет — это твой пайплайн сломался по производительности через неделю. Понимание MVCC помогает дебажить такие штуки.

READ UNCOMMITTED существует в Postgres?

Синтаксически команда есть, но Postgres всегда работает минимум как READ COMMITTED — dirty read невозможен.

Как Postgres реализует Serializable без блокировок?

Через SSI — Serializable Snapshot Isolation. Транзакция работает на снапшоте, но при коммите Postgres проверяет, не возникло ли конфликта чтения-записи; если возник — откат с serialization_failure.

SELECT FOR UPDATE vs LOCK TABLE?

SELECT FOR UPDATE — лочит конкретные строки на запись (другие writer'ы ждут). LOCK TABLE — лочит всю таблицу с заданным режимом, гораздо грубее, нужно реже.

Почему в ClickHouse нет полноценных транзакций?

CH оптимизирован под аналитические нагрузки на чтение, append-only вставки. Транзакции дорого сочетаются с движком MergeTree и distributed таблицами. Есть лёгкие транзакции на отдельную партицию/INSERT, но не attended транзакционность как в OLTP.

Это официальная информация?

Нет. Статья основана на стандарте SQL:1992, документации Postgres 14+, материалах по MVCC.

Тренируйте Data Engineering — откройте тренажёр с 1500+ вопросами для собесов.