Транзакции и ACID в SQL простыми словами

Транзакции — один из главных вопросов на собеседованиях для разработчиков и аналитиков. Разбираем ACID без теоретической воды — только на примерах.

Что такое транзакция

Транзакция — группа операций, которые выполняются как единое целое. Либо все, либо ни одна.

BEGIN;

UPDATE accounts SET balance = balance - 1000 WHERE id = 1;  -- списать
UPDATE accounts SET balance = balance + 1000 WHERE id = 2;  -- зачислить

COMMIT;  -- применить обе операции
-- или ROLLBACK; -- отменить обе

Если между двумя UPDATE произойдёт сбой — без транзакции деньги спишутся, но не зачислятся. С транзакцией — ничего не произойдёт.

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

A — Atomicity (Атомарность)

Всё или ничего. Если хоть одна операция в транзакции провалилась — все изменения откатываются.

BEGIN;
UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
-- Здесь упала программа
-- После перезапуска БД: первый UPDATE тоже откатится

C — Consistency (Согласованность)

Транзакция переводит базу из одного согласованного состояния в другое. Не нарушает ограничения (CHECK, FOREIGN KEY, UNIQUE).

BEGIN;
INSERT INTO orders (customer_id, amount) VALUES (999, 500);
-- Если customer_id = 999 не существует и есть FOREIGN KEY — транзакция откатится
COMMIT;

I — Isolation (Изоляция)

Параллельные транзакции не мешают друг другу. Одна транзакция не видит незафиксированных изменений другой.

D — Durability (Долговечность)

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

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

Полная изоляция дорого стоит (блокировки). Поэтому SQL определяет 4 уровня с разными компромиссами.

Аномалии, от которых защищают уровни

Грязное чтение (Dirty Read): читаем незафиксированные данные другой транзакции.

Транзакция A: UPDATE balance = 500 (ещё не COMMIT)
Транзакция B: SELECT balance → видит 500
Транзакция A: ROLLBACK → баланс вернулся в прежнее
Транзакция B: работала с несуществующими данными

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

Неповторяемое чтение (Non-repeatable Read): дважды читаем одну строку — результат разный.

Транзакция B: SELECT salary WHERE id=1 → 50000
Транзакция A: UPDATE salary = 60000 WHERE id=1; COMMIT
Транзакция B: SELECT salary WHERE id=1 → 60000 (изменилось!)

Фантомное чтение (Phantom Read): повторный запрос возвращает новые строки.

Транзакция B: SELECT COUNT(*) WHERE dept='IT' → 10
Транзакция A: INSERT новый сотрудник в IT; COMMIT
Транзакция B: SELECT COUNT(*) WHERE dept='IT' → 11 (появился фантом)

Как это работает под капотом? PostgreSQL использует MVCC (Multi-Version Concurrency Control). Когда строка изменяется, создаётся её новая версия, а старая остаётся для транзакций, которые начались раньше. Именно поэтому чтение никогда не блокирует запись, а запись — чтение.

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

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

Каждый SELECT видит только зафиксированные данные. Одна транзакция может видеть разные данные при повторном чтении если между чтениями другая транзакция сделала COMMIT.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

REPEATABLE READ

Все SELECT в транзакции видят снимок данных на момент начала транзакции. Защищает от неповторяемого чтения.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

Нюанс PostgreSQL: в стандарте SQL уровень REPEATABLE READ допускает фантомное чтение. В PostgreSQL благодаря snapshot isolation вы не увидите фантомов в рамках одного запроса — но это не то же самое, что SERIALIZABLE. Уровень всё равно допускает аномалии сериализации (например, потерянное обновление при конкурентной вставке). Не путайте поведение PostgreSQL с MySQL, где эти уровни реализованы иначе.

SERIALIZABLE

Самый строгий уровень. Транзакции выполняются так, будто строго последовательно. Может вызывать ошибки сериализации — приложение должно их обрабатывать.

BEGIN ISOLATION LEVEL SERIALIZABLE;

При конкурентных изменениях PostgreSQL может вернуть ERROR: could not serialize access due to concurrent update. Это не баг — это защита. Приложение должно поймать эту ошибку и перезапустить транзакцию. Это плата за максимальную согласованность.

Savepoints: частичный откат

BEGIN;
INSERT INTO orders VALUES (1, 100);

SAVEPOINT my_save;

INSERT INTO orders VALUES (2, 200);

ROLLBACK TO my_save;  -- отменяем только второй INSERT

COMMIT;  -- первый INSERT сохранится

Блокировки

Для изоляции транзакции используют блокировки:

-- Явная блокировка строк
SELECT * FROM orders WHERE id = 1 FOR UPDATE;
-- Другие транзакции не смогут изменить эту строку до COMMIT

-- Блокировка для чтения (другие могут читать, но не писать)
SELECT * FROM orders WHERE id = 1 FOR SHARE;

-- Не ждать освобождения — сразу вернуть ошибку если строка занята
SELECT * FROM orders WHERE id = 1 FOR UPDATE NOWAIT;

NOWAIT полезен, когда ожидание неприемлемо: лучше сразу сообщить об ошибке и дать пользователю попробовать позже, чем держать соединение в подвешенном состоянии.

Deadlock — взаимная блокировка. PostgreSQL её обнаруживает и откатывает одну из транзакций.

Транзакция A: блокирует строку 1, ждёт строку 2
Транзакция B: блокирует строку 2, ждёт строку 1
→ PostgreSQL: ERROR: deadlock detected

Что спрашивают на собеседовании

Что такое ACID? — Atomicity, Consistency, Isolation, Durability. Транзакция — либо всё, либо ничего; не нарушает целостность; изолирована от других; результат сохраняется.

Какой уровень изоляции по умолчанию в PostgreSQL? — READ COMMITTED.

В чём разница между ROLLBACK и ROLLBACK TO SAVEPOINT? — ROLLBACK отменяет всю транзакцию, ROLLBACK TO SAVEPOINT — только до точки сохранения.

Что такое deadlock? — взаимная блокировка двух транзакций, каждая из которых ждёт ресурс, заблокированный другой.