Транзакции в SQL: как перевод между счетами ломается без ACID

Перевод 1000 ₽ со счёта A на счёт B — это два действия: списать деньги с одного счёта и зачислить их на другой.

UPDATE accounts SET balance = balance - 1000 WHERE id = 'A';
UPDATE accounts SET balance = balance + 1000 WHERE id = 'B';

Если между этими двумя запросами приложение упадёт, сеть оборвётся или сервер перезагрузится, деньги спишутся с A и никогда не появятся на B. Без транзакции база данных не знает, что эти два запроса — одна операция, и не отменит первый, если второй не выполнился.

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

BEGIN, COMMIT, ROLLBACK

Транзакция открывается командой BEGIN (или START TRANSACTION) и закрывается либо COMMIT — зафиксировать изменения, либо ROLLBACK — откатить всё, что было сделано внутри неё.

BEGIN;

UPDATE accounts SET balance = balance - 1000 WHERE id = 'A';
UPDATE accounts SET balance = balance + 1000 WHERE id = 'B';

COMMIT;

Теперь, если один из UPDATE не выполнится — например, на счёте A не хватает денег и срабатывает ограничение CHECK (balance >= 0), — сама транзакция это не откатывает. В PostgreSQL после ошибки внутри BEGIN ... COMMIT транзакция переходит в состояние aborted: все последующие команды в той же сессии отклоняются с current transaction is aborted, пока явно не будет вызван ROLLBACK.

BEGIN;

UPDATE accounts SET balance = balance - 1000 WHERE id = 'A';
-- ERROR:  new row for relation "accounts" violates check constraint "accounts_balance_check"
-- (на счёте A не хватило денег)

UPDATE accounts SET balance = balance + 1000 WHERE id = 'B';
-- ERROR:  current transaction is aborted, commands ignored until end of transaction block
-- второй UPDATE даже не пытается выполниться — сессия уже в состоянии ошибки

ROLLBACK;
-- только теперь оба счёта гарантированно остаются нетронутыми

Что гарантирует ACID

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

Atomicity и durability обеспечивает сама СУБД без вашего участия. А вот isolation — то, где разработчик реально принимает решения, потому что более строгая изоляция стоит производительности.

Проблема потерянного обновления

Атомарность не спасает от гонки между двумя транзакциями. Пример: у счёта баланс 5000 ₽, и два перевода по 1000 ₽ списываются с него одновременно.

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

Уровни изоляции и какие проблемы они решают

Стандарт SQL определяет четыре уровня изоляции. Каждый следующий устраняет ещё одну аномалию ценой большей блокировки и меньшей параллельности.

* В PostgreSQL REPEATABLE READ реализован строже стандарта и исключает phantom read тоже, но полагаться на это в других СУБД (например, MySQL/InnoDB — тоже исключает, но по другому механизму) не стоит без проверки документации конкретной базы.

• Dirty read — транзакция читает данные, которые другая транзакция ещё не зафиксировала и может откатить.
• Non-repeatable read — повторное чтение той же строки в рамках одной транзакции возвращает другое значение, потому что кто-то успел его изменить и закоммитить между чтениями.
• Phantom read — повторный запрос с тем же условием выборки (диапазон, фильтр, агрегат по нему) возвращает другой набор строк или другое значение, потому что кто-то добавил или удалил подходящую строку.

По умолчанию PostgreSQL и Oracle используют READ COMMITTED, MySQL — REPEATABLE READ. Для большинства запросов этого достаточно; менять уровень стоит осознанно под конкретный сценарий:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
-- запросы, для которых критична полная изоляция
COMMIT;

Как исправить потерянное обновление без SERIALIZABLE

Поднимать изоляцию до SERIALIZABLE для всей базы — дорогое решение. Для конкретно гонки при списании достаточно заблокировать строку на время транзакции с помощью SELECT ... FOR UPDATE.

BEGIN;

SELECT balance FROM accounts WHERE id = 'A' FOR UPDATE;
-- вторая параллельная транзакция с тем же FOR UPDATE будет ждать здесь

UPDATE accounts SET balance = balance - 1000 WHERE id = 'A';

COMMIT;

Вторая транзакция не сможет прочитать строку через FOR UPDATE, пока первая не завершится COMMIT или ROLLBACK. Она будет ждать и увидит уже обновлённый баланс 4000 ₽, а не устаревшие 5000 ₽.

Взаимные блокировки

Блокировки решают гонки, но создают новый риск. Если транзакция 1 блокирует строку A и ждёт строку B, а транзакция 2 в это время блокирует B и ждёт A — каждая ждёт ресурс, который держит другая. Это дедлок.

СУБД сама обнаруживает такую циклическую блокировку и прерывает одну из транзакций с ошибкой (например, deadlock detected в PostgreSQL), чтобы вторая могла продолжить. Приложение должно уметь поймать эту ошибку и повторить транзакцию. Против конкретно этого сценария — две транзакции блокируют две строки в обратном порядке — помогает единый порядок блокировки, например, по возрастанию id. Но это не страховка от дедлоков вообще: неявные блокировки со стороны внешних ключей, уникальных индексов и триггеров этот порядок не соблюдают, а массовый UPDATE по диапазону блокирует строки в порядке, который выбирает планировщик запроса, а не обязательно по id.

Чек-лист

1. Все запросы, которые должны применяться вместе, обёрнуты в одну транзакцию?
2. При ошибке приложение явно вызывает ROLLBACK, а не оставляет транзакцию открытой?
3. Транзакция максимально короткая — без сетевых запросов или ожидания пользователя внутри неё?
4. Для операций «прочитать и изменить» используется блокировка (FOR UPDATE) или более строгий уровень изоляции?
5. Приложение перехватывает ошибку дедлока (например, deadlock detected) и повторяет транзакцию целиком, а не просто показывает пользователю ошибку?

Вывод

Транзакция нужна не «для надёжности вообще», а как ответ на конкретный сценарий: часть операции выполнилась, часть — нет, либо два запроса одновременно изменили одни и те же данные. Atomicity и durability СУБД обеспечивает сама, а вот уровень изоляции и блокировки — зона ответственности разработчика, и выбирать их стоит под реальную проблему, а не по умолчанию.

Транзакцию проверяет не то, что происходит по плану, а то, что происходит, когда что-то идёт не так.