Oracle mode

Isolation levels supported in Oracle mode

OceanBase Database supports two isolation levels in Oracle mode:

• 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. It is simple, efficient, and suitable for applications with few conflicting transactions.

• 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 settings

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

• Transaction level

  obclient> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
  

• Session level

  obclient> ALTER SESSION SET ISOLATION_LEVEL = SERIALIZABLE;
  

Limits

• 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.