Why MySQL chooses Repeatable Read as the default isolation level

I believe many readers are familiar with MySQL's transaction isolation levels. There are many related articles on the Internet. Many people are familiar with various isolation levels and some reading phenomena that different levels can solve.

We know that there are four standard isolation levels defined by ANSI/ISO SQL, from high to low: Serializable, Repeatable Reads, Read Committed, and Read Uncommitted.

At the RU isolation level, problems such as dirty reads, phantom reads, and non-repeatable reads may occur. At the RC isolation level, the dirty read problem is solved, but the phantom read and non-repeatable read problems still exist. At the RR isolation level, the problems of dirty reads and non-repeatable reads are solved, but the problem of phantom reads still exists. At the Serializable isolation level, the problems of dirty reads, phantom reads, and non-repeatable reads are solved.

These four isolation levels are defined by the ANSI/ISO SQL standard. The commonly used MySQL supports all four isolation levels. However, Oracle database only supports Serializable and Read Committed .

However, many people may not know that the default isolation level of Oracle is RC, while the default isolation level of MySQL is RR.

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

This is a question I asked candidates during previous interviews.

Many people think that this question is meaningless. Isn’t this forcing us to memorize eight-legged essays?

But actually it is not. If you can read this article patiently, you will find my good intentions .

Oracle Isolation Levels

As we said before, Oracle only supports Serializable and Read Committed defined by ANSI/ISO SQL. In fact, according to the introduction given in Oracle official documents, Oracle supports three isolation levels:

That is, Oracle supports Read Committed, Serializable, and Read-Only.

The Read-Only isolation level is similar to the Serializable isolation level, but read-only transactions do not allow data to be modified within the transaction unless the user is SYS.

Among the three isolation levels of Oracle, Serializable and Read-Only are obviously not suitable as the default isolation level, so Read Committed is the only option left.

MySQL Isolation Levels

Compared to Oracle, MySQL has a wider range of default isolation levels to choose from.

First, we exclude Serializable and Read Uncommitted from the four isolation levels, mainly because one of these two isolation levels is too high and the other is too low. If it is too high, it will affect concurrency, and if it is too low, dirty reads will occur.

So, how to choose between the remaining two types, RR and RC?

This story started a long, long time ago.

When MySQL was designed, its positioning was to provide a stable relational database. In order to solve the problem caused by MySQL single point failure, MySQL adopts the master-slave replication mechanism.

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

In order to ensure the consistency of data between the master and slave servers, data synchronization is required. The general synchronization process is as follows, which will not be described in detail here.

In the process of MySQL master-slave replication, data synchronization is carried out through bin log . In simple terms, the master server records data changes in the bin log, and then synchronously transmits the bin log to the slave server. After the slave server receives the bin log, it restores the data in it to its own database storage.

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

MySQL's bin log mainly supports three formats: statement, row, and mixed. MySQL started supporting row in version 5.1.5 and mixed in version 5.1.8.

The biggest difference between statement and row is that when the binlog format is statemen, the binlog records the original text of the SQL statement (this sentence is very important!!! It will be used later).

The differences between these formats will not be elaborated here. The reason for supporting the row format is mainly because there are many problems in the statement format. The most obvious one is that it may cause data inconsistency between the master and slave databases. For a detailed introduction, please refer to Ding Qi’s sharing on Geek Time, “45 Lectures on MySQL Practice”.

So, what is the relationship between this master-slave synchronization and the isolation level we are going to talk about in bin log?

Yes, it matters, and it’s a big deal.

Because MySQL only had the statement bin log format in the early days, problems would arise if the two isolation levels, Read Committed and Read Uncommitted, were used.

For example, on the MySQL official website, someone once reported a related bug to the official

The process of reproducing this bug is as follows:

There is a database table t1, which contains the following two records:

   CREATE TABLE t1 (

      a int(11) DEFAULT NULL,

      b int(11) DEFAULT NULL,

      KEY a (a)

    )ENGINE=InnoDB DEFAULT CHARSET=latin1;

    insert into t1 values(10,2),(20,1);

Then start executing the write operations of the two transactions:

After the above two transactions are executed, the records in the database will become (11, 2) and (20, 2). Everyone can understand the data changes in the main database.

Because the transaction isolation level is read committed, when transaction 1 updates, it will only add a row-level lock to the row b=2, which will not affect transaction 2's write operation on the row b=1.

After the above two transactions are executed, two records will be recorded in the bin log. Because transaction 2 is committed first, UPDATE t1 SET b=2 where b=1; will be recorded first, and then UPDATE t1 SET a=11 where b=2; will be recorded. (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 replayed, UPDATE t1 SET b=2 where b=1; is executed first, and then UPDATE t1 SET a=11 where b=2; executed.

At this time, the data in the database will become (11, 2) and (11, 2). This results in inconsistent data between the main database and the backup database! ! !

In order to avoid such problems from happening. MySQL sets the default isolation level of the database to Repetable Read. So, how does the Repetable Read isolation level solve this problem?

That is because the Repetable Read isolation level not only adds row-level locks to the updated rows when updating data, but also adds GAP locks . In the above example, when transaction 2 is executed, because transaction 1 adds a GAP lock, the transaction execution will be stuck and will need to wait until transaction 1 is committed or rolled back before it can continue to execute. (Regarding GAP lock, I will write a separate article later).

In addition to setting the default isolation level, MySQL also prohibits the use of READ COMMITTED as the transaction isolation level when using statement-format bin logs.

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'

Summarize

So, now we know why MySQL chooses RR as the default database isolation level. In fact, it is to be compatible with the historical statement format bin log.

So, this article has covered less than 1/5 of the knowledge about MySQL isolation level. After reading this article, you may still have the following questions:

1. What is the difference between row format and statement? Can RR be used when using row?

2. What exactly is the RC GAP lock mentioned in the article?

3. What is the difference between RR and RC? How does RC solve the non-repeatable read problem?

4. Since MySQL database selects RR by default, why would a large Internet company like Alibaba change the default isolation level to RC?

Do you know the answers to the above questions, or which one are you more interested in? Welcome to leave a message! I will select topics that you are more interested in and continue to introduce them in depth in the following articles.

Do you still think this question is meaningless?

I actually want to extend more knowledge through such a seemingly meaningless question, so that I can have a more comprehensive understanding of the candidate.

This concludes this article on why MySQL chooses Repeatable Read as the default isolation level. For more information about MySQL Repeatable Read default isolation level, please search 123WORDPRESS.COM’s previous articles or continue browsing the following related articles. I hope you will support 123WORDPRESS.COM in the future!