How to choose transaction isolation level in MySQL project

introduction

Let's start with our content. I believe everyone must have encountered the following interview scenario.

Interviewer: "How many transaction isolation levels does MySQL have?"
You: "Read Uncommitted, Read Committed, Repeatable Read, Serializable! The default is Repeatable Read"
Interviewer: "Why does MySQL choose repeatable read as the default isolation level?"
(You look bitter, not knowing how to answer!)
Interviewer: "Which isolation level did you choose in your project? Why?"
You: "Of course it is the default repeatable read. As for the reason... uh..."
(Then you can go back and wait for notification!)

To avoid the embarrassing situation mentioned above, please continue reading!

The default transaction isolation level of Mysql is Repeatable Read. Does Mysql also use the default isolation level in Internet projects without making any changes?
OK, no, we generally use the Read Commited isolation level in our projects!

what! It is actually Read Committed. Doesn't the Internet say that this isolation level has problems不可重復讀and幻讀? Don't worry about it? Okay, let’s start this article with our questions!

text

Let's think about a question first. In Oracle and SqlServer, Read Commited is selected as the default isolation level. Why doesn't MySQL select Read Commited as the default isolation level, but Repeatable Read as the default isolation level?

Why?Why?Why?

This has historical reasons, and of course we have to start with our master-slave replication!
主從復制，是基于什么復制的？

It is based on binlog replication! I don't want to move the concept of binlog here, just simply understand that binlog is a file that records database changes~

binlog有幾種格式？

OK, there are three types, namely

• statement: records the modification SQL statement
• row: records the changes of actual data in each row
• Mixed: a mixture of statement and row modes

Before MySQL 5.0, binlog only supports the STATEMENT format! However, this format has a bug in master-slave replication under the isolation level of Read Commited, so MySQL uses Repeatable Read as the default isolation level!
Next, let's talk about what bugs occur when binlog is in STATEMENT format and the isolation level is Read Commited. As shown in the figure below, execute the following transaction on the master

At this time, execute the following statement on the master

select * from test;

The output is as follows

+---+
| b |
+---+
| 3 |
+---+
1 row in set

However, if you execute this statement on the slave at this time, the output is as follows

Empty set

In this way, you have the problem of master-slave inconsistency! The reason is actually very simple, that is, the order of execution on the master is to delete first and then insert! At this time, binlog is in STATEMENT format, and the order of records is insert first and then delete! The slave is synchronizing with binglog, so the order of execution of the slave is inconsistent with that of the master! Master-Slave

Inconsistent!

How to solve it?

There are two solutions!
(1) The isolation level is set to Repeatable Read, and gap locks are introduced under this isolation level. When Session 1 executes the delete statement, the gap is locked. Then, Ssession 2 will be blocked when executing the insert statement!
(2) Change the format of binglog to row format. This is row-based replication, so there will naturally be no problem of different SQL execution order! However, this format was not introduced until MySQL version 5.1. Therefore, due to historical reasons, MySQL sets the default isolation level to Repeatable Read to ensure that there are no problems with master-slave replication!

So, after we understand why MySQL chooses Repeatable Read as the default isolation level, let's compare it with Read Commited to explain why the isolation level is set to Read Commited in Internet projects!

contrast

Ok, let’s understand one thing first! The two isolation levels of Read UnCommitted and Serializable are not used in the project for two reasons.

• Using Read UnCommitted, a transaction reads data that has not been committed by another transaction. Needless to say, this is illogical!
• When using serialization (Serializable), each read operation will be locked, and snapshot reads will be invalid. This isolation level is generally used when using MySQL's built-in distributed transaction function! (I have never used this feature of MySQL, because it is an XA transaction, a strong consistency transaction, and has poor performance! Internet distributed solutions mostly use eventual consistency transaction solutions!)

In other words, there is only one question we should worry about: should the isolation level be read committed or repeatable read?
Next, let's compare these two levels and talk about why we choose Read Commited as the transaction isolation level!
Assume the table structure is as follows

 CREATE TABLE `test` (
`id` int(11) NOT NULL,
`color` varchar(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB

The data is as follows

+----+-------+
| id | color |
+----+-------+
| 1 | red |
| 2 | white |
| 5 | red |
| 7 | white |
+----+-------+

For ease of description, the following

• Repeatable Read, referred to as RR;
• Read Commited (RC for short);

Reason 1: At the RR isolation level, there is a gap lock, which makes the probability of deadlock much greater than that of RC!
Execute the statement at this time

select * from test where id <3 for update;

At the RR isolation level, there is a gap lock that can lock the gap (2,5) to prevent other transactions from inserting data!
However, at the RC isolation level, there is no gap lock, and other transactions can insert data!

ps : Deadlock does not mean there will be no deadlock at the RC isolation level, but the probability of occurrence is lower than that of RR!

Reason 2: Under the RR isolation level, if the condition column does not hit the index, the table will be locked! In the RC isolation level, only rows are locked <br /> At this time, the statement is executed

update test set color = 'blue' where color = 'white';

At the RC isolation level, it first goes through the clustered index and performs a full scan. The lock is as follows:

But in practice, MySQL has been optimized. When MySQL Server filters the conditions and finds that they are not met, it will call the unlock_row method to lock the records that do not meet the conditions.

The actual locking is as follows

However, at the RR isolation level, the clustered index is scanned completely, and the entire table is locked, as shown below:

Reason three: At the RC isolation level, the semi-consistent read feature increases the concurrency of update operations!

In 5.1.15, InnoDB introduced a concept called "semi-consistent", which reduces conflicts when updating the same row of records and reduces lock waits.
The so-called semi-consistent read means that if an update statement reads a row of locked records, InnoDB returns the most recently submitted version of the record, and the MySQL upper layer determines whether this version meets the update where condition. If the condition is met (update is required), MySQL will re-initiate a read operation, and this time it will read the latest version of the row (and lock it)!
The specific performance is as follows:
At this time there are two sessions, Session1 and Session2!
Session1 execution

update test set color = 'blue' where color = 'red';

Don't Commit the transaction yet!
At the same time, Session2 executes

update test set color = 'blue' where color = 'white';

When session 2 tries to lock the row, it finds that a lock already exists on the row. InnoDB will enable semi-consistent read and return the latest committed versions (1, red), (2, white), (5, red), and (7, white). MySQL will re-initiate the read operation, this time reading the latest version of the row (and locking it)!
At the RR isolation level, Session2 can only wait!

Two questions

At the RC level, does the non-repeatable read problem need to be solved?
No need to solve it, this problem is acceptable! After all, your data has already been submitted, so there is no big problem in reading it out! Oracle's default isolation level is RC. Have you ever changed Oracle's default isolation level?

At the RC level, what binlog format is used for master-slave replication?
OK, under this isolation level, the binlog used is in row format, which is row-based replication! The founder of Innodb also recommends using this format for binlog!

Summarize

This article is just to explain one thing. For Internet projects, please use the isolation level of Read Commited!

This is the end of this article about how to choose the transaction isolation level in MySQL projects. For more information about MySQL transaction isolation levels, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!