Skip to content

Latest commit

 

History

History
73 lines (56 loc) · 2.73 KB

sql-statement-set-transaction.md

File metadata and controls

73 lines (56 loc) · 2.73 KB
title summary
SET TRANSACTION | TiDB SQL Statement Reference
An overview of the usage of SET TRANSACTION for the TiDB database.

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' ( VariableAssignmentList |
    'PASSWORD' ('FOR' Username)? '=' PasswordOpt |
    ( 'GLOBAL'| 'SESSION' )? 'TRANSACTION' TransactionChars |
    'CONFIG' ( Identifier | stringLit) ConfigItemName EqOrAssignmentEq SetExpr )

TransactionChars ::=
    ( 'ISOLATION' 'LEVEL' IsolationLevel | 'READ' 'WRITE' | 'READ' 'ONLY' AsOfClause? )

IsolationLevel ::=
    ( 'REPEATABLE' 'READ' | 'READ' ( 'COMMITTED' | 'UNCOMMITTED' ) | 'SERIALIZABLE' )

AsOfClause ::=
    ( 'AS' 'OF' 'TIMESTAMP' Expression)

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