Маша решила подарить Диме 50 бонусов. Для базы это два действия: списать 50 у Маши и начислить 50 Диме. А теперь представь, что после первого действия выключили свет. У Маши бонусы списаны, Дима их не получил, 50 бонусов исчезли. В этом уроке учимся склеивать несколько изменений в одно, которое либо проходит целиком, либо не проходит совсем. Это последний инструмент перед финальным уроком раздела.
Перевод денег в банке
Банковский перевод устроен так же: со счёта отправителя деньги уходят, на счёт получателя приходят. Банку важно, чтобы эти две записи никогда не разошлись. Поэтому операция проводится целиком. Если на середине что-то сломалось, банк отменяет и первую половину, будто перевода не было.
Транзакция в базе данных это такой перевод. Несколько команд внутри неё база считает одной операцией. Свойство «всё или ничего» называют атомарностью, это первая буква в аббревиатуре ACID. Остальные буквы разобраны в уроке про ACID.
Каждая команда уже транзакция
До этого урока мы ни разу не писали слово «транзакция», а они всё время были. SQLite оборачивает каждую команду, которая меняет данные, в отдельную транзакцию. Этот режим называют автофиксацией. UPDATE customers SET bonus = 0 обновит либо всех гостей, либо никого, даже если свет погаснет на середине.
Проблема возникает, когда атомарной должна быть не одна команда, а несколько. Вот исходное состояние:
sqlite> SELECT id, name, bonus FROM customers WHERE id IN (1, 4);
╭────┬──────┬───────╮
│ id │ name │ bonus │
╞════╪══════╪═══════╡
│ 1 │ Маша │ 120 │
│ 4 │ Дима │ 75 │
╰────┴──────┴───────╯BEGIN и COMMIT
Чтобы объединить команды, транзакцию открывают командой BEGIN и закрывают командой COMMIT:
sqlite> BEGIN;
sqlite> UPDATE customers SET bonus = bonus - 50 WHERE id = 1;
sqlite> UPDATE customers SET bonus = bonus + 50 WHERE id = 4;
sqlite> SELECT id, name, bonus FROM customers WHERE id IN (1, 4);
╭────┬──────┬───────╮
│ id │ name │ bonus │
╞════╪══════╪═══════╡
│ 1 │ Маша │ 70 │
│ 4 │ Дима │ 125 │
╰────┴──────┴───────╯
sqlite> COMMIT;Между BEGIN и COMMIT изменения видит только твоё соединение. В файл базы они попадают целиком в момент COMMIT. Если программа упадёт до него, при следующем открытии файла SQLite увидит незавершённую транзакцию и отменит её. Сумма бонусов не потеряется.
ROLLBACK
Иногда посреди транзакции понимаешь, что всё пошло не так. Например, случайно обнулил бонусы всем гостям. Команда ROLLBACK отменяет всё, что сделано после BEGIN:
sqlite> BEGIN;
sqlite> UPDATE customers SET bonus = 0;
sqlite> SELECT sum(bonus) FROM customers;
╭────────────╮
│ sum(bonus) │
╞════════════╡
│ 0 │
╰────────────╯
sqlite> ROLLBACK;
sqlite> SELECT sum(bonus) FROM customers;
╭────────────╮
│ sum(bonus) │
╞════════════╡
│ 245 │
╰────────────╯Бонусы вернулись. Это хорошая привычка для опасных UPDATE и DELETE, которые ты писал в уроке про изменение данных: открыл транзакцию, выполнил команду, проверил результат через SELECT и только потом COMMIT.
Ошибка внутри транзакции
Неочевидная деталь: ошибка одной команды не отменяет всю транзакцию. SQLite откатывает только ту команду, которая упала, а остальное ждёт твоего решения:
sqlite> BEGIN;
sqlite> UPDATE customers SET bonus = bonus - 30 WHERE id = 4;
sqlite> INSERT INTO customers (id, name) VALUES (1, 'Маша-2');
Error near line 3: UNIQUE constraint failed: customers.id
sqlite> COMMIT;
sqlite> SELECT id, name, bonus FROM customers WHERE id IN (1, 4);
╭────┬──────┬───────╮
│ id │ name │ bonus │
╞════╪══════╪═══════╡
│ 1 │ Маша │ 70 │
│ 4 │ Дима │ 95 │
╰────┴──────┴───────╯Вставка упала из-за занятого id, но списание у Димы осталось и после COMMIT записалось в базу. Если команды должны пройти только вместе, после ошибки нужно самому сказать ROLLBACK. В программах это делают в обработчике ошибок, как увидим в следующем уроке.
Незакрытая транзакция
Что будет, если открыть транзакцию и выйти из sqlite3? Удалим все позиции заказов и не напишем COMMIT:
sqlite> BEGIN;
sqlite> DELETE FROM order_items;
sqlite> SELECT count(*) FROM order_items;
╭──────────╮
│ count(*) │
╞══════════╡
│ 0 │
╰──────────╯Теперь запустим sqlite3 заново над тем же файлом:
sqlite> SELECT count(*) FROM order_items;
╭──────────╮
│ count(*) │
╞══════════╡
│ 18 │
╰──────────╯Все 18 позиций на месте. Транзакция без COMMIT при закрытии соединения откатывается. Песочница в уроках ведёт себя так же: каждый запуск это новый вызов sqlite3, и всё, что ты не зафиксировал, пропадает к следующему запуску.
Транзакции в SQLite не вкладываются друг в друга. COMMIT без BEGIN и второй BEGIN подряд дают ошибки:
sqlite> COMMIT;
Error near line 1: cannot commit - no transaction is active
sqlite> BEGIN;
sqlite> BEGIN;
Error near line 3: cannot start a transaction within a transaction
sqlite> ROLLBACK;Один писатель за раз
Вспомни первый урок раздела: в файл SQLite одновременно пишет только одно соединение. Транзакция, которая меняет данные, захватывает файл на запись, и остальные писатели ждут, пока она не закончится. Поэтому транзакции держат короткими: открыл, быстро изменил, зафиксировал.
Обычный BEGIN откладывает захват до первой записи. Иногда это приводит к неприятности: две программы начали транзакцию, обе прочитали данные, обе хотят записать, и одна получает ошибку «база занята». Команда BEGIN IMMEDIATE захватывает право на запись сразу, и вторая программа ждёт у входа, а не на середине:
sqlite> BEGIN IMMEDIATE;
sqlite> UPDATE drinks SET price = 140 WHERE id = 7;
sqlite> COMMIT;
sqlite> SELECT name, price FROM drinks WHERE id = 7;
╭────────────────┬───────╮
│ name │ price │
╞════════════════╪═══════╡
│ Чай с чабрецом │ 140 │
╰────────────────┴───────╯Журнал и WAL
Как SQLite гарантирует «всё или ничего», если свет гаснет посреди записи? Через журнал. В обычном режиме SQLite перед изменением копирует старые страницы базы в файл имя.db-journal. Если транзакция не дошла до конца, старые страницы возвращаются на место.
Второй режим называется WAL, его включают командой PRAGMA journal_mode = WAL. Новые данные сначала дописываются в отдельный файл имя.db-wal, а основной файл не трогается до контрольной точки. Главная выгода: пока один пишет, остальные спокойно читают старую версию данных и не ждут. Для сайтов и приложений с частым чтением это заметная разница. Режим запоминается в файле базы, поэтому его включают один раз. В песочнице база живёт в памяти браузера, поэтому файлов журнала там нет.
Сравнить транзакции SQLite с PostgreSQL, где писателей много одновременно, можно в уроке про транзакции PostgreSQL, а частые вопросы с собеседований собраны в уроке про ACID.
Попробуй сам
Переведём 40 бонусов от Маши Лене, а потом попробуем сломать перевод ошибкой:
Убери последний ROLLBACK и запусти ещё раз: что стало с бонусами Маши в следующем запуске?
Проверь себя
Задание
Дальше
Мы прошли весь SQL, который нужен в повседневной работе с SQLite. Остался последний шаг: собрать особенности SQLite в один список и подключиться к базе из настоящей программы. Это финальный урок раздела.
домашка