15.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements — MySQL 8.4

15.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. См. Раздел 17.7.2.3, «Согласованные непротиворечивые чтения». Модификатор WITH CONSISTENT SNAPSHOT не изменяет текущую транзакцию, поэтому он предоставляет согласованную моментальную фотографию только в том случае, если текущий уровень изоляции допускает согласованное чтение. Единственный уровень изоляции, который допускает согласованное чтение, это REPEATABLE READ. Для всех остальных уровней изоляции фраза WITH CONSISTENT SNAPSHOT игнорируется. При игнорировании фразы WITH CONSISTENT SNAPSHOT генерируется предупреждение.
Модификаторы READ WRITE и READ ONLY устанавливают режим доступа к транзакции. Они разрешают или запрещают изменения в таблицах, используемых в транзакции. Ограничение READ ONLY предотвращает изменение или блокировку как транзакционных, так и не транзакционных таблиц, видимых другими транзакциями; транзакция все еще может изменять или блокировать временные таблицы.

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

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

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

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

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

Важно

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

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

SET autocommit=0;

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

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

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

Выражение BEGIN отличается от использования ключевого слова BEGIN, которое начинает BEGIN ... END составное выражение. Последнее не начинает транзакцию. См. Раздел 15.6.1, «BEGIN ... END составное выражение».

Примечание

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

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

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

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

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

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

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

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

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

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

Примечание

В MySQL 8.4, BEGIN, COMMIT, и ROLLBACK не затрагиваются правилами --replicate-do-db или --replicate-ignore-db.

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

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