Why does MySQL use RR isolation level by default?

For the default isolation level of the database, Oracle's default isolation level is RC, and MySQL's default isolation level is RR.

So, do you know why Oracle chooses RC as the default isolation level, and MySQL chooses RR as the default isolation level?

Oracle isolation level

Oracle supports two isolation levels, Serializable and Read Committed, defined by ANSI/ISO SQL. According to Oracle's official documentation, Oracle's isolation levels include Read Committed, Serializable and Read-Only.

picture

The isolation level of Read-Only is similar to Serializable, but only allows read-only transactions to retrieve data and does not allow data to be modified in the transaction unless the user is a SYS user.

Among Oracle's three isolation levels, it is obvious that Serializable and Read-Only are not suitable as the default isolation level, so the only option is Read Committed.

MySQL isolation level

MySQL provides a wider range of default isolation levels than Oracle.

First, we excluded the two levels of Serializable and Read Uncommitted because if one isolation level is too high, it will affect concurrency, and if the other is too low, there will be dirty reading problems.

There are two remaining types: RR and RC. How to choose?

MySQL was designed to provide a stable relational database from the beginning. To solve the MySQL single point of failure problem, MySQL adopts a master-slave replication mechanism.

The so-called master-slave replication is to establish a MySQL cluster to provide services to the outside world as a whole. The machines in the cluster are divided into master server (Master) and slave server (Slave). The master server is responsible for providing write services, while the slave server provides read services.

During the MySQL master-slave replication process, data synchronization is performed through binlog. Simply put, the master server records data changes into the binlog, and then synchronously transmits the binlog to the slave server. After receiving the binlog from the server, restore the data in it to its own database storage.

So, what exactly is recorded in the binlog? What is its format?

MySQL's binlog mainly supports three formats, namely statement, row and mixed. MySQL supports row format starting from version 5.1.5, and mixed format starting from version 5.1.8.

The most important difference between statement and row is that when the format of binlog is statement, binlog records the original text of the SQL statement.

Since MySQL only supported statement binlog format in the early days, problems may occur when using the two isolation levels of Read Committed and Read Uncommitted.

For example, there is a database table t1 with the following two records:

CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  KEY `b` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

insert into t1 values(10,1);
1.
2.
3.
4.
5.
6.
7.

Then start executing the write operations of two transactions:

After the above two transactions are executed, there will be only one record in the database (10,99). Everyone can understand this data change that occurred in the main database.

★

Even if the delete operation of Session 1 is submitted after the insert operation of Session 2, due to the isolation level of READ COMMITTED, the insert operation of Session 2 will not see the delete operation of Session 1, so the records inserted by Session 2 will still be left in the database in the end. (10,99).

This behavior is a feature of the READ COMMITTED barrier, which creates a snapshot at the beginning of a transaction. Ensure isolation between transactions and avoid data inconsistency issues.

After the above two transactions are executed, two records will be recorded in the bin log. Because transaction 2 is submitted first, insert into t1 values(10,99); will be recorded first, and then delete from t1 where b < 100; (Remind again: the bin log in statement format records the original text of the SQL statement)

In this way, after the bin log is synchronized to the standby database, when the SQL statement is played back, insert into t1 values(10,99); will be executed first, and then delete from t1 where b < 100; will be executed.

At this time, the data in the database will become EMPTY SET, that is, there will be no data. This results in data inconsistency between the main database and the standby database! ! !

In order to solve this problem, MySQL sets the default isolation level of the database to Repeatable Read. Under the Repeatable Read isolation level, when updating data, not only row-level locks will be added to the updated rows, but also GAP locks and next-key locks will be added. In the above example, when transaction 2 is executed, because transaction 1 adds GAP lock and next-key lock, this will cause the execution of transaction 2 to be blocked, and it needs to wait for transaction 1 to commit or roll back before it can continue execution.

In addition to setting the default isolation level, MySQL also prohibits setting the transaction isolation level to READ COMMITTED when using statement format binlog.

Once the user actively changes the isolation level, an error will be reported when trying to update:

ERROR 1598 (HY000): Binary logging not possible. Message: Transaction level 'READ-COMMITTED' in InnoDB is not safe for binlog mode 'STATEMENT'
1.

Therefore, we now understand why MySQL chooses Repeatable Read as the default database isolation level. In fact, it is to maintain compatibility with the historical statement format binlog.