SET TRANSACTION

The SET TRANSACTION statement can be used to change the current isolation level on a GLOBAL or SESSION basis. This syntax is an alternative to SET transaction_isolation='new-value' and is included for compatibility with both MySQL, and the SQL standards.

Synopsis

SetStmt

SET TRANSACTION - 图1

TransactionChars

SET TRANSACTION - 图2

IsolationLevel

SET TRANSACTION - 图3

AsOfClause

SET TRANSACTION - 图4

  1. SetStmt ::=
  2.     'SET' ( VariableAssignmentList |
  3.     'PASSWORD' ('FOR' Username)? '=' PasswordOpt |
  4.     ( 'GLOBAL'| 'SESSION' )? 'TRANSACTION' TransactionChars |
  5.     'CONFIG' ( Identifier | stringLit) ConfigItemName EqOrAssignmentEq SetExpr )
  7. TransactionChars ::=
  8.     ( 'ISOLATION' 'LEVEL' IsolationLevel | 'READ' 'WRITE' | 'READ' 'ONLY' AsOfClause? )
  10. IsolationLevel ::=
  11.     ( 'REPEATABLE' 'READ' | 'READ' ( 'COMMITTED' | 'UNCOMMITTED' ) | 'SERIALIZABLE' )
  13. AsOfClause ::=
  14.     ( 'AS' 'OF' 'TIMESTAMP' Expression)

Examples

  1. mysql> SHOW SESSION VARIABLES LIKE 'transaction_isolation';
  2. +-----------------------+-----------------+
  3. | Variable_name         | Value           |
  4. +-----------------------+-----------------+
  5. | transaction_isolation | REPEATABLE-READ |
  6. +-----------------------+-----------------+
  7. 1 row in set (0.00 sec)
  9. mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
  10. Query OK, 0 rows affected (0.00 sec)
  12. mysql> SHOW SESSION VARIABLES LIKE 'transaction_isolation';
  13. +-----------------------+----------------+
  14. | Variable_name         | Value          |
  15. +-----------------------+----------------+
  16. | transaction_isolation | READ-COMMITTED |
  17. +-----------------------+----------------+
  18. 1 row in set (0.01 sec)
  20. mysql> SET SESSION transaction_isolation = 'REPEATABLE-READ';
  21. Query OK, 0 rows affected (0.00 sec)
  23. mysql> SHOW SESSION VARIABLES LIKE 'transaction_isolation';
  24. +-----------------------+-----------------+
  25. | Variable_name         | Value           |
  26. +-----------------------+-----------------+
  27. | transaction_isolation | REPEATABLE-READ |
  28. +-----------------------+-----------------+
  29. 1 row in set (0.00 sec)

MySQL compatibility

  • TiDB supports the ability to set a transaction as read-only in syntax only.
  • The isolation levels READ-UNCOMMITTED and SERIALIZABLE are not supported.
  • The REPEATABLE-READ isolation level is achieved through using the snapshot isolation technology, which is partly compatible with MySQL.
  • In pessimistic transactions, TiDB supports two isolation levels compatible with MySQL: REPEATABLE-READ and READ-COMMITTED. For a detailed description, see Isolation Levels.

See also