Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 43.3Mb
PDF (A4) - 43.4Mb
Man Pages (TGZ) - 297.3Kb
Man Pages (Zip) - 402.5Kb
Info (Gzip) - 4.3Mb
Info (Zip) - 4.3Mb
Excerpts from this Manual

15.3.7 SET TRANSACTION Statement

SET [GLOBAL | SESSION] TRANSACTIONtransaction_characteristic [,transaction_characteristic] ...transaction_characteristic: {    ISOLATION LEVELlevel  |access_mode}level: {     REPEATABLE READ   | READ COMMITTED   | READ UNCOMMITTED   | SERIALIZABLE}access_mode: {     READ WRITE   | READ ONLY}

This statement specifiestransaction characteristics. It takes a list of one or more characteristic values separated by commas. Each characteristic value sets the transactionisolation level or access mode. The isolation level is used for operations onInnoDB tables. The access mode specifies whether transactions operate in read/write or read-only mode.

In addition,SET TRANSACTION can include an optionalGLOBAL orSESSION keyword to indicate the scope of the statement.

Transaction Isolation Levels

To set the transaction isolation level, use anISOLATION LEVELlevel clause. It is not permitted to specify multipleISOLATION LEVEL clauses in the sameSET TRANSACTION statement.

The default isolation level isREPEATABLE READ. Other permitted values areREAD COMMITTED,READ UNCOMMITTED, andSERIALIZABLE. For information about these isolation levels, seeSection 17.7.2.1, “Transaction Isolation Levels”.

Transaction Access Mode

To set the transaction access mode, use aREAD WRITE orREAD ONLY clause. It is not permitted to specify multiple access-mode clauses in the sameSET TRANSACTION statement.

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 usingSET TRANSACTION with an access mode ofREAD WRITE.

If the transaction access mode is set toREAD ONLY, changes to tables are prohibited. This may enable storage engines to make performance improvements that are possible when writes are not permitted.

In read-only mode, it remains possible to change tables created with theTEMPORARY keyword using DML statements. Changes made with DDL statements are not permitted, just as with permanent tables.

TheREAD WRITE andREAD ONLY access modes also may be specified for an individual transaction using theSTART TRANSACTION statement.

Transaction Characteristic Scope

You can set transaction characteristics globally, for the current session, or for the next transaction only:

  • With theGLOBAL keyword:

    • The statement applies globally for all subsequent sessions.

    • Existing sessions are unaffected.

  • With theSESSION keyword:

    • The statement applies to all subsequent transactions performed within the current session.

    • The statement is permitted within transactions, but does not affect the current ongoing transaction.

    • If executed between transactions, the statement overrides any preceding statement that sets the next-transaction value of the named characteristics.

  • Without anySESSION orGLOBAL keyword:

    • The statement applies only to the next single transaction performed within the session.

    • Subsequent transactions revert to using the session value of the named characteristics.

    • The statement is not permitted within transactions:

      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

A change to global transaction characteristics requires theCONNECTION_ADMIN privilege (or the deprecatedSUPER privilege). Any session is free to change its session characteristics (even in the middle of a transaction), or the characteristics for its next transaction (prior to the start of that transaction).

To set the global isolation level at server startup, use the--transaction-isolation=level option on the command line or in an option file. Values oflevel for this option use dashes rather than spaces, so the permissible values areREAD-UNCOMMITTED,READ-COMMITTED,REPEATABLE-READ, orSERIALIZABLE.

Similarly, to set the global transaction access mode at server startup, use the--transaction-read-only option. The default isOFF (read/write mode) but the value can be set toON for a mode of read only.

For example, to set the isolation level toREPEATABLE READ and the access mode toREAD WRITE, use these lines in the[mysqld] section of an option file:

[mysqld]transaction-isolation = REPEATABLE-READtransaction-read-only = OFF

At runtime, characteristics at the global, session, and next-transaction scope levels can be set indirectly using theSET TRANSACTION statement, as described previously. They can also be set directly using theSET statement to assign values to thetransaction_isolation andtransaction_read_only system variables:

  • SET TRANSACTION permits optionalGLOBAL andSESSION keywords for setting transaction characteristics at different scope levels.

  • TheSET statement for assigning values to thetransaction_isolation andtransaction_read_only system variables has syntaxes for setting these variables at different scope levels.

The following tables show the characteristic scope level set by eachSET TRANSACTION and variable-assignment syntax.

Table 15.9 SET TRANSACTION Syntax for Transaction Characteristics

SyntaxAffected Characteristic Scope
SET GLOBAL TRANSACTIONtransaction_characteristicGlobal
SET SESSION TRANSACTIONtransaction_characteristicSession
SET TRANSACTIONtransaction_characteristicNext transaction only

Table 15.10 SET Syntax for Transaction Characteristics

SyntaxAffected Characteristic Scope
SET GLOBALvar_name =valueGlobal
SET @@GLOBAL.var_name =valueGlobal
SET PERSISTvar_name =valueGlobal
SET @@PERSIST.var_name =valueGlobal
SET PERSIST_ONLYvar_name =valueNo runtime effect
SET @@PERSIST_ONLY.var_name =valueNo runtime effect
SET SESSIONvar_name =valueSession
SET @@SESSION.var_name =valueSession
SETvar_name =valueSession
SET @@var_name =valueNext transaction only

It is possible to check the global and session values of transaction characteristics at runtime:

SELECT @@GLOBAL.transaction_isolation, @@GLOBAL.transaction_read_only;SELECT @@SESSION.transaction_isolation, @@SESSION.transaction_read_only;