Isolation levels supported in MySQL mode
The MySQL mode of OceanBase Database supports the following isolation levels:
Read uncommitted: A transaction can read uncommitted data from another transaction. Once transaction A modifies a data record, transaction B can read the change before it is committed. If transaction A rolls back the change by canceling its operation, the data read by transaction B becomes dirty data, which previously existed but no longer does. OceanBase Database supports the read uncommitted isolation level only in syntax. If you set the isolation level to read uncommitted, read committed is implemented actually.
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 MySQL Database across these four isolation levels.
Read uncommitted: MySQL Database supports the read uncommitted isolation level, while OceanBase Database supports this isolation level only in syntax. If you set the isolation level in OceanBase Database to read uncommitted, read committed is implemented actually.
Read committed: At 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 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 are updated, the determination of whether a row meets the update condition is always based on the version in the statement snapshot.
Repeatable read: At 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 be updated directly on the original version. If the earlier transaction commits, the later transaction will be updated 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 be updated 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 lock (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 | READ UNCOMMITTED];
Limitations
The isolation level cannot be set during transaction execution. Otherwise, the following error is returned:
ERROR 1568 (25001): Transaction characteristics can't be changed while a transaction is in progressThe isolation level set at the global level can be overridden by the isolation level at the session level.