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

This article mainly introduces Mysql isolation level, lock and MVCC what is used, has a certain reference value, interested friends can refer to, I hope you have a lot of gains after reading this article, let Xiaobian take you to understand.

We often have high concurrency and high availability. That is to evaluate from quality and quantity, anything can be analyzed from these two angles. In Mysql, transactions are used to guarantee quality, and MVCC is used to guarantee quantity.

Affairs

We use transactions to ensure that the results of each SQL statement execute as expected. We say transactions must have ACID properties. ACID in the three: atomicity, consistency and persistence are actually described in the same way, to ensure the reliability of SQL execution results. Isolation describes the performance of the database in concurrent scenarios, but the amount of concurrency is not fixed, and different businesses may have different requirements. In order to make the database adapt to different concurrent scenarios, great people define four isolation levels: Read Uncommitted, Read Committed (RC), Repeatable Read (RR), Serializable. As the isolation level of the database increases, the concurrency of the data decreases.

isolation level

See https://www.jb51.net/article/116477.htm for how the database will behave under the standard isolation level. We only discuss the two concepts of shared lock and exclusive lock here. Read plus shared lock, write plus exclusive lock:

In RC isolation level, modify the data will add exclusive lock, transaction end release, other transactions are not allowed to read, solve the dirty read problem. (Shared lock released on the spot)

In RR isolation level, read data plus shared lock, transaction end release, other transactions are not allowed to modify, solve non-repeatable read. (shared lock transaction end release)

They actually serialize operations. Mysql optimizes it. When a transaction reads, other transactions cannot write. When a transaction writes, other transactions cannot read. I can solve dirty reading and non-repeatable reading problems without doing so. MVCC appeared. (This also makes the problem more complicated, and different places start to appear under RR isolation level, which happens to be the default isolation level of Mysql)

MVCC

MVCC is Multiversion Concurrent Control, which uses dual version numbers to solve the problem of data isolation. ("create" is a version number,"delete" is a version number, and modification operations are split into "delete" and "create") Each transaction generates a version number when it starts to add, delete and modify each table. Each transaction can only find data with "create" less than this version number and "delete" greater than this version number. In this way, add and delete operations can be completely concurrent, only the modification operation must be queued. In this way, even if there is no shared lock, it solves the problem of non-repeatable reading, because after other transactions modify, the version number of the data is larger than mine, and I will not read it.

MVCC concurrency at RR isolation level

After the introduction of MVCC, it looks good. However, have you ever thought about two transactions updating a piece of data one after the other, and then two transactions reading that piece of data again, what are they reading? Haha, this is simply impossible, because the modification operation is serial, another transaction must commit this transaction before it can be modified. Okay, change the question. Two transactions perform +1 operation on one piece of data one after another. After another transaction is committed, this transaction adds +1 again. Then read that piece of data. Does this transaction read +1 or +2 result? If it reads +2, doesn't that break isolation and read data committed by other transactions?

However, it is true that other transactions have been committed, and this transaction has modified that data, and of course it has to read +2. Although it was originally 0, this transaction obviously only added 1, but after reading it became 2, a little uncomfortable. Indeed, under the standard RR isolation level, because the operations are serial, after this transaction reads a row of data, other transactions cannot modify this data. This data is always operated by this transaction, so isolation is strictly satisfied. But Mysql's RR enhances concurrency between reads and writes, and only if two transactions modify a piece of data at the same time needs to be serial, all other operations can be parallel. So this results in this kind of result, as if there is an unrepeatable reading. However, this non-repeatable reading is actually in line with our intuitive feelings. After the transaction modifies the data, of course, we must read the latest data.

To analyze the process:

Data create version number is 0

Transaction 1 version number is 1, read data value=0

Transaction 2 version number 2, modified data value+1=1, original data delete version number 2, new data create version number updated to 2, commit

Transaction 1 modifies data value+1=2,(since the modification is currently read, the data with the largest version number is always read, so the value read is 1) delete version number after modification is 1,

The new data create version number is 1

This transaction reads data value=2

In-depth analysis:

In fact, the above description is also vulnerable. What if there is a third transaction version number of 3? Since version number is 3, can I directly read uncommitted data of transactions 1 and 2? In fact, in MVCC, each transaction also has a minimum visible version low_limit_id (records with transaction numbers>= low_limit_id are invisible to the current transaction), filtering out transactions that are currently executing but have not committed. For example, transaction 3, although version number is 3, but low_limit_id=1, so the modifications of transaction 1 and transaction 2 are invisible to 3.

Thank you for reading this article carefully. I hope that the article "Mysql isolation level, lock and MVCC are useful" shared by Xiaobian will be helpful to everyone. At the same time, I hope that everyone will support you a lot and pay attention to the industry information channel. More relevant knowledge is waiting for you to learn!

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.