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

Мы научились раскладывать данные по таблицам так, чтобы они не врали. Но данные не лежат без дела: их меняют, часто несколько изменений подряд, и всё это может оборваться на середине. С этого урока начинается серия о том, как СУБД держит данные целыми, когда их меняют. Первый шаг: транзакция и четыре её обещания, ACID.

Оплата курса

Студент Петя покупает курс SQL за 5000 рублей. В базе школы это два изменения:

UPDATE accounts SET balance = balance - 5000 WHERE student_id = 1;
INSERT INTO enrollments (student_id, course_id) VALUES (1, 10);

Теперь представь, что сервер выключился между двумя командами. Деньги списаны, а на курс Петя не записан. Или наоборот, если команды стоят в другом порядке: записан, но не заплатил. Обе ситуации плохие, и обе возникают не из-за ошибки в коде, а из-за того, что мир прерывается в любой момент.

Нужно, чтобы два изменения прошли вместе или не прошли вовсе. Как в банкомате: ты либо получаешь деньги и видишь списание, либо не получаешь и списания нет. Вариант «списали, но не выдали» банк обязан исключить.

Транзакция

Транзакция это группа команд, которую СУБД выполняет как одно целое. В SQL её обрамляют двумя командами:

BEGIN;
UPDATE accounts SET balance = balance - 5000 WHERE student_id = 1;
INSERT INTO enrollments (student_id, course_id) VALUES (1, 10);
COMMIT;

BEGIN открывает транзакцию. COMMIT фиксирует её: с этого момента изменения постоянны. Если что-то пошло не так, команда ROLLBACK отменяет всё, что сделано после BEGIN, как будто ничего не было.

BEGIN;
UPDATE accounts SET balance = balance - 5000 WHERE student_id = 1;
-- выяснилось, что курс уже закрыт
ROLLBACK;  -- баланс Пети снова прежний

Если сервер упадёт до COMMIT, СУБД после перезапуска поведёт себя так, будто был ROLLBACK. Это и есть главная гарантия: незафиксированная транзакция не оставляет следов.

А что, если не писать BEGIN? Тогда каждая команда сама по себе отдельная транзакция, её фиксируют сразу. Этот режим называют автофиксацией, и в нём наша оплата снова разваливается на два независимых шага.