Shulou(Shulou.com)06/03 Report--

This article mainly explains "what is the MVCC mechanism". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Now let the editor take you to learn "what is the MVCC mechanism"?

Review of 1BI undo log version chain

In the last article, we explained what the undo log version chain looks like. "MYSQl Deep Discovery Series two undo log." Let's take a look at that picture.

2. What is readview?

Didn't we say mvcc? Why is there another readview? is there any connection between the two?

Don't worry, listen to me slowly. Let's simply draw a picture and see what this readview is and how it relates to mvcc.

Through the above picture, we can basically distinguish the relationship between the three of them. There are only four main things in readview.

Min_txr_id: the smallest transaction id in the current readview

Max_txr_id: the largest transaction id in current readview

Creator_txr_id: transaction id of this readview

[xx,xx,xx]: id array of currently active transactions

What on earth are these values for? For example, the current readview min_txr_id: 20 max_txr_id: 30 creator_txr_id:20 [20jue 30] then this readview is created by transaction 20. At this time, there are three versions of undo version chain corresponding to txr_id 19 22 29. If you change the transaction, you can only read the version with txr_id 19, but not the other two versions.

3, the isolation level of the transaction and the problems caused by each isolation level

We all know that there are four transaction isolation levels.

Read uncommited: read as commit, that is, you can read the uncommitted data from other transactions. I call it ru for short.

Read committed: read commit, that is, only after another transaction commits can it be read to the current transaction. I call it rc for short.

Repeatable read: can be read repeatedly, and a single transaction can read the same value many times. Below I call it rr for short.

Serializable: serialization, so transactions are queued to execute sequentially. There is no concurrency, and performance is the worst.

What are the common problems with these levels of isolation?

Ru scenario 1: two transactions An and B operate on the same data at the same time. A updates the data value as a but has not yet committed. At this time, B reads a, but transaction A rolls back for some reason, so that transaction B can no longer read a. This is dirty reading.

Scenario 2: there are two AB transactions to update unified data c at the same time, transaction A updates c to a that has not yet been committed, and transaction b updates a with long b and then commits it. But thing A rolled back, causing transaction B to blind the name and update it to b, which is now c, which is dirty writing.

Rc scenario 1: there are two transactions AB running at the same time, transaction A queries 6 rows that meet the conditions according to the prescribed conditions, select count (1) from xxx where age > 18, at this time event B inserts 2 pieces of data and commits transactions, and transaction A queries again and finds that the number of matching entries becomes 8. It is strange that the number of entries found in the same condition of two queries is not the same. This is illusion.

Please scene 2: there are two transaction AB operating the same data c at the same time, and the transaction query c value completes some business operations. At this time, transaction A updates the data c value to an and commits it, but transaction B queries this data again and finds that the value is different? The value of the same data read many times in the same transaction is different, which is non-repeatable.

Although rr solves the problem of repeatability, that is, no matter how many times the data is updated by other transactions, it always reads the same value, but there is still the problem of illusory reading.

4How does read committed support in MySQL

How does mysql support read committed through mvcc? if the isolation level we set in mysql is rc, each query will generate a new readview, which is the key to implementing rc. Let's take a look at the following examples

Or two transactions A (id=20) and B (id=30) operate data c at the same time. Transaction A generates readview 1 min_txr_id=20, max_txr_id=30 for the first query, active transaction [20jue 30]. The undo log version corresponding to creator_txr_id=20 is txr_id=19 and the old value is c.

At this point, transaction B updates c to b and commits, generating a version of undo log txr_id=30, and transaction An initiates a query again to generate a new readview 2 min_txr_id=20,max_txr_id=31, active transaction [20], creator_txr_id=20.

Find that the active transaction is only itself, and then follow the undo log version chain to find txr_id=30. Although its transaction id is larger than itself, it does not exist in the active transaction, indicating that transaction 30 has been committed, so you can see this version at this time, so transaction A reads c value b for the second time.

5How does repeatble read support in MySQL?

Let's take a look at how mysql implements rr through mvcc to solve the problems in unrepeatable and fantasy reading 2 at the same time.

If we set mysql to the rr isolation level, only one readview will be generated per transaction, no matter how many times the current transaction queries the database.

If each transaction reads the same readview, that is, you can see the same version of undo log every time, so the data you see every time is the same, you can solve the problem of repeatable reading and fantasy reading.

At this point, I believe you have a deeper understanding of "what is the MVCC mechanism", might as well come to the actual operation of it! Here is the website, more related content can enter the relevant channels to inquire, follow us, continue 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.