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

• Repeatable read: The same data read at different times within a transaction is consistent. This isolation level cannot prevent 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 settings

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

• Transaction level

  obclient> SET TRANSACTION ISOLATION LEVEL 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

  obclient> ALTER SESSION SET ISOLATION_LEVEL = 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.