If InnoDB is the MySQL storage engine and the transaction isolation level is RR repeatable read, it is possible to avoid phantom reads.

However, even after 1202 years, there are still people who say that InnoDB will have phantom reads under the RR isolation level, so they have to rely on gap and next-key locks to prevent phantom reads. At first, I thought InnoDB really didn’t know this point, so I talked to him. Finally, I found out that InnoDB is obsessed with this point.

This will be covered later when we talk about repeatable read isolation levels.

Originally, I thought transaction isolation levels were too simple to talk about, but with that in mind, I’m going to talk about transaction isolation in detail. Next, by the way, I’ll check all of InnoDB’s transaction isolation levels.

ACID

Before we talk about transaction isolation levels, we need to know about the ACID model.

Represent respectively:

  • Atomicity atomic
  • Consistency Consistency
  • The Isolation Isolation
  • Durability persistence

Atomicity means that all operations in an InnoDB transaction will either succeed or fail without being in an intermediate state. More generally, if transaction A fails, all of its changes should be rolled back.

Consistency is mainly to protect the consistency of data and prevent data consistency problems caused by database crash. For example, if we update MySQL data, the updated data will be stored in InnoDB’s Buffer Pool first. If InnoDB has no crash recovery mechanism, the updated data will be lost.

Many other blogs write about the fact that data integrity is not compromised before and after a transaction begins. I said I couldn’t understand it. It was too abstract.

Isolation, mainly refers to the isolation between transactions, and more specifically, the transaction isolation level we will discuss in this article.

Persistence refers to the fact that we add or delete certain data, and once successful, the operation should be persisted to disk.

The ACID model can be understood as a database design paradigm where the focus is on the reliability of the data, and the data itself. InnoDB in MySQL fully adheres to the ACID model and supports data consistency checksum crash recovery at the storage engine level.

The isolated type, as in ACID, is the focus of this article.

Transaction Isolation level

There are many articles that go straight to the types of transaction isolation levels, what they mean, and how they are used. But I think it’s important to understand why a transaction isolation level is needed and what it solves.

We know that InnoDB has multiple transactions that operate on data at the same time. Here are some examples:

  • Suppose transaction A needs A queryid=1But after transaction A finishes querying, transaction B pairsid=1Transaction A executes the query again. Should transaction A see the updated data or the updated data?
  • Or, as in the previous example, transaction A reads transaction B’s data, but what if transaction B rolls back? Doesn’t transaction A’s data become dirty?
  • Or transaction A reads1-3 PMThere are four items in the schedule of transaction A, but transaction A reads the completion of transaction B and then goes1-3Clicking on this time period inserts A new schedule, so if transaction A reads again, should it display 4 schedules or 5?

The transaction isolation level is needed to answer these questions. In fact, the above three cases correspond to unrepeatable reads, dirty reads and phantom reads respectively. InnoDB solves these problems separately with the transaction isolation level. All transaction isolation levels are as follows:

  • READ UNCOMMITTED READ is not committed
  • READ COMMITTED READ COMMITTED
  • REPEATABLE READ REPEATABLE READ
  • The SERIALIZABLE serialization

InnoDB’s default transaction isolation level is REPEATABLE READ.

Read uncommitted

Transaction A reads data that transaction B has not committed

If transaction B makes an error and rolls back, the data read by transaction A becomes dirty, causing dirty reads.

If transaction B updates the data read by transaction A, then transaction A reads again and reads the result of transaction B’s modification, causing non-repeatable reads.

If transaction B adds new data and transaction A reads the new data again, it will read the new data from transaction B, causing A magic read.

So in summary, reading uncommitted at the isolation level causes the following problems:

  • Dirty read
  • Unrepeatable read
  • Phantom read

Reading has been submitted

Transaction A reads the data that transaction B has committed

If transaction B updates the data read by transaction A and commits, then when transaction A reads again, it will read the changes from other transactions, making it non-repeatable.

Similarly, if transaction B adds data and commits, transaction A reads again and gets the data that transaction B just committed, causing A phantom read.

So in summary, reading at the committed isolation level causes:

  • Unrepeatable read
  • Phantom read

Repeatable read

Transaction A will not read the data updated by transaction B, nor will it read the data added by transaction B

In the repeatable read scenario, no dirty or non-repeatable reads occur, but phantom reads may occur.

No matter what transaction B does, transaction A queries data with id=1.

However, in some cases, phantom reading may occur. Repeatable reads only produce phantom reads in some cases, but by no means InnoDB can’t avoid phantom reads. First of all, InnoDB has a very specific way of dealing with phantom reads at the RR isolation level, which is a critical lock, which is a combination of gap and record locks.

Here is an example of when phantom reads occur and when they do not occur at the RR isolation level. The first is the possibility of phantom reading.

SELECT * FROM `student` WHERE `id` > 1
Copy the code

Since InnoDB has MVCC for multiple transactions concurrently, SELECT is a snapshot read and does not lock, so the critical key lock will not work. If another transaction inserts a data, then the transaction may find the data with id > 1 again.

However, this problem can be avoided if the display is locked.

SELECT * FROM `student` WHERE `id` > 1 FOR UPDATE
Copy the code

As to why the key lock can avoid phantom reading, the previous article has been very clear, so I won’t go into the details here.

serialization

So transactions are forced to execute sequentially

This avoids concurrency problems in the first place, but it can degrade MySQL performance. Because now only one transaction can be running at a time.

EOF

So much for transaction isolation levels, and then, when you have time, let’s look at the underlying principles of transaction isolation levels.

This article is available on Github github.com/sh-blog. Welcome Star. Wechat search concern [SH full stack notes], reply [queue] to obtain MQ learning materials, including basic concept analysis and RocketMQ detailed source analysis, continuous update.

If you think this article is helpful to you, please click a like, close a note, share, leave a message.