SET [GLOBAL | SESSION] TRANSACTION transaction_characteristic [, transaction_characteristic] ... transaction_characteristic: ISOLATION LEVEL level | READ WRITE | READ ONLY level: REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED | SERIALIZABLE
This statement specifies
characteristics. It takes a list of one or more characteristic
values separated by commas. These characteristics set the
level or access mode. The isolation level is used for
InnoDB tables. The
access mode may be specified as to whether transactions operate in
read/write or read-only mode.
SET TRANSACTION can
include an optional
SESSION keyword to indicate the scope of the
You can set transaction characteristics globally, for the current session, or for the next transaction:
GLOBALkeyword, the statement applies globally for all subsequent sessions. Existing sessions are unaffected.
SESSIONkeyword, the statement applies to all subsequent transactions performed within the current session.
GLOBALkeyword, the statement applies to the next (not started) transaction performed within the current session. Subsequent transactions revert to using the
A global change to transaction characteristics requires the
SUPER privilege. Any session is
free to change its session characteristics (even in the middle of
a transaction), or the characteristics for its next transaction.
SET TRANSACTION without
SESSION is not
permitted while there is an active transaction:
mysql> START TRANSACTION; Query OK, 0 rows affected (0.02 sec) mysql> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; ERROR 1568 (25001): Transaction characteristics can't be changed while a transaction is in progress
To set the global default isolation level at server startup, use
option to mysqld on the command line or in an
option file. Values of
level for this
option use dashes rather than spaces, so the permissible values
SERIALIZABLE. For example, to
set the default isolation level to
REPEATABLE READ, use these
lines in the
[mysqld] section of an option
[mysqld] transaction-isolation = REPEATABLE-READ
It is possible to check or set the global and session transaction
isolation levels at runtime by using the
SELECT @@GLOBAL.transaction_isolation, @@transaction_isolation; SET GLOBAL transaction_isolation='REPEATABLE-READ'; SET SESSION transaction_isolation='SERIALIZABLE';
Similarly, to set the transaction access mode at server startup or
at runtime, use the
--transaction-read-only option or
variable. By default, these are
OFF (the mode
is read/write) but can be set to
ON for a
default mode of read only.
Setting the global or session value of
equivalent to setting the isolation level or access mode with
For information about transaction isolation levels, see Section 18.104.22.168, “Transaction Isolation Levels”.
The transaction access mode may be specified with
SET TRANSACTION. By default, a
transaction takes place in read/write mode, with both reads and
writes permitted to tables used in the transaction. This mode may
be specified explicitly using an access mode of
If the transaction access mode is set to
ONLY, changes to tables are prohibited. This may enable
storage engines to make performance improvements that are possible
when writes are not permitted.
It is not permitted to specify both
READ ONLY in the same statement.
In read-only mode, it remains possible to change tables created
TEMPORARY keyword using DML
statements. Changes made with DDL statements are not permitted,
just as with permanent tables.
READ WRITE and
access modes also may be specified for an individual transaction