Transaction isolation levels in Oracle mode

Isolation levels supported in Oracle mode

The Oracle mode of OceanBase Database supports the following isolation levels:

• Read committed: A query executed by a transaction can read only the data committed before the query starts. This isolation level cannot prevent non-repeatable or phantom reads.

• Serializable: A query for a transaction can read only the data committed before the transaction starts. This is the strictest isolation level. It can prevent dirty, non-repeatable, and phantom reads. At this isolation level, it seems as if transactions are serially executed.

By default, OceanBase Database uses the read committed isolation level.

Isolation level behavior comparison

Oracle Database supports the read committed and serializable isolation levels. In the Oracle mode of OceanBase Database, the behavior under these two isolation levels is consistent with that of Oracle Database.

Isolation level settings

You can set the isolation level to the transaction level or the session level.

• Transaction level

  SET TRANSACTION ISOLATION LEVEL [READ COMMITTED | SERIALIZABLE];
  

  After you set a transaction isolation level, the isolation level takes effect only for the next transaction and the configurations will not be modified.

• Session level

  ALTER SESSION SET ISOLATION_LEVEL = [READ COMMITTED | SERIALIZABLE];
  

Limitations

• You cannot set the isolation level during transaction execution. Otherwise, the following error is reported:

  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.