Транзакции в InnoDB

В прошлом уроке каждая команда жила сама по себе: выполнилась и сразу осталась в базе. Но зритель, который меняет место в зале, ждёт двух действий сразу: старый билет вернули, новый продали. Если сервер упадёт между ними, зритель останется либо без билета, либо с двумя. Сегодня разбираем, как InnoDB склеивает несколько команд в одно целое и где эта склейка неожиданно рвётся.

Обмен на кассе

Вера пришла на кассу поменять место на сеансе «Москва слезам не верит». Кассир делает две вещи: гасит билет A1 и печатает билет B1. Если принтер зажевал новый билет, кассир не оставит Веру ни с чем: он отменит возврат и отдаст старый билет обратно. Обмен либо случился целиком, либо его не было.

Транзакция устроена так же. START TRANSACTION открывает её, COMMIT делает изменения постоянными, ROLLBACK отменяет всё, что успели сделать внутри.

START TRANSACTION;
UPDATE tickets SET status = 'refunded' WHERE id = 5;
INSERT INTO tickets (screening_id, viewer_id, seat, status, sold_at) VALUES (3, 3, 'B1', 'paid', NOW());
SELECT id, seat, status FROM tickets WHERE screening_id = 3;

+----+------+----------+
| id | seat | status   |
+----+------+----------+
|  5 | A1   | refunded |
|  6 | A2   | paid     |
| 12 | B1   | paid     |
|  7 | D4   | refunded |
+----+------+----------+
4 rows in set (0.00 sec)

Внутри транзакции ты уже видишь свои изменения. Строки идут по местам, а не по номерам: InnoDB прочитал их по уникальному ключу (screening_id, seat). Без ORDER BY порядок никто не обещает, это мы помним по уроку про SELECT.

COMMIT;

Query OK, 0 rows affected (0.00 sec)

Теперь обмен виден всем, и после перезапуска сервера он никуда не денется.

ROLLBACK: передумали

Обратный случай. Администратор по ошибке удаляет все билеты сеанса 3 и успевает заметить это до COMMIT:

START TRANSACTION;
DELETE FROM tickets WHERE screening_id = 3;
SELECT COUNT(*) FROM tickets WHERE screening_id = 3;

Query OK, 4 rows affected (0.00 sec)

+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

ROLLBACK;
SELECT COUNT(*) FROM tickets WHERE screening_id = 3;

Query OK, 0 rows affected (0.00 sec)

+----------+
| COUNT(*) |
+----------+
|        4 |
+----------+
1 row in set (0.00 sec)

Четыре билета вернулись. Откат работает только в InnoDB: в уроке про движки мы видели, что MyISAM на ROLLBACK отвечает предупреждением 1196 и оставляет строки как есть.

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

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

SELECT @@autocommit, @@transaction_isolation;

+--------------+-------------------------+
| @@autocommit | @@transaction_isolation |
+--------------+-------------------------+
|            1 | REPEATABLE-READ         |
+--------------+-------------------------+
1 row in set (0.00 sec)

Отсюда частая ошибка в коде: возврат и продажа отправлены двумя командами без транзакции. Приложение упало между ними, и в базе остался возврат без продажи.

Автофиксацию можно выключить для своей сессии. Тогда транзакция открывается сама с первой же командой и ждёт твоего COMMIT или ROLLBACK:

SET autocommit = 0;
UPDATE halls SET seats = 150 WHERE id = 1;
ROLLBACK;
SELECT seats FROM halls WHERE id = 1;

+-------+
| seats |
+-------+
|   120 |
+-------+
1 row in set (0.00 sec)

Расширение Большого зала до 150 мест откатилось. Это хорошая страховка, когда ты руками правишь рабочую базу. Опасность в обратном: забыл COMMIT, закрыл клиент, и все правки за час пропали.

SAVEPOINT: откатить часть

Иногда нужно отменить только хвост транзакции. Для этого ставят точку сохранения, как сохранение в игре перед трудным уровнем:

START TRANSACTION;
INSERT INTO tickets (screening_id, viewer_id, seat, sold_at) VALUES (5, 1, 'B2', NOW());
SAVEPOINT after_first;
INSERT INTO tickets (screening_id, viewer_id, seat, sold_at) VALUES (5, 1, 'B3', NOW());
ROLLBACK TO SAVEPOINT after_first;
COMMIT;
SELECT id, screening_id, seat FROM tickets WHERE screening_id = 5;

+----+--------------+------+
| id | screening_id | seat |
+----+--------------+------+
| 10 |            5 | B1   |
| 13 |            5 | B2   |
+----+--------------+------+
2 rows in set (0.00 sec)

Билет B2 остался, B3 исчез. Если ошибиться в имени точки, сервер так и скажет:

ROLLBACK TO SAVEPOINT nosuch;

ERROR 1305 (42000): SAVEPOINT nosuch does not exist

Номера не возвращаются

Посмотри ещё раз на номера: B2 получил 13, а следующий билет B4, который продали уже после транзакции, получил 15:

INSERT INTO tickets (screening_id, viewer_id, seat, sold_at) VALUES (5, 2, 'B4', NOW());
SELECT MAX(id) FROM tickets;

+---------+
| MAX(id) |
+---------+
|      15 |
+---------+
1 row in set (0.00 sec)

Номер 14 достался отменённому B3. ROLLBACK вернул строку, но не номер: счётчик AUTO_INCREMENT живёт вне транзакций, иначе параллельным сессиям пришлось бы ждать друг друга за каждым номером. Про дырки в нумерации мы говорили в уроке про AUTO_INCREMENT. Вывод тот же: номер это ярлык строки, а не счётчик продаж.

Одна команда тоже атомарна

Что будет, если внутри транзакции одна команда упадёт? Здесь MySQL ведёт себя иначе, чем PostgreSQL. В PostgreSQL ошибка ломает всю транзакцию. В InnoDB откатывается только упавшая команда, а транзакция живёт дальше.

Зато сама команда откатывается целиком, даже если она вставляет много строк. Продаём Борису два места на «Сталкер», и второе уже занято:

INSERT INTO tickets (screening_id, viewer_id, seat, sold_at) VALUES (5, 2, 'B5', NOW()), (5, 2, 'B1', NOW());

ERROR 1062 (23000): Duplicate entry '5-B1' for key 'tickets.uq_ticket_seat'

SELECT id, seat FROM tickets WHERE screening_id = 5;

+----+------+
| id | seat |
+----+------+
| 10 | B1   |
| 13 | B2   |
| 15 | B4   |
+----+------+
3 rows in set (0.00 sec)

Места B5 нет, хотя первая строка была правильной. Это атомарность оператора: одна команда никогда не оставляет половину своей работы.

Но в транзакции из нескольких команд решать придётся тебе. Если вторая команда упала, первая всё ещё внутри транзакции. Приложение ловит ошибку и само говорит ROLLBACK, иначе следующий COMMIT зафиксирует первую половину.

Неявная фиксация

Самая коварная ловушка MySQL. Открываем транзакцию, уменьшаем Малый зал до одного места и создаём таблицу:

START TRANSACTION;
UPDATE halls SET seats = 1 WHERE id = 2;
CREATE TABLE tx_probe (id INT);
ROLLBACK;
SELECT seats FROM halls WHERE id = 2;

+-------+
| seats |
+-------+
|     1 |
+-------+
1 row in set (0.00 sec)

ROLLBACK ответил Query OK, но в зале остался один стул. Команды, которые меняют схему (CREATE, ALTER, DROP, TRUNCATE), вызывают неявную фиксацию: сервер сам делает COMMIT перед ними. Таблица тоже осталась:

SHOW TABLES LIKE 'tx%';

+----------------------+
| Tables_in_kino (tx%) |
+----------------------+
| tx_probe             |
+----------------------+
1 row in set (0.00 sec)

В PostgreSQL создание таблицы можно откатить, в MySQL нельзя. Поэтому миграцию схемы в MySQL не заворачивают в одну транзакцию с данными: упавшая на середине миграция оставит схему наполовину изменённой. Ещё один источник неявной фиксации: START TRANSACTION посреди открытой транзакции. Он сначала зафиксирует старую, потом откроет новую.

ACID одним абзацем

Четыре свойства хорошей транзакции называют ACID. Атомарность: всё или ничего, это мы и проверяли весь урок. Согласованность: после транзакции соблюдены все правила таблиц. Изоляция: параллельные транзакции не мешают друг другу. Долговечность: после COMMIT изменения переживут падение сервера, InnoDB для этого пишет журнал на диск. Подробно и с примерами каждое свойство разобрано в уроке теории.

Что видят соседи

В выводе выше было REPEATABLE-READ. Это уровень изоляции InnoDB по умолчанию, и он отличается от PostgreSQL.

Представь две сессии. Сессия А открыла транзакцию и меняет цену сеанса, но ещё не сделала COMMIT. Сессия Б читает этот сеанс и видит старую цену: чужие незафиксированные правки не видны никому. Если Б попробует изменить ту же строку, её UPDATE будет ждать, пока А не закончит. А при REPEATABLE READ есть ещё одна особенность: если Б уже читала эту строку в своей транзакции, она и после COMMIT сессии А продолжит видеть старую цену в обычных SELECT, будто смотрит на снимок базы, сделанный при первом чтении.

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

Что запомнить

• Транзакция собирает несколько изменений в одно: COMMIT сохраняет всё, ROLLBACK отменяет всё.
• В режиме autocommit каждая команда сама себе транзакция.
• SAVEPOINT позволяет откатить часть транзакции, не теряя сделанного до точки.
• Одна команда атомарна сама по себе: если вторая строка вставки упала, первой тоже не будет.
• CREATE TABLE и другие команды DDL незаметно фиксируют открытую транзакцию.

Попробуй сам

В песочнице учебная база кинотеатра. Запрос удаляет билеты сеанса внутри транзакции, откатывает удаление, а потом пробует продать два места одной командой, одно из которых занято.

Замени ROLLBACK на COMMIT и посмотри, сколько билетов останется. Потом добавь в транзакцию CREATE TABLE после DELETE и убедись, что откат больше не спасает. Напоследок поставь SAVEPOINT между двумя вставками и откати только вторую.

Проверь себя

Задание

Дальше

Данные мы меняем безопасно. Теперь сделаем так, чтобы запросы не тормозили, когда билетов станет миллион. Следующий урок про индексы и про то, как спросить MySQL, каким путём он собирается выполнить запрос.