Isolation level

Definition of isolation level

ANSI and ISO/IEC define four isolation levels based on the SQL standard. They are defined based on the situations that must be prevented during the concurrent execution of transactions.

These situations include:

• Dirty read

  One transaction reads the data that has not been committed by other transactions.

• Non-repeatable read

  The row of data you once read is modified or deleted when queried again. For example, for select c2 from test where c1=1;, the value of c2 queried for the first time is 1. When you query c2 again, its value becomes 2 because other transactions have modified the value.

• Phantom read

  A read-only request returns a group of rows that meet the search criteria. When the request is executed the next time, it finds that other rows that meet the criteria have been inserted.

Based on these three situations, four isolation levels are defined:

• Read uncommitted

• Read committed

• Repeatable read

• Serializable

The MySQL mode of OceanBase Database supports Read Committed and Repeatable Read. The Oracle mode supports Read Committed and Serializable. The default isolation level of OceanBase Database in both two modes is Read Committed.

Isolation level settings

Syntax

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

• Transaction level: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

• Session level: ALTER SESSION SET ISOLATION_LEVEL = SERIALIZABLE;

Notes

• You cannot set the isolation level during transaction execution. Otherwise, an error is returned.

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

• You need to make sure that GTS is enabled before enabling 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.

System limits

• Internal transaction

  Internal transactions triggered by user transactions and transactions that maintain internal table information are called internal transactions. OceanBase Database adopts the Read Committed isolation level for internal transactions.

• Cross-tenant transaction

  Some internal tables have not been split and distributed to ordinary tenants. Therefore, cross-tenant transactions may exist in OCP. You cannot run cross-tenant transactions at the Serializable isolation level.