Spec-Zone .ru
спецификации, руководства, описания, API
|
|
Spec-Zone .ru
спецификации, руководства, описания, API
|
SET [GLOBAL | SESSION] TRANSACTIONtransaction_characteristic[,transaction_characteristic] ...transaction_characteristic: ISOLATION LEVELlevel| READ WRITE | READ ONLYlevel: 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.
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 or GLOBAL keyword, the statement applies to the next (not started) transaction
performed within the current session.
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 changedwhile 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
levellevel 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.
InnoDB supports each of the transaction isolation levels described here using
different locking strategies. You can
enforce a high degree of consistency with the default REPEATABLE READ level, for operations on crucial data where ACID
compliance is important. Or you can relax the consistency rules with READ COMMITTED or even READ UNCOMMITTED, in situations such as bulk reporting where precise
consistency and repeatable results are less important than minimizing the amount of overhead for locking. SERIALIZABLE
enforces even stricter rules than REPEATABLE READ, and is used mainly in specialized situations, such as with
XA transactions and for troubleshooting issues with
concurrency and deadlocks.
For full information about how these isolation levels work with InnoDB
transactions, see Section 14.2.3.1, "The InnoDB Transaction Model and Locking". In particular, for additional
information about InnoDB record-level locks and how it uses them to execute various
types of statements, see Section 14.2.3.5, "InnoDB Record, Gap, and Next-Key Locks" and Section
14.2.3.7, "Locks Set by Different SQL Statements in InnoDB".
The following list describes how MySQL supports the different transaction levels. The list goes from the most commonly used level to the least used.
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 (nonlocking) SELECT
statements within the same transaction, these SELECT statements are consistent also with respect to each other.
See Section 14.2.3.3, "Consistent Nonlocking
Reads".
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
locks to block insertions by other sessions into the gaps covered by the range.
A somewhat Oracle-like isolation level with respect to consistent (nonlocking) reads: Each consistent read, even within the same transaction, sets and reads its own fresh snapshot. See Section 14.2.3.3, "Consistent Nonlocking Reads".
For locking reads
(SELECT
with FOR UPDATE or LOCK IN SHARE MODE), UPDATE statements, and DELETE statements, InnoDB locks only
index records, not the gaps before
them, and thus permits the free insertion of new records next to locked records.
In MySQL 5.6, when READ COMMITTED isolation level is used,
or the deprecated 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 nonmatching rows are released after
MySQL has evaluated the WHERE condition.
If you use READ COMMITTED or enable innodb_locks_unsafe_for_binlog, you must
use row-based binary logging.
SELECT
statements are performed in a nonlocking 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.
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 (nonlocking) read and need not block for other transactions. (To force a plain SELECT to block if other transactions have modified the selected
rows, disable autocommit.)
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.