Транзакции и ACID в SQL: шпаргалка

Зачем аналитику знать транзакции

На собесе аналитика это редкий блок, но на middle+ и позициях «DA + DE» спрашивают. Также полезно понимать последствия непоследовательных данных в дашбордах.

ACID — 4 свойства транзакции

Atomicity — атомарность

Транзакция выполняется вся или никак. Если в середине ошибка — rollback.

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

Если второй UPDATE упадёт — первый откатится автоматически.

Consistency — консистентность

После транзакции данные должны быть в валидном состоянии согласно constraints (FK, UNIQUE, CHECK). Если нарушение — rollback.

Isolation — изоляция

Параллельные транзакции не должны мешать друг другу. Уровни — ниже.

Durability — долговечность

После COMMIT данные сохранены (даже при отказе питания). Обеспечивается WAL (Write-Ahead Log).

Синтаксис транзакций

-- Стандарт
BEGIN;
...
COMMIT;

-- Или ROLLBACK в случае ошибки
BEGIN;
...
ROLLBACK;

-- Savepoint — точка возврата внутри транзакции
BEGIN;
INSERT INTO t VALUES (1);
SAVEPOINT s1;
INSERT INTO t VALUES (2);
ROLLBACK TO s1;  -- откатываем до s1, но продолжаем транзакцию
COMMIT;

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

От слабого к сильному:

READ UNCOMMITTED

Можно видеть незакоммиченные изменения других транзакций (dirty reads).

Редкий уровень. В PostgreSQL его нет — всегда как READ COMMITTED.

READ COMMITTED (по умолчанию в PostgreSQL)

Видит только закоммиченные данные. Но в одной транзакции при двух чтениях можно получить разные результаты (non-repeatable read).

REPEATABLE READ

Видит snapshot на начало транзакции. Все чтения возвращают тот же результат.

В PostgreSQL реализовано через MVCC.

SERIALIZABLE

Максимум изоляции — как если бы транзакции выполнялись по очереди. Самый медленный.

BEGIN ISOLATION LEVEL SERIALIZABLE;
...
COMMIT;

Попробовать силы на подобных вопросах проще всего в тренажёре Карьерник — прямо в Telegram, без регистрации через сайт.

Аномалии многопоточного доступа

Dirty Read

Транзакция A читает незакоммиченные данные B.

Non-Repeatable Read

A читает row X, B его меняет + коммитит, A читает опять — получает другое значение.

Phantom Read

A читает «всех пользователей Москвы» (10 строк), B вставляет нового + коммитит, A читает снова — 11 строк.

Lost Update

A читает значение, B читает его же, оба обновляют — изменения A теряются.

Таблица уровней

Уровень	Dirty	Non-repeatable	Phantom
READ UNCOMMITTED	возможна	возможна	возможна
READ COMMITTED	нет	возможна	возможна
REPEATABLE READ	нет	нет	возможна
SERIALIZABLE	нет	нет	нет

Locks (блокировки)

Row-level locks

При UPDATE автоматически блокирует строку до конца транзакции:

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- row id=1 залочена до COMMIT

SELECT FOR UPDATE

Явная блокировка строк при SELECT:

BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- теперь никто не может обновить или удалить эту строку
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

Нужно, когда вы читаете, вычисляете, потом обновляете — надо предотвратить параллельные изменения.

Advisory locks

Логические блокировки, не привязанные к строкам:

SELECT pg_advisory_lock(123);  -- захватить лок с id=123
-- ... работаем ...
SELECT pg_advisory_unlock(123);

Используются для application-level координации.

Deadlock

Когда две транзакции взаимно ждут друг друга:

T1: UPDATE a; UPDATE b;
T2: UPDATE b; UPDATE a;

PostgreSQL это детектирует и делает ROLLBACK одной из транзакций. Решения:

• Всегда обновлять в одном порядке.
• Использовать SELECT FOR UPDATE для захвата всего заранее.

Transactional в аналитических запросах

Для SELECT транзакции обычно не используются (нет данных для изменения). Но они полезны для:

• Consistent snapshots для отчёта: открыть REPEATABLE READ транзакцию и делать несколько SELECT с одной точки времени.
• Long-running queries: если нужно, чтобы они видели данные на момент старта.

Пройти 30–50 задач по теме за вечер можно в Telegram-тренажёре. Это то, что отличает «знаю» от «уверенно отвечу на собесе».

10 вопросов с собесов

1. Что такое ACID?

Atomicity, Consistency, Isolation, Durability.

2. Дефолтный уровень в PostgreSQL?

READ COMMITTED.

3. Что такое dirty read?

Чтение незакоммиченных данных. В PostgreSQL невозможно (нет READ UNCOMMITTED).

4. Разница REPEATABLE READ и SERIALIZABLE?

REPEATABLE READ защищает от phantom reads частично (в PostgreSQL через snapshot). SERIALIZABLE — полностью.

5. Когда использовать SELECT FOR UPDATE?

Когда читаете значение, собираетесь его использовать для UPDATE, и не хотите, чтобы другая транзакция его изменила между.

6. Что такое WAL?

Write-Ahead Log — журнал, куда пишутся изменения до их применения. Обеспечивает Durability: после рестарта БД восстанавливается из WAL.

7. Savepoint — зачем?

Частичный rollback внутри транзакции. Если вложенная операция упала — откат только до savepoint, остальное продолжается.

8. Deadlock — как разрешается?

PostgreSQL детектирует через цикл в зависимостях и делает ROLLBACK одной транзакции. Приложение должно retry.

9. MVCC — что это?

Multi-Version Concurrency Control. Каждая транзакция видит snapshot, данные не блокируются при чтении. PostgreSQL использует MVCC вместо lock-based изоляции.

10. Как сделать запрос «вне транзакции»?

В PostgreSQL каждый SELECT без BEGIN — это implicit транзакция. Нельзя «совсем без транзакции». Но можно использовать AUTOCOMMIT и короткие запросы.

---

Как тренироваться

Транзакции — теория с практикой. Откройте psql, прогоните BEGIN/UPDATE/ROLLBACK несколько раз, почитайте pg_locks во время этого — и всё прояснится.

Тренажёр Карьерник содержит вопросы по транзакциям и изоляции для senior-позиций.

Читайте также

• Транзакции SQL: гайд
• Индексы SQL: шпаргалка
• EXPLAIN SQL: шпаргалка
• Нормализация БД

FAQ

Нужно ли знать ACID аналитику?

На junior — нет. На middle — да, базово. На senior — глубоко, включая уровни изоляции и locks. Для DA/DE hybrid ролей — обязательно.

Когда использовать SERIALIZABLE?

Только когда действительно нужна полная изоляция — денежные переводы, критические операции. Для аналитики почти никогда.

Почему PostgreSQL не имеет READ UNCOMMITTED?

Из-за MVCC архитектуры — все транзакции видят консистентные snapshots, «грязных» данных просто нет в подходящей форме. PostgreSQL маппит READ UNCOMMITTED → READ COMMITTED.

Что такое 2PC (Two-Phase Commit)?

Протокол для распределённых транзакций (несколько БД). В аналитике почти не используется — слишком сложный. Для локальной БД достаточно стандартных транзакций.