Syntax
------

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

Description
-----------

This statement sets the transaction isolation level or the transaction access
mode globally, for the current session, or for the next transaction:

* With the GLOBAL keyword, the statement sets the default
 transaction level globally for all subsequent sessions. Existing sessions are
 unaffected.
* With the SESSION keyword, the statement sets the default
 transaction level for all subsequent transactions performed within the
 current session.
* Without any SESSION or GLOBAL keyword,
 the statement sets the isolation level for the next (not started) transaction
 performed within the current session.

A change to the global default isolation level requires the SUPER privilege.
Any session is free to change its session isolation level (even in the middle
of a transaction), or the isolation level for its next transaction.

Isolation Level
---------------

To set the global default isolation level at server startup, use the
--transaction-isolation=level option on the command line or in an option file.
Values of level for this option use dashes rather than spaces, so the
allowable values are READ_UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, or
SERIALIZABLE. For example, to set the default isolation level to REPEATABLE
READ, use these lines in the [mysqld] section of an option file:

[mysqld]
transaction-isolation = REPEATABLE-READ
To determine the global and session transaction isolation levels at runtime,
check the value of the tx_isolation system variable (note that the variable
has been renamed transaction_isolation from MariaDB 11.1.1, to match the
option, and the old name deprecated).

SELECT @@GLOBAL.tx_isolation, @@tx_isolation;

From MariaDB 11.1.1:

SELECT @@GLOBAL.transaction_isolation, @@transaction_isolation;

InnoDB supports each of the translation isolation levels described here using
different locking strategies. The default level is REPEATABLE READ. For
additional information about InnoDB record-level locks and how it uses them to
execute various types of statements, see InnoDB Lock Modes, and
http://dev.mysql.com/doc/refman/en/innodb-locks-set.html.

Isolation Levels
----------------

The following sections describe how MariaDB supports the different transaction
levels.

READ UNCOMMITTED
----------------

SELECT statements are performed in a non-locking fashion, but a possible
earlier version of a row might be used. Thus, using this isolation level, such
reads are not consistent. This is also called a "dirty read." Otherwise, this
isolation level works like READ COMMITTED.

READ COMMITTED
--------------

A somewhat Oracle-like isolation level with respect to consistent
(non-locking) reads: Each consistent read, even within the same transaction,
sets and reads its own fresh snapshot. See
http://dev.mysql.com/doc/refman/en/innodb-consistent-read.html.

For locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE), InnoDB locks
only index records, not the gaps before them, and thus allows the free
insertion of new records next to locked records. For UPDATE and DELETE
statements, locking depends on whether the statement uses a unique index with
a unique search condition (such as WHERE id = 100), or a range-type search
condition (such as WHERE id > 100). For a unique index with a unique search
condition, InnoDB locks only the index record found, not the gap before it.
For range-type searches, InnoDB locks the index range scanned, using gap locks
or next-key (gap plus index-record) locks to block insertions by other
sessions into the gaps covered by the range. This is necessary because
"phantom rows" must be blocked for MySQL replication and recovery to work.

Note: If the READ COMMITTED isolation level is used or the
innodb_locks_unsafe_for_binlog system variable is enabled, there is no InnoDB
gap locking except for foreign-key constraint checking and duplicate-key
checking. Also, record locks for non-matching rows are released after MariaDB
has evaluated the WHERE condition.If you use READ COMMITTED or enable
innodb_locks_unsafe_for_binlog, you must use row-based binary logging.

REPEATABLE READ
---------------

This is the default isolation level for InnoDB. For consistent reads, there is
an important difference from the READ COMMITTED isolation level: All
consistent reads within the same transaction read the snapshot established by
the first read. This convention means that if you issue several plain
(non-locking) SELECT statements within the same transaction, these SELECT
statements are consistent also with respect to each other. See
http://dev.mysql.com/doc/refman/en/innodb-consistent-read.html.

For locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE), UPDATE, and
DELETE statements, locking depends on whether the statement uses a unique
index with a unique search condition, or a range-type search condition. For a
unique index with a unique search condition, InnoDB locks only the index
record found, not the gap before it. For other search conditions, InnoDB locks
the index range scanned, using gap locks or next-key (gap plus index-record)
locks to block insertions by other sessions into the gaps covered by the range.

This is the minimum isolation level for non-distributed XA transactions.

SERIALIZABLE
------------

This level is like REPEATABLE READ, but InnoDB implicitly converts all plain
SELECT statements to SELECT ... LOCK IN SHARE MODE if autocommit is disabled.
If autocommit is enabled, the SELECT is its own transaction. It therefore is
known to be read only and can be serialized if performed as a consistent
(non-locking) read and need not block for other transactions. (This means that
to force a plain SELECT to block if other transactions have modified the
selected rows, you should disable autocommit.)

Distributed XA transactions should always use this isolation level.

Access Mode
-----------

The access mode specifies whether the transaction is allowed to write data or
not. By default, transactions are in READ WRITE mode (see the tx_read_only
system variable). READ ONLY mode allows the storage engine to apply
optimizations that cannot be used for transactions which write data. Note that
unlike the global read_only mode, READ_ONLY ADMIN (and SUPER before MariaDB
10.11.0) privilege doesn't allow writes and DDL statements on temporary tables
are not allowed either.

It is not permitted to specify both READ WRITE and READ ONLY in the same
statement.

READ WRITE and READ ONLY can also be specified in the START TRANSACTION
statement, in which case the specified mode is only valid for one transaction.

Examples
--------

SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Attempting to set the isolation level within an existing transaction without
specifying GLOBAL or SESSION.

START TRANSACTION;

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

URL: https://mariadb.com/kb/en/set-transaction/