Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 38.5Mb
PDF (A4) - 38.6Mb
PDF (RPM) - 33.3Mb
HTML Download (TGZ) - 8.1Mb
HTML Download (Zip) - 8.2Mb
HTML Download (RPM) - 7.0Mb
Man Pages (TGZ) - 134.2Kb
Man Pages (Zip) - 190.2Kb
Info (Gzip) - 3.4Mb
Info (Zip) - 3.4Mb
Excerpts from this Manual


    transaction_characteristic [, transaction_characteristic] ...

transaction_characteristic: {

level: {

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 to 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 or GLOBAL keyword, the statement applies to the next (not started) transaction performed within the current session. Subsequent transactions revert to using the SESSION isolation level.

A global change to transaction characteristics requires the CONNECTION_ADMIN or 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:

Query OK, 0 rows affected (0.02 sec)

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=level 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 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:

transaction-isolation = REPEATABLE-READ

It is possible to check or set the global and session transaction isolation levels at runtime by using the transaction_isolation system variable:

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 transaction_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 transaction_isolation or transaction_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, “Transaction Isolation Levels”.

Transaction Access Mode

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.

User Comments
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
Sign Up Login You must be logged in to post a comment.