Shulou(Shulou.com)05/31 Report--

Editor to share with you how to achieve the transaction isolation level in MySQL, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

When it comes to database transactions, it must be easy to have a lot of knowledge about transactions popping up in your mind, such as the ACID features of transactions, isolation levels, problems solved (dirty reading, non-repeatable reading, phantom reading), etc., but few people really know how these characteristics of transactions are implemented and why there are four isolation levels.

Today we will first talk about the implementation principle of transaction isolation in MySQL, and we will continue to analyze the implementation principle of other features in the future.

Of course, MySQL is broad and profound, and the omissions in the article are inevitable. Criticism and correction are welcome.

Description

MySQL's transaction implementation logic is located at the engine level, and not all engines support transactions, and the following instructions are based on the InnoDB engine.

Define

Isolation means that after different transactions are committed and executed successively, the final effect is serial, that is to say, for a transaction, the data changes it perceives during execution should only be caused by its own operations, and there are no data changes caused by other transactions.

Isolation solves the problem of concurrent transactions.

Standard SQL isolation level

The simplest way to achieve isolation is for each transaction to be executed in series, and if the previous transaction has not been completed, the subsequent transaction will wait. However, this implementation is obviously not efficient and is not suitable for use in the actual environment.

In order to solve the above problems and achieve different degrees of concurrency control, SQL standard setters propose different isolation levels: uncommitted read (read uncommitted), committed read (read committed), repeatable read (repeatable read), serialized read (serializable). One of the highest isolation levels is serialized reads, while in other isolation levels, problems are more or less allowed because transactions are executed concurrently. See the following matrix table:

Isolation level (+: allowed, -: not allowed) dirty read non-repeatable uncommitted read + + committed read-+ repeatable read-+ serialized read--

Note that MySQL's InnoDB engine solves the phantom reading problem through gap locks at the repeatable level and the non-repeatable reading problem through MVCC, as shown in the following analysis.

Implementation principle Standard SQL transaction isolation level implementation principle

The problem we encountered above is actually the control problem under concurrent transactions, and the most common way to solve concurrent transactions is to be pessimistic about concurrency control (that is, locks in the database). The implementation of the standard SQL transaction isolation level depends on locks, so let's take a look at how it is implemented:

Transaction isolation level implementation uncommitted read (RU) transactions do not lock the data currently being read

The moment a transaction updates some data (that is, the moment when the update occurs), it must first add a row-level shared lock to it until the end of the transaction. The commit read (RC) transaction adds a row-level shared lock to the currently read data (locks are added only when read), and releases the row-level shared lock as soon as the row is read

The moment a transaction updates some data (that is, the moment the update occurs), it must first add a row-level exclusive lock to it until the end of the transaction. The moment a RR transaction reads some data (that is, the moment it starts reading), it must first add a row-level shared lock to it and not release it until the end of the transaction

The moment a transaction updates some data (that is, the moment the update occurs), it must first add a row-level exclusive lock to it until the end of the transaction. When a serialized read (S) transaction reads data, it must first add a table-level shared lock to it until the end of the transaction.

When a transaction updates data, it must first add a table-level exclusive lock to it until the end of the transaction.

As you can see, when only locks are used to achieve isolation level control, frequent locking and unlocking are required, and read-write conflicts are easy to occur (for example, at the RC level, transaction A updates data row 1, transaction B has to wait for transaction A to commit and release the lock to read data row 1 before transaction A commits).

In order to solve the problem of read-write conflicts without locking, MySQL introduces the MVCC mechanism, which can be found in my previous analysis article: reading optimistic locks and pessimistic locks and MVCC in the database.

Implementation principle of InnoDB transaction isolation level

Before we go any further, we have a few concepts to understand:

1. Locked read and consistent non-locked read

Lock read: in a transaction, actively lock a read, such as SELECT. LOCK IN SHARE MODE and SELECT... FOR UPDATE . Row sharing locks and row exclusive locks are added respectively. The classification of locks can be found in my previous analysis article: MySQL lock classification that you should know.

Https://dev.mysql.com/doc/refman/8.0/en/innodb-locking-reads.html

Consistent non-locked read: InnoDB uses MVCC to provide a snapshot of the database at a point in time to a transactional query. The query sees changes made by transactions committed before that point in time, but not changes made by later or uncommitted transactions (except for this transaction). In other words, after the start of the transaction, the data seen by the transaction is the data at the moment when the transaction starts, and subsequent modifications of other transactions will not be visible in this transaction.

Consistent read is the default mode for InnoDB to process SELECT statements at the RC and RR isolation levels. Consistent unlocked reads do not set any locks on the tables they access, so while consistent unlocked reads are performed on tables, other transactions can read or modify them concurrently.

Https://dev.mysql.com/doc/refman/8.0/en/innodb-consistent-read.html

2. Current read and snapshot read

Current read

Read the latest version, such as UPDATE, DELETE, INSERT, SELECT. LOCK IN SHARE MODE 、 SELECT... FOR UPDATE these operations are a kind of current read, why is it called current read? That is, it reads the latest version of the record, ensures that other concurrent transactions cannot modify the current record, and locks the read record.

Snapshot read

What is read is the snapshot version, that is, the historical version, such as the unlocked SELECT operation is the snapshot read, that is, the unlocked non-blocking read; the premise of the snapshot read is that the isolation level is not the uncommitted read level and the serialization read level, because the uncommitted read always reads the latest data rows, not those that match the current transaction version, while the serialization read locks the table.

3. Implicit locking and explicit locking

Implicit locking

InnoDB uses the two-phase locking protocol during transaction execution (where display locking is not actively performed):

Locking can be performed at any time, and InnoDB will automatically add locks when needed according to the isolation level

Locks are released only when commit or rollback is executed, and all locks are released at the same time.

Explicit locking

InnoDB also supports display locking through specific statements (storage engine layer)

Select... Lock in share mode / / shared lock select... For update / / exclusive lock

Display locking for MySQL Server layers:

Lock tableunlock table

After understanding the above concepts, let's take a look at how InnoDB transactions are implemented (the following readings refer to select with non-active locking)

Transaction isolation level implementation uncommitted read (RU) transaction does not lock the data currently being read, it is the current read

The moment a transaction updates some data (that is, the moment when the update occurs), it must first add a row-level shared lock to it until the end of the transaction. Commit read (RC) transactions do not lock the data currently being read and are snapshot reads

The moment a transaction updates some data (that is, the moment the update occurs), it must first add a row-level exclusive lock (Record) to it until the end of the transaction. A RR transaction does not lock the data currently being read and is a snapshot read

The moment a transaction updates some data (that is, the moment the update occurs), it must first add a row-level exclusive lock (Record,GAP,Next-Key) to it until the end of the transaction.

Through gap locking, MySQL solves the problem of phantom reading at this level.

Through snapshots, MySQL solves the problem of non-repeatable reading at this level. Serialize read (S) transactions. When reading data, you must first add a table-level shared lock to it until the end of the transaction, all of which are currently read.

When a transaction updates data, it must first add a table-level exclusive lock to it until the end of the transaction.

As we can see, InnoDB solves the problem of read-write conflict well through MVCC, and solves the phantom reading problem under the standard level one level in advance, which greatly improves the concurrency ability of the database.

Some common misunderstandings about the situation that the bottom package does not include delete?

Non-repeatable read: read a row multiple times before and after, the data content is inconsistent, update and delete operations for other transactions. To solve this problem, use a row sharing lock to lock to the end of the transaction (that is, at the RR level, of course MySQL uses MVCC to solve this problem at the RC level)

Illusory reading: illusory reading occurs when the same query generates different sets of rows at different times, aiming at insert operations of other transactions. To solve this problem, lock the entire table to the end of the transaction (that is, S level, of course, MySQL uses gap locking at the RR level to solve this problem).

When many articles on the Internet refer to fantasy reading and submit reading, some say that fantasy reading includes the situation of delete, and some say that delete should belong to the question of submitting reading, so what is the truth? Let's actually take a look at the official documents of MySQL (below)

The so-called phantom problem occurs within a transaction when the same query produces different sets of rows at different times. For example, if a SELECT) is executed twice, but returns a row the second time that was not returned the first time, the row is a "phantom" row.

Https://dev.mysql.com/doc/refman/5.7/en/innodb-next-key-locking.html

You can see that the phantom reading is aimed at the changes before and after the result set, so it seems that the situation of delete should be classified as illusion. However, when we analyze the implementation principle of the standard SQL listed above at the RR level, we know that the RR level of the standard SQL will add a shared lock to the found data rows, so it is impossible for other transactions to delete these data rows at this time, so under RR. There is no phantom reading due to delete, that is, phantom reading does not contain delete.

Can MVCC solve the problem of phantom reading?

Many articles on the Internet will say that MVCC or MVCC+ gap locks solve the problem of phantom reading, but in fact, MVCC does not solve the problem of phantom reading. Examples are as follows:

Begin;# assumes that the users table is empty and the data found below is empty select * from users; # unlocked # at this time another transaction commits and an id=1 data select * from users; # read snapshot is inserted, and the checked data is empty update users set name='mysql' where id=1;#update is currently read, so the update is successful and an updated snapshot select * from users is generated # read the snapshot and find a record with an id of 1, because MVCC can find the snapshot commit generated by the current transaction

It can be seen that the rows of data checked out before and after are inconsistent, and a phantom reading has occurred. Therefore, only MVCC can not solve the problem of phantom reading, which depends on the gap lock. As follows:

Begin;# assumes that the users table is empty, and the data found below is empty select * from users lock in share mode; # plus shared lock # at this time another transaction B wants to commit and inserts a piece of id=1 data. Because there is a gap lock, you have to wait for select * from users; # to read the snapshot. The checked data is empty and update users set name='mysql' where id=1;#update is currently read. Because there is no data, select * from users will not be updated. # read the snapshot and check that the data is empty. Commit;# transaction B successfully committed and inserted the data.

Note that in order to solve the phantom reading problem at the RR level, we need to explicitly add a lock, otherwise the query will not be locked.

These are all the contents of the article "how to achieve transaction isolation levels in MySQL". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.