Overview

Isolation levels are used to describe the degree of interference between concurrently executing 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 search condition is executed again, the result set reads another row that has been newly inserted by a committed transaction.

Types of isolation levels

The ANSI/ISO SQL standard (SQL 92) defines four isolation levels, which are as follows:

• Read uncommitted

• Read committed

• Repeatable read

• Serializable

The tolerance of these four isolation levels to the exceptional situations mentioned above is as follows:

Isolation levels in OceanBase Database

OceanBase Database supports the following isolation levels:

• Oracle mode

  • Read committed

  • Serializable

• MySQL mode

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

The default isolation level in OceanBase Database is read committed.

In actuality, OceanBase Database only implements two isolation levels: read committed and serializable. When a user specifies the repeatable read isolation level, it is actually implemented as serializable. This means that the repeatable read isolation level in OceanBase Database is more restrictive and does not allow for phenomena like phantom reads. In 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

In 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. It will not read data that has been newly committed or modified by concurrent transactions during the statement's execution, as if it obtains the latest snapshot of the current database before the execution of each statement. The snapshot only records committed data, thus preventing dirty read anomalies. However, because a new snapshot is obtained before each statement, two consecutive SELECT statements within the same transaction might see 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 scenario, 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

In 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 based on the old snapshot, as this would result in a lost update situation. Therefore, transaction A can only roll back. At this point, OceanBase Database will return 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
  

The business layer needs to consider the possibility of transactions rolling back due to write conflicts and prepare transaction retry logic. If the transaction is complex and the cost of retrying is high, and the business does not require all statements within the transaction to see consistent data, it is recommended to 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 must execute 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, 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 classic example of this is write skew: Suppose 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 snapshot obtained by Trx1 and Trx2, the COUNT for both tables is 0, consequently, both table T1 and table T2 will end up having a row inserted with num=0. If transactions 1 and 2 were executed serially, whether 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. OceanBase Database currently 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 SELECT FOR UPDATE.

Comparison of OceanBase Database isolation levels with other databases