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
transaction
characteristics. It takes a list of one or more characteristic
values separated by commas. These characteristics set the
transaction isolation
level or access mode. The isolation level is used for
operations on InnoDB
tables. The
access mode may be specified as of MySQL 5.6.5 and indicates
whether transactions operate in read/write or read-only mode.
In addition, SET TRANSACTION
can
include an optional GLOBAL
or
SESSION
keyword to indicate the scope of the
statement.
Scope of Transaction Characteristics
You can set transaction characteristics globally, for the current session, or for the next transaction:
With the
GLOBAL
keyword, the statement applies globally for all subsequent sessions. Existing sessions are unaffected.With the
SESSION
keyword, the statement applies to all subsequent transactions performed within the current session.Without any
SESSION
orGLOBAL
keyword, the statement applies to the next (not started) transaction performed within the current session. Subsequent transactions revert to using theSESSION
isolation level.
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
GLOBAL
or 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
the
--transaction-isolation=
option to mysqld on the command line or in an
option file. Values of level
level
for this
option use dashes rather than spaces, so the permissible 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
It is possible to check or set the global and session transaction
isolation levels at runtime by using the
tx_isolation
system variable:
SELECT @@GLOBAL.tx_isolation, @@tx_isolation;
SET GLOBAL tx_isolation='REPEATABLE-READ';
SET SESSION tx_isolation='SERIALIZABLE';
Similarly, to set the transaction access mode at server startup or
at runtime, use the
--transaction-read-only
option or
tx_read_only
system 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
tx_isolation
or
tx_read_only
is equivalent to
setting the isolation level or access mode with
SET GLOBAL
TRANSACTION
or
SET SESSION
TRANSACTION
.
Transaction Isolation Levels
For information about transaction isolation levels, see Section 14.5.2.1, “Transaction Isolation Levels”.
Transaction Access Mode
As of MySQL 5.6.5, 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 READ
WRITE
.
If the transaction access mode is set to READ
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 WRITE
and READ ONLY
in the same statement.
In read-only mode, it remains possible to change tables created
with the TEMPORARY
keyword using DML
statements. Changes made with DDL statements are not permitted,
just as with permanent tables.
The READ WRITE
and READ ONLY
access modes also may be specified for an individual transaction
using the START
TRANSACTION
statement.