MySQL transaction isolation level, I believe many readers are familiar with, there are many kinds of related articles, many people for a variety of isolation levels, and different levels can solve some of the reading phenomenon is full of details.

If you are not familiar with this part of the knowledge, you can take a look at the article I wrote a few years ago: Database Read phenomenon analysis introduced what are dirty read, unreal read, non-repeatable read and their background. The in-depth analysis of transaction isolation levels covers database isolation levels and the read problem solved.

As we know, there are four standard isolation levels defined by ANSI/ISO SQL, from highest to lowest: Serializable, Repeatable Reads, Read Committed, Read Uncommitted.

At the RU isolation level, dirty reads, phantom reads, and unrepeatable reads may occur. The RC isolation level solves the problem of dirty reads, including phantom reads and unrepeatable reads. In RR isolation, dirty read and unrepeatable read problems are solved, but phantom read problems exist. The Serializable isolation level solves the problems of dirty read, phantom read, and unrepeatable read.

These four isolation levels are defined by ANSI/ISO SQL standards, and are supported by MySQL. However, Oracle databases only support Serializable and Read Committed.

However, many people may not know that Oracle’s default isolation level is RC, while MySQL’s default isolation level is RR. So, do you know why Oracle chose RC as the default level and MySQL chose RR as the default isolation level? This is a question I’ve asked candidates in previous interviews.

Many people think that this question is meaningless. Isn’t it forcing us to recite the eight-part essay? But it is not. If you can read this article patiently, you will find my good intentions.

Isolation level of Oracle

Oracle supports only ANSI/ISO SQL Serializable and Read Committed isolation levels. Oracle supports three isolation levels:

Oracle supports Read Committed, Serializable, and Read-Only. 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 in a transaction unless the user is SYS.

Of the three Oracle isolation levels, Serializable and Read-Only are clearly unsuitable as the default isolation levels, so Read Committed is the Only choice left.

MySQL isolation level Compared to Oracle, MySQL’s default isolation level has a wider range of options.

First, we excluded Serializable and Read Uncommitted from the four isolation levels, mainly because one isolation level was too high and the other too low. Too high will affect the concurrency, too low will have dirty read phenomenon. So, what’s the choice between RR and RC?

It all started long long ago. MySQL was designed to provide a stable relational database. In order to solve the problems caused by MySQL single point of failure, MySQL adopts the master-slave replication mechanism.

The so-called master-slave replication is actually to build a MySQL cluster to provide services externally. Machines in the cluster are divided into Master servers and Slave servers. The Master server provides write services and the Slave server provides read services.

To ensure data consistency between the primary and secondary servers, data synchronization is required. The general synchronization process is as follows and is not detailed here

The master server records data changes in the bin log and then synchronizes the bin log to the slave server. After receiving the bin log, the slave server restores the data in the bin log to its own database storage.

So, what’s in the binlog? What’s the format? MySQL bin log supports three formats: Statement, Row, and mixed. MySQL started supporting row in 5.1.5 and mixed in 5.1.8.

The difference between a statement and a row is that when a statement is in the statemen format, the binlog contains the original text of the SQL statement. It will be used later).

The differences between the two formats are not explained here. The main reason why ROW format is supported is because there are many problems with statement format. The most obvious problem is that it can cause data inconsistency between primary and secondary databases.

So, what does this master-slave synchronization have to do with the isolation level we’re talking about with bin log? It matters, and it matters a lot.

In the early days of MySQL, the statement bin log format was available only. In this case, the isolation levels of Read Committed and Read Uncommitted could cause problems.

For example, on the MySQL official website, someone has mentioned a related Bug to the official

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 a (a) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; Insert into t1 values (1, 2), (20, 1);Copy the code

We then write two transactions:

After the above two transactions are executed, the records in the database will become (11,2) and (20,2). The data changes sent to the main database will be understood by all.

Because the isolation level of a transaction is read COMMITTED, a row-level lock will be added to row B =2 when transaction 1 updates, and the write operation on row b=1 will not be affected.

UPDATE t1 SET b=2 where b=1 UPDATE T1 SET b=2 where b=1 UPDATE t1 SET a=11 where b=2; (Again, the bin log in statement format records the original text of SQL statements.)

UPDATE T1 SET b=2 where b=1 is executed when SQL statements are played back after bin log is synchronized to the standby database. UPDATE t1 SET a=11 where b=2; .

At this point, the data in the database will become (11, 1, 2) and (11,2). This results in data inconsistency between the primary and secondary databases!!

In order to avoid such problems. Repetable Read is the database’s default isolation level. Repetable Read is the database’s default isolation level.

That’s because Repetable Read, an isolation level, not only adds row-level locks to updated rows, but also adds GAP locks when updating data. In the example above, when transaction 2 is executed, the GAP lock is added to transaction 1, causing the transaction to be stuck until transaction 1 is committed or rolled back. (I’ll cover GAP Lock in a separate article later). In addition to setting the default isolation level, MySQL also disallows READ COMMITTED as the transaction isolation level when the statement format bin log is used.

Once the user actively changes the isolation level and tries to update it, an error is reported:

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

So, now we know why MySQL chose RR as the default database isolation level. In fact, it is to be compatible with the historical statement format bin log. So, at this point in this article, you’ve covered less than 1/5 of the information about MySQL isolation levels. You may also have the following questions:

  • 1. What is the difference between a row format and statement? Can RR be used when using ROW?
  • 2. What exactly is RC GAP Lock mentioned in the text?
  • 3. What’s the difference between RR and RC? How does RC solve the unrepeatable read problem?
  • 4. Since the default MySQL database is RR, why would a large Internet company like Ali change the default isolation level to RC?