Урок 10. Транзакции — BEGIN, COMMIT, ROLLBACK

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

Учебная таблица

Для этого урока нужна отдельная таблица счетов:

CREATE TABLE accounts (
    id      INTEGER PRIMARY KEY,
    owner   TEXT    NOT NULL,
    balance INTEGER NOT NULL CHECK (balance >= 0)
);

INSERT INTO accounts (owner, balance) VALUES ('Анна', 100), ('Борис', 50);

CHECK (balance >= 0) не даст счёту уйти в минус.

BEGIN и COMMIT

Анна переводит Борису 30 рублей:

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

SELECT * FROM accounts;

BEGIN открывает транзакцию. Изменения после него видны в этом же подключении, но в базу окончательно ещё не записаны. COMMIT фиксирует их все разом. Если до COMMIT программа упадёт или пропадёт питание, после перезапуска база окажется в состоянии до BEGIN — как будто перевода не было. Половины перевода не будет никогда.

ROLLBACK — отменить

ROLLBACK отменяет всё, что сделано после BEGIN. Проверьте:

BEGIN;
DELETE FROM accounts;
SELECT COUNT(*) FROM accounts;

ROLLBACK;
SELECT COUNT(*) FROM accounts;

Счета вернулись. Поэтому изменения рабочей базы удобно делать так: BEGIN, изменение, проверочный SELECT, и только потом COMMIT — или ROLLBACK, если что-то пошло не так.

Ошибка внутри транзакции

Теперь Борис пытается перевести Анне 200 рублей, которых у него нет. Порядок команд неудачный: сначала зачисление, потом списание.

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

Вторая команда нарушает CHECK и не выполняется: CHECK constraint failed: balance >= 0. Но первая-то уже прошла:

SELECT * FROM accounts;

Транзакция всё ещё открыта, и в ней у Анны 270 рублей. Ошибка одной команды в SQLite и MySQL отменяет только эту команду, а не всю транзакцию. Решение принимаете вы — здесь нужен откат:

ROLLBACK;
SELECT * FROM accounts;

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

Автофиксация

А что было во всех прошлых уроках, без BEGIN? Каждая команда сама по себе была транзакцией и фиксировалась сразу после выполнения. Это называется автофиксацией (autocommit), и так по умолчанию работают sqlite3, mysql и psql.

Программы ведут себя по-разному:

• DB Browser for SQLite держит изменения в открытой транзакции до нажатия «Write Changes»; «Revert Changes» — это ROLLBACK.
• Python (модуль sqlite3) сам открывает транзакцию перед INSERT, UPDATE и DELETE, а фиксирует только по con.commit(). Забытый commit() — частая причина вопроса «почему данные не сохранились».
• MySQL Workbench и другие программы для баз могут работать в любом режиме — смотрите их настройки.

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

Гарантии транзакций обозначают словом ACID:

• Atomicity (атомарность) — все команды или ни одной;
• Consistency (согласованность) — после транзакции соблюдены все ограничения: ключи, CHECK, NOT NULL;
• Isolation (изолированность) — другие подключения не видят незафиксированных изменений;
• Durability (долговечность) — после COMMIT данные сохранены и переживут сбой.

Отличия СУБД

В MySQL транзакции работают только с таблицами InnoDB — это тип таблиц по умолчанию. Старые таблицы MyISAM транзакций не поддерживают: ROLLBACK для них ничего не вернёт.

Точки сохранения

Внутри транзакции можно поставить метку и откатиться только до неё:

BEGIN;
UPDATE accounts SET balance = balance - 10 WHERE id = 1;
SAVEPOINT before_bonus;
UPDATE accounts SET balance = balance + 1000 WHERE id = 2;
ROLLBACK TO before_bonus;
COMMIT;

Из двух изменений зафиксируется первое: второе отменено откатом к метке before_bonus. Точки сохранения есть в SQLite, MySQL и PostgreSQL.

Транзакции и блокировки

Пока транзакция с изменениями открыта, другие подключения не могут менять те же данные (в SQLite — вообще ничего в этом файле): они ждут или получают ошибку. Поэтому транзакции делают короткими: открыли, изменили, зафиксировали. Не оставляйте транзакцию открытой, пока ждёте ответа пользователя. Типичная ошибка SQLite в такой ситуации — database is locked, о ней справка Ошибка database is locked.

Попытка открыть транзакцию внутри открытой даёт в SQLite cannot start a transaction within a transaction, а COMMIT без BEGIN — cannot commit - no transaction is active.

Коротко

• Транзакция — группа команд, которые выполняются все или ни одной.
• BEGIN — начать, COMMIT — зафиксировать, ROLLBACK — отменить.
• Ошибка одной команды в SQLite и MySQL не отменяет транзакцию сама — нужен явный ROLLBACK.
• Без BEGIN каждая команда фиксируется сразу; в Python нужен con.commit(), в DB Browser — «Write Changes».
• Держите транзакции короткими: открытая транзакция мешает другим подключениям.

Проверьте себя