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

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

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

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

По шагам это будет выглядеть так:

Убедиться, что остаток на счете клиента больше 3000 рублей.
Вычесть 3000 рублей со счета клиента.
Добавить 3000 к счету интернет-магазина.

Команды входящие в транзакцию:

Команда START TRANSACTION начинает транзакцию.

Убеждаемся, что на счету пользователя достаточно средств.

Снимаем средства со счета пользователя.

Перемещаем денежные средства на счет интернет-магазина.

Чтобы изменения вступили в силу, мы должны выполнить команду COMMIT.

Транзакция целиком:

Основные концепции транзакции ACID

Atomicity, Consistency, Isolation, Durability (Атомарность, Согласованность, Изолированность, Стойкость, или долговечность).

Atomicy — атомарност

Атомарность гарантирует, что никакая транзакция не будет зафиксирована в системе частично. Когда транзакции атомарны, не существует такого понятия, как частично выполненная транзакция. Атомарность гарантирует, что будут либо выполнены все подоперации транзакции, либо не выполнено ни одной.

Consistency — согласованность

Согласованность означает, что любая завершённая транзакция (транзакция, которая достигла завершения транзакции – end of transaction) фиксирует только допустимые результаты. При выполнении принципа согласованности база данных должна всегда переходить из одного непротиворечивого состояния в другое непротиворечивое состояние. Другими словами, каждая успешная транзакция по определению фиксирует только допустимые результаты. Это условие является необходимым для поддержки четвёртого свойства.

Isolation — изолированность

Изолированность - транзакция должна быть изолирована от других, т.е. её результат не должен зависеть от выполнения других параллельных транзакций. Изолированность - требование дорогое, поэтому в реальных БД существуют режимы, не полностью изолирующие транзакцию (уровни изолированности).

Durability — сохраняемость

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

Управление транзакциями

Для управления транзакциями используются следующие команды:

COMMIT - сохраняет изменения;
ROLLBACK - откатывает (отменяет) изменения;
SAVEPOINT - создаёт точку к которой группа транзакций может откатиться;
SET TRANSACTION - размещает имя транзакции.

Команды управление транзакциями используются только для DML команд: INSERT, UPDATE, DELETE.

Rollback

ROLLBACK (от roll back — англ. откатывать, возвращаться) — оператор языка SQL, который применяется для того, чтобы:

отменить все изменения, внесённые начиная с момента начала транзакции или с какой-то точки сохранения (SAVEPOINT);
очистить все точки сохранения данной транзакции;
завершить транзакцию;
освободить все блокировки данной транзакции.

При выполнении транзакции мы выясняем, что не можем завершить транзакцию, например, пользователь ее отменяет или происходит еще что-то. Чтобы ее отметить мы воспользовались командой ROLLBACK.

Для некоторых операторов нельзя выполнить откат при помощи оператора ROLLBACK. К их числу относят следующие команды:

CREATE INDEX
DROP INDEX
CREATE TABLE
DROP TABLE
TRUNCATE TABLE
ALTER TABLE
RENAME TABLE
CREATE DATABASE
DROP DATABASE
ALTER DATABASE

Не помещайте их в транзакции с другими операторами.

Кроме того, существует ряд операторов, которые неявно завершают транзакцию, как если бы был вызван оператор COMMIT:

ALTER TABLE
BEGIN
CREATE INDEX
CREATE TABLE
CREATE DATABASE
DROP DATABASE
DROP INDEX
DROP TABLE
DROP DATABASE
LOAD MASTER DATA
LOCK TABLES
RENAME
SET AUTOCOMMIT=1
START TRANSACTION
TRUNCATE TABLE

SAVEPOINT и ROLLBACK TO SAVEPOINT

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

SAVEPOINT - создает точку сохранения;
ROLLBACK TO SAVEPOINT - позволяет откатиться к одной из точек сохранения.

Несколько точек сохранения

Допускается создание нескольких точек сохранения. Если текущая транзакция имеет точку сохранения с таким же именем, старая точка удаляется и устанавливается новая. Все точки сохранения транзакций удаляются, если выполняется оператор COMMIT или ROLLBACK без указания имени точки сохранения.

Уровни изоляции

Стандарт SQL определяет четыре уровня изоляции с конкретными правилами, устанавливающими, какие изменения видны внутри и вне транзакции, а какие нет:

READ UNCOMMITTED - используется редко, поскольку его производительность не намного выше, чем у других. На этом уровне вы видите промежуточные результаты чужих транзакций, т.е. осуществляете грязное чтение.
READ COMMITTED - подразумевает, что транзакция увидит только те изменения, которые были уже зафиксированы другими транзакциями к моменту ее начала. Произведенные ею изменения останутся невидимыми для других транзакций, пока она не будет зафиксирована. На этом уровне возможен феномен невоспроизводимого чтения. Это означает, что вы можете выполнить одну и ту же команду дважды и получить различный результат.
REPEATABLE READ - этот уровень изоляции установлен по умолчанию. Он гарантирует, что любые строки, которые считываются в контексте транзакции, будут выглядеть такими же при последовательных операциях чтения в пределах одной и той же транзакции, однако теоретически на этом уровне возможен феномен фантомного чтения (phantom reads). Он возникает в случае, если вы выбираете некоторый диапазон строк, затем другая транзакция вставляет новую строку в этот диапазон, после чего вы выбираете тот же диапазон снова. В результате вы увидите новую фантомную строку.
SERIALIZABLE - самый высокий уровень изоляции, решает проблему фантомного чтения, заставляя транзакции выполняться в таком порядке, чтобы исключить возможность конфликта. Уровень SERIALIZABLE блокирует каждую строку, которую транзакция читает. На этом уровне может возникать множество задержек и конфликтов при блокировках. На практике данный уровень изоляции применяется достаточно редко.

Изменить уровень изоляции можно при помощи команды SET TRANSACTION:

Журнал транзакций

Запросить параметры журнала транзакций можно при помощи следующего запроса:

Где располагается журнал транзакций

ib_logfile0 и ib_logfile1 - файлы журнала транзакций, все транзакции сначала помещаются сюда, затем перегоняются в файлы единого табличного пространства: если сервер MySQL останавливается штатно, все транзакции из журнала сохраняются в таблицу. Если происходит сбой и сервер останавливается, например из-за отсутствия питания, перед стартом MySQL проверяет журнал транзакций и перегоняет в единое табличное пространство все транзакции которые не были сохранены в таблицах. Таким образом, потерять сохраненные транзакции невозможно.

Получить путь к каталогу, в котором размещены файлы транзакций, можно при помощи следующего запроса:

Управление режимом сохранения транзакций

За режим управления сохранения транзакций отвечает переменная innodb_flush_log_at_trx_commit, которая может принимать следующие значения:

0 — сохранение журнала раз в секунду,
1 — сохранение после каждой транзакции,
2 — сохранение журнала раз в секунду и после каждой транзакции.

Узнать текущий режим сохранения транзакций:

Установить новое значение:

Теги:

MySQL