SET TRANSACTION

Purpose

You can use this statement to set the transaction isolation level.

OceanBase Database supports two isolation levels in MySQL mode: read committed and repeatable read. The default isolation level is read committed.

• You cannot set the isolation level during transaction execution. Otherwise, an error is returned.

  ERROR:ORA-01453: SET TRANSACTION must be first statement of transaction

• You need to make sure that Global Timestamp Service (GTS) is enabled before you enable the serializable isolation level.

• When you run session transactions, you need to set the isolation level to the session level. This isolation level can be overridden by isolation levels at the transaction level.

Syntax

SET [GLOBAL | SESSION | LOCAL] TRANSACTION trans_opt;

trans_opt:
   transaction_access_mode
 | isolation_level
 | transaction_access_mode, isolation_level
 | isolation_level, transaction_access_mode

transaction_access_mode:
    [READ ONLY | READ WRITE]

isolation_level:
    ISOLATION LEVEL {
      READ UNCOMMITED
    | READ COMMITED
    | REPEATABLE READ
    | SERIALIZABLE
  }

Parameters

Examples

• Set the transaction isolation level of the next transaction in the current session to read committed.

   obclient> SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
  

• Set the transaction isolation level of the current session to repeatable read.

   obclient> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;