13.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements — MySQL 5.7

13.3.1 Операторы START TRANSACTION, COMMIT и ROLLBACK

START TRANSACTION
    [transaction_characteristic [, transaction_characteristic] ...]

transaction_characteristic: {
    WITH CONSISTENT SNAPSHOT
  | READ WRITE
  | READ ONLY
}

BEGIN [WORK]
COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
SET autocommit = {0 | 1}

Эти операторы обеспечивают контроль над использованием:

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

По умолчанию MySQL работает с включенным режимом. Это означает, что, если не указано иное, каждый оператор является атомарным, как если бы он был окружен START TRANSACTION и COMMIT. Вы не можете использовать ROLLBACK для отмены эффекта; однако, если при выполнении оператора произойдет ошибка, оператор будет отменён.

Чтобы отключить режим автоматического подтверждения неявным образом для одной серии операторов, используйте оператор START TRANSACTION:

START TRANSACTION;
SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
UPDATE table2 SET summary=@A WHERE type=1;
COMMIT;

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

START TRANSACTION допускает несколько модификаторов, которые контролируют характеристики транзакции. Для указания нескольких модификаторов разделяйте их запятыми.

Модификатор WITH CONSISTENT SNAPSHOT начинает для СУБД, которые способны это делать. Это относится только к InnoDB. Эффект такой же, как при выполнении START TRANSACTION, за которым следует SELECT из любой таблицы InnoDB. См. Раздел 14.7.2.3, «Согласованные непротиворечивые чтения». Модификатор WITH CONSISTENT SNAPSHOT не изменяет текущую транзакцию, поэтому он предоставляет согласованную моментальную копию только в том случае, если текущий уровень изоляции позволяет согласованное чтение. Единственным уровнем изоляции, который допускает согласованное чтение, является REPEATABLE READ. Для всех других уровней изоляции условие WITH CONSISTENT SNAPSHOT игнорируется. Начиная с MySQL 5.7.2, генерируется предупреждение, когда условие WITH CONSISTENT SNAPSHOT игнорируется.
Модификаторы READ WRITE и READ ONLY устанавливают режим доступа к транзакции. Они позволяют или запрещают изменения в таблицах, используемых в транзакции. Ограничение READ ONLY предотвращает изменение или блокировку транзакционных и нетранзакционных таблиц, видимых другим транзакциям; транзакция всё ещё может изменять или блокировать временные таблицы.

MySQL включает дополнительные оптимизации для запросов к InnoDB таблицам, когда транзакция известна как только для чтения. Указание READ ONLY гарантирует, что эти оптимизации применяются в случаях, когда статус только для чтения не может быть определён автоматически. См. Раздел 8.5.3, «Оптимизация транзакций InnoDB только для чтения» для получения дополнительной информации.

Если режим доступа не указан, применяется режим по умолчанию. Если не было изменено, это режим чтения/записи. Не допускается указывать как READ WRITE, так и READ ONLY в одном и том же операторе.

В режиме только для чтения по-прежнему возможно изменение таблиц, созданных с помощью ключевого слова TEMPORARY, с помощью операторов DML. Изменения, вносимые операторами DDL, недопустимы, как и для постоянных таблиц.

Для получения дополнительной информации о режиме доступа к транзакциям, включая способы изменения режима по умолчанию, см. Раздел 13.3.6, «Оператор SET TRANSACTION».

Если системная переменная read_only включена, явное начало транзакции с помощью START TRANSACTION READ WRITE требует привилегии SUPER.

Важно

Многие API, используемые для написания приложений MySQL-клиентов (таких как JDBC), предоставляют собственные методы для начала транзакций, которые могут (и иногда должны) использоваться вместо отправки оператора START TRANSACTION от клиента. См. Главу 27, Подключаемые модули и API, или документацию для вашего API, для получения дополнительной информации.

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

SET autocommit=0;

После отключения режима автоматического подтверждения путём установки переменной autocommit в ноль, изменения в таблицах, поддерживающих транзакции (например, для InnoDB или NDB) не делаются постоянными немедленно. Вы должны использовать COMMIT для сохранения изменений на диске или ROLLBACK для игнорирования изменений.

autocommit — это переменная сеанса и её необходимо установить для каждого сеанса. Чтобы отключить режим автоматического подтверждения для каждого нового подключения, см. описание системной переменной autocommit в Разделе 5.1.7, «Системные переменные сервера».

BEGIN и BEGIN WORK поддерживаются как псевдонимы START TRANSACTION для инициализации транзакции. START TRANSACTION — это стандартный синтаксис SQL, это рекомендуемый способ запуска транзакции ad-hoc и позволяет модификаторы, которые BEGIN не делает.

Оператор BEGIN отличается от использования ключевого слова BEGIN, которое запускает BEGIN ... END составной оператор. Последний не запускает транзакцию. См. Раздел 13.6.1, «BEGIN ... END Составной оператор».

Примечание

Во всех хранимых программах (хранимые процедуры и функции, триггеры и события) анализатор рассматривает BEGIN [WORK] как начало блока BEGIN ... END. Начните транзакцию в этом контексте с помощью START TRANSACTION вместо этого.

Необязательное ключевое слово WORK поддерживается для COMMIT и ROLLBACK, а также условия CHAIN и RELEASE. CHAIN и RELEASE могут быть использованы для дополнительного управления завершением транзакции. Значение системной переменной completion_type определяет поведение по умолчанию. См. Раздел 5.1.7, «Системные переменные сервера».

Клауза AND CHAIN заставляет новую транзакцию начинаться сразу после завершения текущей, а новая транзакция имеет тот же уровень изоляции, что и завершённая. Новая транзакция также использует тот же режим доступа (READ WRITE или READ ONLY) что и завершённая. Клауза RELEASE заставляет сервер разорвать текущее подключение клиента после завершения текущей транзакции. Включение ключевого слова NO подавляет CHAIN или RELEASE завершение, что может быть полезно, если системная переменная completion_type настроена на цепочечное или освобождающее завершение по умолчанию.

Начало транзакции приводит к подтверждению любой ожидающей транзакции. См. Раздел 13.3.3, «Операторы, вызывающие неявное подтверждение» для получения дополнительной информации.

Начало транзакции также приводит к освобождению блокировок таблиц, полученных с помощью LOCK TABLES, как будто вы выполнили UNLOCK TABLES. Начало транзакции не освобождает глобальную блокировку чтения, полученную с помощью FLUSH TABLES WITH READ LOCK.

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

Если вы используете таблицы из более чем одного хранилища данных, поддерживающего транзакции (например, InnoDB), и уровень изоляции транзакции не равен SERIALIZABLE, возможно, что при завершении одной транзакции другая активная транзакция, использующая те же таблицы, увидит только часть изменений, внесённых первой транзакцией. То есть, атомарность транзакций не гарантируется при использовании смешанных движков, и могут возникнуть несоответствия. (Если транзакции со смешанными движками встречаются редко, вы можете использовать SET TRANSACTION ISOLATION LEVEL, чтобы установить уровень изоляции на SERIALIZABLE на основе каждой транзакции по мере необходимости.)
Если вы используете таблицы, которые не поддерживают транзакции в рамках транзакции, изменения в этих таблицах сохраняются сразу, независимо от состояния режима автозапуска.
Если вы выполните оператор ROLLBACK после обновления нетранзакционной таблицы в рамках транзакции, произойдёт предупреждение. Изменения в таблицах, поддерживающих транзакции, откатываются, но не изменения в неподдерживающих транзакции таблицах.

Каждая транзакция хранится в двоичном журнале в одном блоке при COMMIT. Транзакции, которые отменены, не записываются в журнал. (Исключение: изменения в нетранзакционных таблицах нельзя отменить. Если транзакция, которая отменяется, включает изменения в нетранзакционных таблицах, вся транзакция записывается с оператором ROLLBACK в конце, чтобы убедиться, что изменения в нетранзакционных таблицах будут реплицированы.) См. Раздел 5.4.4, «Двоичный журнал».

Вы можете изменить уровень изоляции или режим доступа для транзакций с помощью оператора SET TRANSACTION. См. Раздел 13.3.6, «Оператор SET TRANSACTION».

Откат может быть медленным процессом, который может произойти неявно без явного запроса пользователя (например, при возникновении ошибки). По этой причине SHOW PROCESSLIST отображает Rolling back в столбце State для сеанса не только для явных откатов, выполненных с помощью оператора ROLLBACK, но и для неявных откатов.

Примечание

В MySQL 5.7, BEGIN, COMMIT, и ROLLBACK не затронуты правилами --replicate-do-db или --replicate-ignore-db.

Когда InnoDB выполняет полное откат транзакции, все блокировки, установленные транзакцией, освобождаются. Если один SQL-оператор в рамках транзакции отменяется в результате ошибки, например, ошибки дублирования ключа, блокировки, установленные оператором, сохраняются, а транзакция остаётся активной. Это происходит потому, что InnoDB хранит блокировки строк в формате, который не позволяет ему узнать позже, какая блокировка была установлена каким оператором.

Если оператор SELECT в рамках транзакции вызывает хранимую функцию, и оператор внутри хранимой функции завершается ошибкой, этот оператор отменяется. Если ROLLBACK выполняется для транзакции впоследствии, вся транзакция отменяется.