[Java Interview] Please briefly talk about the transaction isolation level of Mysql

A fan who has worked for 6 years, went to Ali for an interview and was asked "Mysql transaction isolation level" in the first interview.

He didn't answer, and kept complaining to me in private messages.

I said, you can only blame you when you were young. You didn't work hard enough at that time, so you are not skilled enough now.

Well, on this question, look at the answers of ordinary people and experts.

Ordinary people:

There are four transaction isolation levels of Mysql

1. Read Submitted
2. Repeatable read
3. Serialization
4. Uncommitted Read

These four isolation levels represent that I am in a different one...that is, when I compete with multiple transactions, then each of them is an impact on the data when my transaction is executed concurrently and executed in parallel.

For example, there is an isolation between my transactions, so through different isolation levels, it can solve the so-called phantom read or non-repeatable read and said that the read is uncommitted.

Then I remember that the safest one is serialization. Serialization means that it will not have any of the above problems of phantom reading and non-repeatable reading.

Expert:

OK, I will answer this question in several ways.

First of all, the transaction isolation level is a specification to solve the data security problem caused by the competition of multiple parallel transactions.

Specifically, multiple transaction contention may produce three different phenomena.

1. Assuming that two transactions T1/T2 are executing at the same time, the T1 transaction may read the uncommitted data of the T2 transaction, but the uncommitted transaction T2 may be rolled back, which means that the T1 transaction may not be read to the end. Dirty reads occur in existing data.

   

2. Assuming that two transactions T1/T2 are executed at the same time, when transaction T1 reads the same row of data at different times, the results may be different, resulting in the problem of non-repeatable reading.

   

3. Suppose two transactions T1/T2 are executed at the same time. During the process of executing range query or range modification in transaction T1, transaction T2 inserts a piece of data that belongs to the scope of transaction T1 and submits it. At this time, transaction T1 query finds that there is an extra one The data, or the fact that the data has not been modified in the T1 transaction, looks like a hallucination, a phenomenon called phantom read.

   

In practical applications of these three phenomena, some scenarios may not accept the existence of certain phenomena, so four isolation levels are defined in the SQL standard, which are:

1. Reads are not committed. Under this isolation level, dirty reads, non-repeatable reads, and phantom reads may occur.
2. Read committed (RC), at this isolation level, non-repeatable reads and phantom reads may occur.
3. Repeatable Read (RR), at this isolation level, phantom reads may occur
4. Serialization. Under this isolation level, multiple parallel transactions are executed serially without security issues.

Among the four isolation levels, only serialization solves all the problems, but it also means that the performance of this isolation level is the lowest.

In Mysql, the default isolation level of the InnoDB engine is RR (repeatable read), because it needs to ensure the isolation characteristics of the transaction ACID characteristics.

The above is my understanding of the problem.

Summarize

Regarding this issue, many programmers who have used Mysql for 5 years or even longer do not necessarily know very well.

This is actually abnormal, because although the default isolation level of InnoDB can solve more than 99% of the problems, some companies may modify the isolation level for certain businesses.

And if you don't know, it is very likely that inexplicable problems will occur in the program.

If you have any interview questions, career development questions, or study questions, you can PM me.

Copyright notice: All articles in this blog are licensed under CC BY-NC-SA 4.0 unless otherwise stated. Please indicate the source for Mic带你学架构 !
If this article is helpful to you, please help to follow and like, your persistence is the driving force for my continuous creation. Welcome to follow the WeChat public account of the same name to get more technical dry goods!