title: SET TRANSACTION summary: TiDB 数据库中 SET TRANSACTION 的使用概况。

SET TRANSACTION

SET TRANSACTION 语句用于在 GLOBALSESSION 的基础上更改当前的隔离级别,是 SET transaction_isolation ='new-value' 的替代语句,提供 MySQL 和 SQL 标准的兼容性。

语法图

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

示例

  1. SHOW SESSION VARIABLES LIKE 'transaction_isolation';
  1. +-----------------------+-----------------+
  2. | Variable_name | Value |
  3. +-----------------------+-----------------+
  4. | transaction_isolation | REPEATABLE-READ |
  5. +-----------------------+-----------------+
  6. 1 row in set (0.00 sec)
  1. SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
  1. Query OK, 0 rows affected (0.00 sec)
  1. SHOW SESSION VARIABLES LIKE 'transaction_isolation';
  1. +-----------------------+----------------+
  2. | Variable_name | Value |
  3. +-----------------------+----------------+
  4. | transaction_isolation | READ-COMMITTED |
  5. +-----------------------+----------------+
  6. 1 row in set (0.01 sec)
  1. SET SESSION transaction_isolation = 'REPEATABLE-READ';
  1. Query OK, 0 rows affected (0.00 sec)
  1. SHOW SESSION VARIABLES LIKE 'transaction_isolation';
  1. +-----------------------+-----------------+
  2. | Variable_name | Value |
  3. +-----------------------+-----------------+
  4. | transaction_isolation | REPEATABLE-READ |
  5. +-----------------------+-----------------+
  6. 1 row in set (0.00 sec)

MySQL 兼容性

  • TiDB 支持仅在语法中将事务设置为只读的功能。
  • 不支持隔离级别 READ-UNCOMMITTEDSERIALIZABLE
  • 通过快照隔离 (Snapshot Isolation) 技术,实现乐观事务的 REPEATABLE-READ 隔离级别,和 MySQL 兼容。
  • 在悲观事务中,TiDB 支持与 MySQL 兼容的 REPEATABLE-READREAD-COMMITTED 两种隔离级别。具体描述详见 Isolation Levels

另请参阅