УСТАНОВКА ТРАНЗАКЦИИ

Синтаксис

SET [GLOBAL | SESSION] TRANSACTION
    transaction_property [, transaction_property] ...

transaction_property:
    ISOLATION LEVEL level
  | READ WRITE
  | READ ONLY

level:
     REPEATABLE READ
   | READ COMMITTED
   | READ UNCOMMITTED
   | SERIALIZABLE

Описание

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

• С ключевым словом GLOBAL, команда устанавливает глобальный значение уровня транзакции по умолчанию для всех последующих сессий. Существующие сессии не затрагиваются.
• С ключевым словом SESSION, команда устанавливает значение уровня транзакции по умолчанию для всех последующих транзакций, выполняемых в текущей сессии.
• Без ключевых слов SESSION или GLOBAL, команда устанавливает уровень изоляции только для следующей (ещё не начатой) транзакции, выполняемой в текущей сессии. После этого она возвращается к использованию значения сессии.

Изменение глобального значения уровня изоляции требует привилегии SUPER. Любая сессия может свободно изменить свой уровень изоляции сессии (даже в середине транзакции) или уровень изоляции своей следующей транзакции.

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

Для установки глобального значения уровня изоляции по умолчанию при запуске сервера используйте опцию --transaction-isolation=level в командной строке или в файле опций. Значения уровня для этой опции используют дефисы вместо пробелов, поэтому допустимые значения — READ_UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ или SERIALIZABLE. Например, чтобы установить значение уровня изоляции по умолчанию в REPEATABLE READ, используйте эти строки в разделе [mariadb] файла опций:

[mariadb]
transaction-isolation = REPEATABLE-READ

Чтобы определить глобальный и сессионный уровни изоляции транзакции во время выполнения, проверьте значение системной переменной tx_isolation (обратите внимание, что переменная была переименована в transaction_isolation с MariaDB 11.1.1, чтобы соответствовать опции, а старое имя устарело).

SELECT @@GLOBAL.tx_isolation, @@tx_isolation;

С MariaDB 11.1.1:

SELECT @@GLOBAL.transaction_isolation, @@transaction_isolation;

InnoDB поддерживает каждый из уровней изоляции транзакций, описанных здесь, используя различные стратегии блокировки. Уровень по умолчанию — REPEATABLE READ. Для получения дополнительной информации о блокировках записей InnoDB и о том, как он использует их для выполнения различных типов операторов, см. Режимы блокировки InnoDB и http://dev.mysql.com/doc/refman/en/innodb-locks-set.html.

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

В следующих разделах описывается, как MariaDB поддерживает различные уровни транзакций.

READ UNCOMMITTED

SELECT операторы выполняются без блокировок, но может быть использована потенциально более ранняя версия строки. Таким образом, используя этот уровень изоляции, такие чтения не согласованы. Это также называется "грязным чтением". В противном случае, этот уровень изоляции работает так же, как READ COMMITTED.

READ COMMITTED

Уровень изоляции, похожий на Oracle в отношении согласованных (без блокировок) чтений: каждое согласованное чтение, даже в рамках одной транзакции, устанавливает и считывает свою свежую моментальную копию. См. http://dev.mysql.com/doc/refman/en/innodb-consistent-read.html.

Для чтений с блокировками (SELECT с FOR UPDATE или LOCK IN SHARE MODE), InnoDB блокирует только индексные записи, а не пробелы перед ними, и, таким образом, позволяет свободно вставлять новые записи рядом с заблокированными записями. Для UPDATE и DELETE операторов блокировка зависит от того, использует ли оператор уникальный индекс с уникальным условием поиска (например, WHERE id = 100) или условие поиска диапазона (например, WHERE id > 100). Для уникального индекса с уникальным условием поиска InnoDB блокирует только найденную индексную запись, а не пробел перед ней. Для поисков диапазона InnoDB блокирует прочитанный диапазон индекса, используя блокировки пробелов или блокировки следующих записей (пробел плюс индексная запись) для предотвращения вставки другими сессиями в пробелы, покрываемые диапазоном. Это необходимо, так как для работы репликации и восстановления MariaDB необходимо блокировать "фантомные строки".

Примечание: Если используется уровень изоляции READ COMMITTED или включена системная переменная innodb_locks_unsafe_for_binlog, блокировка пробелов InnoDB отсутствует, за исключением проверки ограничений внешних ключей и проверки дублирующих ключей. Кроме того, блокировки записей для несоответствующих строк снимаются после того, как MariaDB оценит условие WHERE. Если используется READ COMMITTED или включена innodb_locks_unsafe_for_binlog, необходимо использовать построчное двоичное протоколирование.

REPEATABLE READ

Это уровень изоляции по умолчанию для InnoDB. Для согласованных чтений есть важное отличие от уровня изоляции READ COMMITTED: все согласованные чтения в рамках одной транзакции считывают моментальную копию, созданную первым чтением. Эта соглашение означает, что если вы выпустите несколько простых (без блокировок) SELECT операторов в рамках одной транзакции, эти SELECT операторы также согласованы друг с другом. См. http://dev.mysql.com/doc/refman/en/innodb-consistent-read.html.

Для чтений с блокировками (SELECT с FOR UPDATE или LOCK IN SHARE MODE), UPDATE и DELETE операторов, блокировка зависит от того, использует ли оператор уникальный индекс с уникальным условием поиска или условие поиска диапазона. Для уникального индекса с уникальным условием поиска InnoDB блокирует только найденную индексную запись, а не пробел перед ней. Для других условий поиска InnoDB блокирует прочитанный диапазон индекса, используя блокировки пробелов или блокировки следующих записей (пробел плюс индексная запись) для предотвращения вставки другими сессиями в пробелы, покрываемые диапазоном.

Это минимальный уровень изоляции для распределённых XA транзакций.

SERIALIZABLE

Этот уровень аналогичен REPEATABLE READ, но InnoDB неявно преобразует все простые операторы SELECT в SELECT ... LOCK IN SHARE MODE, если autocommit отключён. Если autocommit включён, SELECT является отдельной транзакцией. Следовательно, он известен как только для чтения и может быть сериализован, если выполнен как согласованное (без блокировок) чтение, и не должен блокировать другие транзакции. (Это означает, что чтобы заставить простой SELECT заблокировать, если другие транзакции изменили выбранные строки, необходимо отключить autocommit.)

Распределённые XA транзакции всегда должны использовать этот уровень изоляции.

Режим доступа

Режим доступа определяет, разрешено ли транзакции писать данные. По умолчанию транзакции находятся в режиме READ WRITE (см. системную переменную tx_read_only). Режим READ ONLY позволяет хранилищу данных применять оптимизации, которые нельзя использовать для транзакций, которые записывают данные. Обратите внимание, что в отличие от глобального режима read_only, привилегия READ_ONLY ADMIN (и SUPER до MariaDB 10.11.0) не разрешает записи, и операторы DDL для временных таблиц также не разрешены.

Не разрешается указывать одновременно READ WRITE и READ ONLY в одной команде.

READ WRITE и READ ONLY также могут быть указаны в команде START TRANSACTION, в этом случае указанный режим действителен только для одной транзакции.

Примеры

SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Попытка установить уровень изоляции внутри существующей транзакции без указания GLOBAL или SESSION.

START TRANSACTION;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
ERROR 1568 (25001): Transaction characteristics can't be changed while a transaction is in progress