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
SETVariableAssignmentListPASSWORDFORUsername=PasswordOptGLOBALSESSIONTRANSACTIONTransactionCharsCONFIGIdentifierstringLitConfigItemNameEqOrAssignmentEqSetExpr
TransactionChars
ISOLATIONLEVELIsolationLevelREADWRITEREADONLYAsOfClause
IsolationLevel
REPEATABLEREADREADCOMMITTEDUNCOMMITTEDSERIALIZABLE
AsOfClause
ASOFTIMESTAMPExpression

Examples

mysql> SHOW SESSION VARIABLES LIKE 'transaction_isolation'; +-----------------------+-----------------+ | Variable_name | Value | +-----------------------+-----------------+ | transaction_isolation | REPEATABLE-READ | +-----------------------+-----------------+ 1 row in set (0.00 sec) mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; Query OK, 0 rows affected (0.00 sec) mysql> SHOW SESSION VARIABLES LIKE 'transaction_isolation'; +-----------------------+----------------+ | Variable_name | Value | +-----------------------+----------------+ | transaction_isolation | READ-COMMITTED | +-----------------------+----------------+ 1 row in set (0.01 sec) mysql> SET SESSION transaction_isolation = 'REPEATABLE-READ'; Query OK, 0 rows affected (0.00 sec) mysql> SHOW SESSION VARIABLES LIKE 'transaction_isolation'; +-----------------------+-----------------+ | Variable_name | Value | +-----------------------+-----------------+ | transaction_isolation | REPEATABLE-READ | +-----------------------+-----------------+ 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

Was this page helpful?