ACID и уровни изоляции на собеседовании системного аналитика

Зачем ACID спрашивают

Системный аналитик описывает в ТЗ, что должно происходить при параллельной работе пользователей. Два кассира одновременно списывают со счёта 1000 рублей, на счёте было 1500 — что должно случиться? «Списать дважды и уйти в минус» — это баг ТЗ, не реализации. На собесе спросят, какой уровень изоляции вы выберете и почему.

Главная боль без ACID — аналитик пишет «при оплате списываем деньги и создаём заказ» одной фразой, не указывая транзакционности. Разраб реализует двумя отдельными запросами, между ними сетевой сбой — деньги списались, заказа нет. Через два месяца саппорт находит 50 таких кейсов, фаундер задаёт вопросы о компетенции аналитика.

ACID — гарантии классических реляционных БД. Уровни изоляции — компромисс между корректностью и производительностью.

ACID: четыре свойства

Atomicity (атомарность). Транзакция выполняется целиком или не выполняется вообще. Если на середине что-то упало — откат всех изменений.

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

При падении после первого UPDATE — оба изменения откатываются.

Consistency (согласованность). Транзакция переводит БД из одного валидного состояния в другое. Все constraints (CHECK, FOREIGN KEY, UNIQUE) соблюдаются. Например, баланс не может стать отрицательным, если есть CHECK (balance >= 0).

Isolation (изоляция). Параллельные транзакции не мешают друг другу: эффект как при последовательном выполнении (теоретически). На практике — разные уровни изоляции с разными гарантиями.

Durability (долговечность). После COMMIT данные сохранены даже при отключении питания. Достигается через WAL (Write-Ahead Log) — изменения сначала пишутся в журнал, потом в data files.

Не ACID: в-памяти словарь без транзакций, кэш Redis без AOF, файловая система без fsync.

Аномалии при параллельных транзакциях

При недостаточной изоляции возникают четыре классические аномалии:

Dirty Read (грязное чтение). Транзакция T1 видит несохранённые изменения T2. Если T2 откатится — T1 принимала решения по фантомным данным.

T1: SELECT balance FROM accounts WHERE id=1; -- видит 500 (uncommitted T2)
T2: UPDATE accounts SET balance=500 WHERE id=1; -- (ROLLBACK)
T1: -- работает с 500, хотя на самом деле было 1000

Non-Repeatable Read (неповторяемое чтение). T1 читает строку дважды, между чтениями T2 коммитит UPDATE — T1 видит разные значения одной строки.

T1: SELECT balance FROM accounts WHERE id=1; -- 1000
T2: UPDATE accounts SET balance=500 WHERE id=1; COMMIT;
T1: SELECT balance FROM accounts WHERE id=1; -- уже 500

Phantom Read (фантомное чтение). T1 читает диапазон по условию, T2 INSERT в этот диапазон — T1 на повторном чтении видит новые «фантомные» строки.

T1: SELECT count(*) FROM orders WHERE status='new'; -- 5
T2: INSERT INTO orders (status) VALUES ('new'); COMMIT;
T1: SELECT count(*) FROM orders WHERE status='new'; -- 6

Lost Update (потерянное обновление). Две транзакции считывают значение, обе увеличивают, обе сохраняют — одно обновление потеряно.

T1: SELECT balance FROM accounts WHERE id=1; -- 1000
T2: SELECT balance FROM accounts WHERE id=1; -- 1000
T1: UPDATE accounts SET balance = 1000 + 100; COMMIT; -- 1100
T2: UPDATE accounts SET balance = 1000 + 200; COMMIT; -- 1200
-- факт: балансы +100 и +200 = должно быть 1300, фактически 1200

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

Стандарт SQL определяет четыре уровня (от слабого к сильному):

*В Postgres Repeatable Read уже не допускает phantom read благодаря MVCC, в стандарте SQL — допускает.

Дефолты:

• Postgres: Read Committed
• MySQL InnoDB: Repeatable Read
• Oracle: Read Committed
• SQL Server: Read Committed (с снапшотами)

Установка:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
-- ...
COMMIT;

Когда какой уровень:

• Read Uncommitted — почти никогда. Допустимо для read-only аналитики, где допустима неконсистентность.
• Read Committed — дефолт большинства OLTP. Балансы, профили, не критичные к параллельным изменениям.
• Repeatable Read / Snapshot — отчёты внутри транзакции, где важна консистентность чтений.
• Serializable — финансовые операции, где нужна корректность как при последовательном выполнении. Платится производительностью (блокировки или повторные попытки).

MVCC и snapshot isolation

MVCC (Multi-Version Concurrency Control) — подход, при котором БД хранит несколько версий строки, читатели видят свою версию (snapshot), писатели не блокируют читателей.

В Postgres каждая строка имеет служебные xmin (создавшая транзакция) и xmax (удалившая). Транзакция видит строку, если xmin ≤ её snapshot ID и xmax > snapshot ID.

Snapshot Isolation — изоляция через MVCC. Чтение даёт snapshot на момент начала транзакции (или первого SELECT). Записи проверяются на конфликты при COMMIT.

Write skew — аномалия snapshot isolation, не запрещаемая чистым SI. Две транзакции читают одно и то же, обновляют разные строки на основе прочитанного — каждая по отдельности корректна, вместе — нарушают инвариант.

Правило: всегда хотя бы один врач "on call".
T1: проверяет, что врачей on-call >= 2 (видит Anna, Boris). Снимает Anna.
T2: проверяет, что врачей on-call >= 2 (видит Anna, Boris). Снимает Boris.
Оба коммитят. Никого on-call.

Postgres SERIALIZABLE (через SSI — Serializable Snapshot Isolation) ловит write skew и кидает один из коммитов с retry.

BASE и eventual consistency

В распределённых системах ACID часто заменяется на BASE (Basically Available, Soft state, Eventual consistency).

CAP-теорема: при разделении сети (Partition) распределённая БД может выбрать только одно из (Consistency, Availability).

• CP (consistency + partition tolerance) — отказ от availability при разделении. Postgres replication (синхронная), MongoDB с majority writes.
• AP (availability + partition tolerance) — отказ от строгой consistency. Cassandra, DynamoDB (с low consistency).
• CA — только в одной ноде; не distributed.

Eventual consistency — после прекращения изменений система за конечное время сойдётся к согласованному состоянию. Все реплики увидят одно. Платится тем, что в моменте читатели могут видеть устаревшие данные.

Кейсы для BASE: социалки (лента, лайки), каталоги (карточки товара), DNS, S3.

Кейсы для строгого ACID: деньги, медицинские записи, юридические документы.

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

Не указывать уровень изоляции в ТЗ. Разраб берёт дефолт БД. Дефолт может не подходить под бизнес-логику.

Применять SERIALIZABLE «на всё». На write-heavy нагрузке — взрыв retry/блокировок. SERIALIZABLE точечно, не глобально.

Игнорировать Lost Update. Read-modify-write без блокировки = Lost Update почти всегда. Решения: SELECT ... FOR UPDATE, оптимистичный lock через version колонку и WHERE version = N, атомарный UPDATE balance = balance + 100.

Полагаться на «никто не будет одновременно». На проде «никто не будет» = «случается раз в месяц и крушит данные».

Считать, что ACID = безопасность. ACID — про корректность параллельной работы. Не про авторизацию, не про шифрование.

Путать BASE и «без транзакций». BASE — это компромисс с гарантиями. Cassandra при правильной настройке даёт строгую consistency на уровне ключа. Не «нет транзакций — пусть всё ломается».

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

• OAuth 2.1 на собесе SA
• JWT на собесе SA
• Подготовка к собесу системного аналитика
• Сoбеседование системного аналитика
• REST API на собесе SA

FAQ

Какой уровень изоляции выбрать для платежей?

Минимум Repeatable Read, лучше Serializable. Для критичных мест — SELECT ... FOR UPDATE на нужные строки в Read Committed (точечный пессимистичный lock).

Чем Serializable отличается от Repeatable Read в Postgres?

В Postgres Repeatable Read = snapshot isolation, не допускает phantom read, но допускает write skew. Serializable = SSI, ловит write skew (выкидывает одну транзакцию с serialization_failure), нужен retry в коде.

Что такое deadlock и как с ним?

Две транзакции взаимно ждут блокировок друг друга. БД обнаруживает циклическую зависимость и убивает одну — клиент получает ошибку. Лечится консистентным порядком захвата блокировок (например, всегда брать lock на меньший id первым).

MongoDB поддерживает ACID?

С 4.x — да, multi-document транзакции. До этого — атомарность только на уровне одного документа. На собесе спрашивают «когда ACID, когда BASE» — стандартный ответ: бизнес-критичные данные → ACID-СУБД, профили/каталоги/события → BASE.

Уровень изоляции на стороне приложения или БД?

На стороне БД (через SET TRANSACTION ISOLATION LEVEL). Приложение задаёт уровень при старте транзакции, БД обеспечивает гарантии.

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

Нет. Статья основана на стандарте SQL:1992, ANSI SQL изоляции и документации Postgres / MySQL / Oracle. Поведение БД на конкретном уровне может отличаться от формального стандарта.

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