Overview

Isolation levels are used to describe the degree of interference between concurrent transactions. Based on the exceptional situations that must be avoided during transaction execution, the ANSI/ISO SQL standard (SQL 92) defines four isolation levels. The higher the isolation level, the less the mutual impact between transactions, and the fewer exceptional situations are allowed. In the serializable level, which is the highest isolation level, no exceptional situations are allowed. These exceptional situations that need to be avoided include:

• Dirty read: A transaction reads the data that has not been committed by other transactions.

• Non-repeatable read: When you query a row of data that you have read, you find that this row of data has been modified or deleted. 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: When the same query conditions are used again during request execution, the result set reads another eligible row that has been newly inserted by a committed transaction.

Types of isolation levels

The ANSI/ISO SQL standard (SQL 92) defines the following four isolation levels:

• Read uncommitted

• Read committed

• Repeatable read

• Serializable

The following table shows the tolerance of these four isolation levels to the exceptional situations mentioned above.

Isolation levels in OceanBase Database

OceanBase Database supports the following isolation levels:

• Oracle mode

  • Read committed

  • Serializable

• MySQL mode

  • Read uncommitted

  • Read committed

  • Repeatable read

  • Serializable

    The serializable isolation level in the MySQL mode is similar to the serializable level in Oracle Database but does not strictly adhere to the concept of serializable.

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

In actuality, OceanBase Database only implements two isolation levels: read committed and serializable. If you set the isolation level to repeatable read, serializable is implemented actually. If you set the isolation level to read uncommitted, read committed is implemented actually. This means that the repeatable read isolation level in OceanBase Database is more restrictive and does not allow for phenomena like phantom reads. At the read committed isolation level, OceanBase Database does not allow for dirty reads, but it may allow for non-repeatable reads and phantom reads. On the other hand, the serializable level does not allow for dirty reads, non-repeatable reads, or phantom reads.

Read committed isolation level

At the read committed isolation level in OceanBase Database, each SELECT statement can only read data that has been committed by other transactions prior to its execution. The statement will not read data that has been newly committed or modified by concurrent transactions during its execution, as if it obtains the latest snapshot of the current database before the execution of each statement. The snapshot records only the committed data, thus preventing dirty read anomalies. However, because a new snapshot is obtained before each statement is executed, two consecutive SELECT statements in the same transaction may obtain different data. Consequently, the read committed isolation level in OceanBase Database cannot prevent non-repeatable reads and phantom reads.

For update operations such as UPDATE, DELETE, and SELECT FOR UPDATE, their behavior when searching for target rows is similar to that of SELECT. They can only find row versions that were committed before the statement began. If the version does not satisfy the predicate conditions for the update operation, the row is skipped. If it does satisfy the conditions, the row is updated. However, if the current transaction (referred to as transaction A) attempts to update a target row that satisfies the predicate conditions, this row may have already been updated by another concurrent transaction (referred to as transaction B). In this case, if transaction B has not concluded, transaction A must wait for transaction B to commit or roll back. If transaction B rolls back, transaction A can continue updating the target row. If transaction B commits, transaction A will re-execute the statement, obtaining a new snapshot to read the updated version by transaction B. If this version still satisfies the predicate conditions, the update will proceed based on this version.

Repeatable read or serializable isolation level

At the serializable or repeatable read isolation level in OceanBase Database, the first statement of a transaction obtains the current database snapshot as a transaction snapshot, and subsequent SELECT statements will read data based on this transaction snapshot. They can only read data that has been committed by other transactions prior to the transaction snapshot and will not read data that has been newly committed or modified by concurrent transactions. Because each statement uses the same transaction snapshot, the transaction always sees consistent data and does not experience non-repeatable reads or phantom reads.

For update operations such as UPDATE, DELETE, and SELECT FOR UPDATE, their behavior when searching for target rows is similar to that of SELECT. They can only find row versions that were committed before obtaining the transaction snapshot. If the version does not satisfy the predicate conditions for the update operation, the row is skipped. If it does satisfy the conditions, the row is updated. However, if the current transaction (referred to as transaction A) locates a target row that has been updated by another concurrent transaction (referred to as transaction B), and transaction B has not concluded, transaction A must wait for transaction B to commit or roll back. If transaction B rolls back, transaction A can continue updating the originally located row. If transaction B commits, transaction A cannot update the row based on the old snapshot, as this would result in a lost update. Therefore, transaction A can only roll back. At this point, OceanBase Database returns the following error message:

• Oracle mode:

  ORA-08177: can't serialize access for this transaction
  

• MySQL mode:

  ERROR 6235 (25000): can't serialize access for this transaction
  

You need to consider the possibility of transactions rolling back due to write conflicts and prepare transaction retry logic at the business layer. If the transaction is complex and the cost of retrying is high, and the business does not require all statements in the transaction to see consistent data, we recommend that you use the read-committed isolation level.

Limitations of the serializable isolation level in OceanBase Database

In the SQL standard, the serializable isolation level only needs to avoid the anomalies of dirty reads, non-repeatable reads, and phantom reads. However, the strict definition of serializable requires that any two successfully committed concurrent transactions be executed in sequence, with one transaction occurring after the other. In other words, the parallel execution results of transactions should be identical to those of some serial execution. Similar to Oracle and PostgreSQL 9.0 (and earlier), the serializable isolation level in OceanBase Database cannot guarantee strict serializability. This means that the results of transaction execution may not be the same as the results of any serial execution mode. A typical example of this is write skew. Assume there are tables T1(num int) and T2(num int), both initially without any data. At this point, in transaction 1 (Trx1) and transaction 2 (Trx2), the following commands are executed in the following sequence:

Trx1                                                  Trx2
BEGIN;
INSERT INTO T2 SELECT COUNT(*) FROM T1;

                                                      BEGIN;
                                                      INSERT INTO T1 SELECT COUNT(*) FROM T2;
COMMIT;
                                                      COMMIT:

Due to the fact that in the snapshots obtained by Trx1 and Trx2, the value of COUNT is 0 for both tables. Consequently, both table T1 and table T2 will end up having a row inserted with num=0. If the two transactions were executed serially, regardless of the execution order of Trx1->Trx2 or Trx2->Trx1, the final result would be that the two tables should have been inserted with num=0 and num=1, respectively. At present, OceanBase Database cannot guarantee strict serializable isolation level due to its read operations not being locked, lack of mutual exclusion between reads and writes, and the absence of checking for circular read and write conflicts at transaction commit time. In most real-world scenarios, as long as there are no dirty reads, phantom reads, and non-repeatable reads, the business requirements are met. If the business absolutely requires strict serializable isolation, explicit locking can be applied to read operations, such as using the SELECT FOR UPDATE statement.

Comparison of OceanBase Database with other databases in terms of isolation levels