Урок 8.4 · Время чтения: ~10 мин

Транзакция в SQL — это группа операторов, которую база данных выполняет как единое целое: либо срабатывают все, либо ни один. В этом уроке вы разберёте START TRANSACTION, COMMIT и ROLLBACK, узнаете, как SAVEPOINT отменяет часть транзакции, что происходит при ошибке и от каких проблем защищают уровни изоляции, когда несколько пользователей меняют одни и те же данные.

Транзакции в SQL: COMMIT, ROLLBACK и уровни изоляции

В предыдущих уроках вы меняли данные операторами INSERT, UPDATE и DELETE, по одному за раз. Реальные операции обычно требуют нескольких операторов: выдать фильм в прокат в Sakila — значит добавить строку в rental и строку в payment. Если второй оператор не выполнится, первый не должен остаться сам по себе.

Учебные базы на SQLtest доступны только для чтения, поэтому там эти операторы вернут ошибку прав доступа. Примеры используют схему Sakila; они проверены на MySQL 8.0 и PostgreSQL 17.

Что такое транзакция?

Транзакция превращает несколько изменений в одну операцию. База данных гарантирует четыре свойства ACID, с которыми вы познакомились в уроке 1.3:

• Атомарность (Atomicity): срабатывают все операторы транзакции или ни один.
• Согласованность (Consistency): после транзакции все ограничения по-прежнему выполняются.
• Изолированность (Isolation): параллельные транзакции не видят незавершённых изменений друг друга.
• Долговечность (Durability): зафиксированные изменения переживают сбой или отключение питания.

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

START TRANSACTION, COMMIT и ROLLBACK

START TRANSACTION;

-- statements that change data

COMMIT;    -- save all changes
-- or
ROLLBACK;  -- undo all changes

• START TRANSACTION (или BEGIN) открывает транзакцию.
• COMMIT делает все её изменения постоянными и видимыми другим пользователям.
• ROLLBACK отменяет все изменения, сделанные с начала транзакции.

Ключевые слова в разных СУБД немного различаются:

Пример: выдать фильм и принять оплату

START TRANSACTION;

INSERT INTO rental (rental_date, inventory_id, customer_id, staff_id)
VALUES ('2005-08-24 10:00:00', 1, 1, 1);

INSERT INTO payment (customer_id, staff_id, rental_id, amount, payment_date)
VALUES (1, 1, LAST_INSERT_ID(), 0.99, '2005-08-24 10:00:00');

COMMIT;

Результат: обе строки сохраняются вместе. LAST_INSERT_ID() в MySQL возвращает только что сгенерированный rental_id; в PostgreSQL для этого используется INSERT ... RETURNING rental_id.

Пример: отменить ошибку

START TRANSACTION;

UPDATE film
SET rental_rate = rental_rate * 2;

SELECT SUM(rental_rate) FROM film;  -- the new prices, visible inside the transaction

ROLLBACK;

Результат: после ROLLBACK все цены возвращаются к прежним. До COMMIT другие пользователи удвоенных цен не видят (кроме уровня READ UNCOMMITTED, о котором ниже).

Автокоммит: каждый оператор — отдельная транзакция

Если вы не открыли транзакцию, база данных всё равно её использует: каждый оператор выполняется в собственной транзакции и фиксируется автоматически. Этот режим называется автокоммит (autocommit), и в MySQL, PostgreSQL и SQL Server он включён по умолчанию.

SELECT @@autocommit;  -- MySQL: 1 means autocommit is on

Поэтому одиночный UPDATE без START TRANSACTION нельзя отменить через ROLLBACK: он зафиксирован в момент завершения. START TRANSACTION приостанавливает автокоммит до COMMIT или ROLLBACK.

SAVEPOINT: отменить часть транзакции

Точка сохранения (savepoint) отмечает место внутри транзакции. ROLLBACK TO SAVEPOINT отменяет только изменения, сделанные после неё; транзакция остаётся открытой.

START TRANSACTION;

UPDATE film SET rental_rate = 1.99 WHERE film_id = 1;

SAVEPOINT before_second;

UPDATE film SET rental_rate = 9.99 WHERE film_id = 2;

ROLLBACK TO SAVEPOINT before_second;

COMMIT;

Результат: фильм 1 получает новую цену 1.99; изменение фильма 2 отменено, и он сохраняет прежнюю цену.

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

Что происходит, когда оператор завершается ошибкой

Здесь СУБД ведут себя по-разному, и это частый источник ошибок.

MySQL и MariaDB отменяют только неудачный оператор. Транзакция остаётся открытой, и COMMIT сохраняет всё, что выполнилось успешно:

START TRANSACTION;

INSERT INTO payment (customer_id, staff_id, amount, payment_date)
VALUES (1, 1, 2.99, '2005-08-24 10:00:00');   -- succeeds

INSERT INTO payment (customer_id, staff_id, amount, payment_date)
VALUES (999, 1, 4.99, '2005-08-24 10:00:00'); -- fails: customer 999 does not exist

COMMIT;

Результат в MySQL: вторая вставка падает с ошибкой внешнего ключа, но COMMIT всё равно сохраняет первый платёж. Чтобы получить «всё или ничего», приложение должно само проверять ошибки и выполнять ROLLBACK.

PostgreSQL после ошибки помечает всю транзакцию как неудавшуюся. Каждый следующий оператор отклоняется с сообщением «current transaction is aborted», а COMMIT фактически выполняет откат: ничего не сохраняется.

Операторы, которые фиксируют транзакцию неявно

В MySQL, MariaDB и Oracle DDL-операторы, такие как CREATE TABLE, ALTER TABLE и DROP TABLE, перед выполнением фиксируют текущую транзакцию. Последующий ROLLBACK не отменит изменения, сделанные до них:

START TRANSACTION;

UPDATE film SET rental_rate = 5.99 WHERE film_id = 1;

CREATE TABLE film_backup (film_id INT);  -- implicit COMMIT here

ROLLBACK;  -- too late: the UPDATE is already saved

Результат в MySQL: новая цена 5.99 остаётся. PostgreSQL и SQL Server выполняют DDL внутри транзакции, поэтому там ROLLBACK отменяет и UPDATE, и CREATE TABLE.

Проблемы параллельной работы: грязное, неповторяющееся и фантомное чтение

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

Грязное чтение по шагам:

Уровни изоляции транзакций

Уровень изоляции определяет, от каких из этих аномалий защищена транзакция. Чем сильнее изоляция, тем меньше сюрпризов, но тем больше транзакции ждут друг друга.

Уровень по умолчанию зависит от СУБД:

• MySQL и MariaDB (InnoDB): REPEATABLE READ. Обычный SELECT читает снимок данных, сделанный при первом чтении в транзакции, поэтому на практике фантомов он тоже не видит.
• PostgreSQL, SQL Server, Oracle: READ COMMITTED. PostgreSQL выполняет READ UNCOMMITTED как READ COMMITTED, поэтому грязного чтения там не бывает; Oracle поддерживает только READ COMMITTED и SERIALIZABLE.

Как изменить уровень:

-- for the next transaction only
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- for all following transactions of this connection (MySQL)
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

В PostgreSQL SET TRANSACTION ISOLATION LEVEL ... пишется сразу после BEGIN. На уровне SERIALIZABLE база данных может отменить одну из двух конфликтующих транзакций с ошибкой сериализации; тогда приложение должно её повторить.

Практические правила

• Делайте транзакции короткими: пока транзакция открыта, она держит блокировки, которых ждут другие пользователи. Никогда не ждите ввода пользователя внутри транзакции.
• Проверяйте результат каждого оператора и выполняйте ROLLBACK при ошибке, особенно в MySQL, который не сделает этого за вас.
• Не смешивайте DDL с транзакциями над данными в MySQL: он неявно фиксирует транзакцию.
• Две транзакции, которые блокируют одни и те же строки в обратном порядке, могут заблокировать друг друга: это взаимоблокировка (deadlock). База данных отменяет одну из них, и приложение должно её повторить.

Главное из урока:

• Транзакция объединяет операторы в одно целое: COMMIT сохраняет их все, ROLLBACK отменяет их все.
• Без явной транзакции каждый оператор фиксируется сразу (автокоммит).
• SAVEPOINT и ROLLBACK TO SAVEPOINT отменяют только часть транзакции.
• После ошибки MySQL оставляет транзакцию открытой, а PostgreSQL прерывает её; в MySQL выполняйте ROLLBACK сами.
• В MySQL DDL-операторы неявно фиксируют текущую транзакцию.
• Уровни изоляции защищают от грязного, неповторяющегося и фантомного чтения; по умолчанию это REPEATABLE READ в MySQL и READ COMMITTED в PostgreSQL, SQL Server и Oracle.

Часто задаваемые вопросы

Что такое транзакция в SQL?

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

Чем COMMIT отличается от ROLLBACK?

COMMIT завершает транзакцию и делает её изменения постоянными и видимыми другим пользователям. ROLLBACK завершает транзакцию и отменяет все её изменения, возвращая данные в состояние до START TRANSACTION.

Можно ли откатить изменения после COMMIT?

Нет. После COMMIT изменения постоянны; чтобы их отменить, нужен новый оператор, который вернёт данные обратно, или резервная копия. То же относится к операторам, выполненным в режиме автокоммита.

Какой уровень изоляции транзакций используется по умолчанию?

REPEATABLE READ в MySQL и MariaDB с InnoDB; READ COMMITTED в PostgreSQL, SQL Server и Oracle.

Вопросы для собеседования

Что означает ACID для транзакции?

Атомарность: всё или ничего. Согласованность: ограничения выполняются до и после. Изолированность: параллельные транзакции не видят незафиксированных изменений друг друга. Долговечность: зафиксированные данные переживают сбои.

Что такое грязное чтение и какой уровень изоляции его исключает?

Грязное чтение — это чтение незафиксированного изменения другой транзакции, которое позже может быть откачено. Его исключают READ COMMITTED и все более сильные уровни.

Чем неповторяющееся чтение отличается от фантомного?

Неповторяющееся чтение — это изменившееся значение в строке, которую вы уже прочитали; фантомное чтение — это новая строка, которая появляется в результате повторного запроса с тем же условием.

Что происходит в MySQL, если оператор внутри транзакции завершился ошибкой?

Отменяется только этот оператор; транзакция остаётся открытой, и COMMIT сохраняет успешно выполненные операторы. Чтобы вся операция была атомарной, приложение должно перехватить ошибку и выполнить ROLLBACK.