Levels of transaction isolation

This topic describes the levels of transaction isolation in OceanBase Database and how to configure it.

ANSI, ISO, and IEC define four isolation levels based on the SQL standard. OceanBase Database supports the following isolation levels:

• MySQL mode

  • Read committed

  • Repeatable read

• Oracle mode

  • Read committed

  • Repeatable read

  • Serializable

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

Set isolation level

You can set an 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;
  

Take note of the following items when you set the isolation level:

• You cannot set the isolation level of a transaction that is being executed, otherwise, the following error is reported:

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

• Before you apply the serialization isolation level in an Oracle tenant, make sure that Global Timestamp Service (GTS) is turned on.

  You can execute the following statement to view and enable GTS.

  obclient> SHOW VARIABLES LIKE 'ob_timestamp_service';
  
  obclient> SET GLOBAL ob_timestamp_service=GTS;
  

• You must set the transaction isolation level to the session level when you start a transaction. Session isolation level can be replaced by the transaction isolation level.

  You can execute the following statement to view the current transaction isolation level:

  obclient> SHOW VARIABLES LIKE 'tx_isolation';
  

Limits

• Internal transaction

  An internal transaction can either be triggered by a user transaction or occurs when an internal table is modified. Internal transactions use the read committed isolation level.

• Cross-tenant transaction

  Cross-tenant transactions may occur because some internal tables have not been assigned to common tenants. Under the serialization transaction level, cross-tenant transactions are not allowed in Oracle tenants.