Transaction isolation levels in Oracle-compatible mode

Isolation levels supported in Oracle-compatible mode

The Oracle-compatible 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-compatible 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];
  

  This statement starts a new transaction and sets the isolation level for that transaction.

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

• The session must maintain the session-level transaction isolation level. When a transaction is started, the session-level isolation level is used. This isolation level can be overridden by the transaction-level isolation level.