Transaction isolation levels in MySQL mode

Isolation levels supported in MySQL mode

The MySQL 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.

• Repeatable read: The same data read at different times within a transaction is consistent.

• Serializable: It is similar to the serializable isolation level in Oracle Database but is not strictly a serializable isolation level.

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

Isolation level behavior comparison

MySQL Database supports four isolation levels: read uncommitted, read committed, repeatable read, and serializable. The MySQL mode of OceanBase Database shows some differences in behavior compared with the MySQL database across these four isolation levels.

• Read uncommitted:

  MySQL Database supports the read uncommitted isolation level, while OceanBase Database does not.

• Read committed:

  In the read committed isolation level, when determining if a row meets the update condition, MySQL Database uses semi-consistent reads. If a row has already been updated by a concurrent transaction, MySQL Database waits for the concurrent transaction to finish and then evaluates the need for an update based on the latest version. On the other hand, in OceanBase Database, regardless of whether concurrent transactions update, the determination of whether a row meets the update condition is always based on the version in the statement snapshot.

• Repeatable read:

  In the repeatable read isolation level, when a write conflict occurs, in MySQL Database, the later transaction will wait for the earlier transaction to finish. If the earlier transaction rolls back, the later transaction will update directly on the original version. If the earlier transaction commits, the later transaction will update on the newly committed version. On the other hand, in OceanBase Database, when a write conflict occurs, the later transaction will wait for the earlier transaction to finish. If the earlier transaction rolls back, the later transaction will update directly on the original version. However, if the earlier transaction commits, the later transaction will roll back and return an error.

• Serializable:

  The serializable isolation level in MySQL Database uses two-phase locking (2PL), which can ensure strict serializability. On the other hand, the serializable isolation level in OceanBase Database uses snapshot isolation, which cannot guarantee strict serializability.

Isolation level settings

There are two ways to set the isolation level, namely the global level and the session level.

SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL [READ COMMITTED | REPEATABLE READ | SERIALIZABLE];

Limitations

• The isolation level cannot be set during transaction execution, otherwise the following error will occur:

  ERROR 1568 (25001): Transaction characteristics can't be changed while a transaction is in progress
  

• The isolation level set at the global level can be overridden by the isolation level at the session level.