Rechercher dans le manuel MySQL

13.3.7 SET TRANSACTION Syntax

Table des matières
  1. SET [GLOBAL | SESSION] TRANSACTION
  2.     transaction_characteristic [, transaction_characteristic] ...
  3.  
  4. transaction_characteristic: {
  5.     ISOLATION LEVEL level
  6.   | access_mode
  7. }
  8.  
  9. level: {
  10.      REPEATABLE READ
  11.    | READ COMMITTED
  12.    | READ UNCOMMITTED
  13.    | SERIALIZABLE
  14. }
  15.  
  16. access_mode: {
  17.      READ WRITE
  18.    | READ ONLY
  19. }

This statement specifies transaction characteristics. It takes a list of one or more characteristic values separated by commas. Each characteristic value sets the transaction isolation level or access mode. The isolation level is used for operations on InnoDB tables. The access mode specifies 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.

Transaction Isolation Levels

To set the transaction isolation level, use an ISOLATION LEVEL level clause. It is not permitted to specify multiple ISOLATION LEVEL clauses in the same SET TRANSACTION statement.

The default isolation level is REPEATABLE READ. Other permitted values are READ COMMITTED, READ UNCOMMITTED, and SERIALIZABLE. For information about these isolation levels, see Section 15.7.2.1, “Transaction Isolation Levels”.

Table des matières Haut

Transaction Access Mode

To set the transaction access mode, use a READ WRITE or READ ONLY clause. It is not permitted to specify multiple access-mode clauses in the same SET 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 using SET TRANSACTION with 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.

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.

Table des matières Haut

Transaction Characteristic Scope

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

  • 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.

    • 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 any SESSION or GLOBAL 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:

      1. mysql> START TRANSACTION;
      2. Query OK, 0 rows affected (0.02 sec)
      3.  
      4. mysql> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
      5. ERROR 1568 (25001): Transaction characteristics can't be changed
      6. while a transaction is in progress

A change to global 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 (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 of level for this option use dashes rather than spaces, so the permissible values are READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, or SERIALIZABLE.

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

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

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

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

  • SET TRANSACTION permits optional GLOBAL and SESSION keywords for setting transaction characteristics at different scope levels.

  • The SET statement for assigning values to the transaction_isolation and transaction_read_only system variables has syntaxes for setting these variables at different scope levels.

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

Table 13.7 SET TRANSACTION Syntax for Transaction Characteristics

Syntax Affected Characteristic Scope
SET GLOBAL TRANSACTION transaction_characteristic Global
SET SESSION TRANSACTION transaction_characteristic Session
SET TRANSACTION transaction_characteristic Next transaction only

Table 13.8 SET Syntax for Transaction Characteristics

Syntax Affected Characteristic Scope
SET GLOBAL var_name = value Global
SET @@GLOBAL.var_name = value Global
SET PERSIST var_name = value Global
SET @@PERSIST.var_name = value Global
SET PERSIST_ONLY var_name = value No runtime effect
SET @@PERSIST_ONLY.var_name = value No runtime effect
SET SESSION var_name = value Session
SET @@SESSION.var_name = value Session
SET var_name = value Session
SET @@var_name = value Next transaction only

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

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

Rechercher dans le manuel MySQL

Traduction non disponible

Le manuel MySQL n'est pas encore traduit en français sur l'infobrol. Seule la version anglaise est disponible pour l'instant.

Document créé le 26/06/2006, dernière modification le 26/10/2018
Source du document imprimé : https://www.gaudry.be/mysql-rf-set-transaction.html

L'infobrol est un site personnel dont le contenu n'engage que moi. Le texte est mis à disposition sous licence CreativeCommons(BY-NC-SA). Plus d'info sur les conditions d'utilisation et sur l'auteur.

Références

  1. Consulter le document html Langue du document :en Manuel MySQL : https://dev.mysql.com/

Ces références et liens indiquent des documents consultés lors de la rédaction de cette page, ou qui peuvent apporter un complément d'information, mais les auteurs de ces sources ne peuvent être tenus responsables du contenu de cette page.
L'auteur de ce site est seul responsable de la manière dont sont présentés ici les différents concepts, et des libertés qui sont prises avec les ouvrages de référence. N'oubliez pas que vous devez croiser les informations de sources multiples afin de diminuer les risques d'erreurs.

Table des matières Haut