Isolation levels supported in MySQL mode
The MySQL mode of OceanBase Database supports the following isolation levels:
Read uncommitted: A transaction can read data that has not been committed by another transaction. If transaction A modifies a data item but does not commit the change, transaction B can immediately see the uncommitted changes. If transaction A rolls back the change (cancels the operation), the data read by transaction B becomes "dirty" data that existed before but no longer exists. OceanBase Database currently supports only the read uncommitted isolation level in syntax. If you specify the read uncommitted isolation level, the read committed isolation level is actually used.
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, but OceanBase Database supports only the syntax of the read uncommitted isolation level. OceanBase Database currently supports only the read uncommitted isolation level in syntax. If you specify the read uncommitted isolation level, the read committed isolation level is actually used.
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 | READ UNCOMMITTED];
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 progressThe isolation level set at the global level can be overridden by the isolation level at the session level.