My original blog address: blog.csdn.net/weixin_3931…

MVCC(Multi-Version Concurrency Control)

introduce

MySQL implements read committed and repeatable read isolation levels through MVCC. For example, in the previous article on transaction isolation levels, a medium repeatable read. By default, the read and write operations of a row of data are not locked mutually exclusive to ensure isolation, avoiding frequent lock mutually exclusive, while in the serialization isolation level, all operations are locked mutually exclusive to ensure high isolation.

What is MVCC multi-version concurrency control

The main core principle, using mysql undo log version chain. Generates a transaction snapshot (read-view) for committed, ongoing, and ungenerated transactions. Compare the undo log version chain with the read-view to determine which data can be queried. Which data cannot be queried.

Undo log version chain

The undo log version chain indicates thatA line of dataMysql keeps the undo rollback log after each transaction, and concatenates these undo logs with two hidden fields trx_ID and roll_pointer to form a history version chainYou have version chain. We know which transactions have been committed and where my current transaction ID is.

Read-view (consistency view)

A lot of people know about consistency views. But some of you don’t understand. Every time you create a consistent view, you are copying a snapshot of mysql data. If you copy the entire mysql database, you will have to copy it every time you query it. Mysql must not explode. You don’t have to make a new copy of the original data to create a snapshot. I just have to make sure I don’t feel the updates. That’s a snapshot.

So what does mysq do with consistent views?In fact, when mysq generates the consistency view, it mainly determines the above three transaction segments

1. Transactions that have been committed
2. Ongoing transactions (not committed yet)
3. Transactions that have not yet started

So let’s look at them separately how do we possibly find these three paragraphs?

An ongoing transaction

This is simple, mysql has a place to maintain all ongoing transactions. It’s straight out

Transactions that have been committed

At this point, many people say to look at the undo version chain in the largest committed transaction ID, as long as it is smaller than it is ok. That’s not true. For example, 90 is definitely bigger than 89. But the 90 transaction can execute very quickly. It’s done ahead of schedule. Then the largest undo chain is 90, but the smaller 89 is still uncommitted.

So committed transactions = the part less than the maximum committed transaction ID – ongoing transactions

Id of a transaction that has not been started

This is simple, as long as you find the largest transaction ID, greater than it is still the start of the transaction

Repeatable read implementation principle

The first is repeatable reads: in a transaction, the same data is read each time. Other transactions that modify and commit are also unreadable in their own transactions.

How do you do that? In fact, we only need to record what transaction ids were committed when the transaction was first queried. We do not recalculate committed transaction ids throughout the transaction. You can do it. When querying, we only query the transaction ids recorded by our consistency view. Mentioned above: Committed transactions = the part less than the maximum committed transaction ID – ongoing transactions

Read committed implementation principles

Read Committed: In a transaction, each read of the same data may be different. Someone else’s transaction is modified and committed and we can sense it and read it.

How do you do that? Count which transactions have been committed each time a query is initiated, as opposed to the above. I’m going to do it the same way. It’s just that the timing of generating the consistent view has changed. Becomes generated for each query

What is the result set of each of the above SELECT queries at the isolation level of repeatable reads and read committed? So let’s think about it in terms of the calculation above

Repeatable parsing

Read committed parses

Leave it to you, who love to learn. If you really can’t figure it out. Then send me a message

Note: The begin/start transaction commands are not the starting point of a transaction. The transaction is not actually started until the first modification operation is performed on the InnoDB table. The transaction IDS are assigned to mysql strictly in the order in which transactions are started.

Conclusion:

The MVCC mechanism is implemented through the read-view mechanism and the undo version chain comparison mechanism, so that different transactions can read different versions of the same data in the version chain according to the data version chain comparison rule.

BufferPool and mysq

Why can’t Mysql just update the data on disk and set up such a complex mechanism to execute SQL? Because a request comes directly to the disk fileRandom read and write, and then update the data in the disk file. Performance can be quite poor. Because the performance of random disk reads and writes is very poor, updating disk files directly does not make the database resistant to high concurrency. The Mysql system may seem complicated, but it guarantees everyUpdate requests are always to update the memory BufferPool, and then write log files sequentially, which can also ensure data consistency in various abnormal cases. Update memory performance is extremely high,Then sequential writing to the log files on disk also performs much better than random reading and writing to the disk files.It is this mechanism that allows our MySQL database to withstand several stems of read and write requests per second on higher-configuration machines.