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

This article is about how MySQL implements RC transaction isolation. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

The ReadView mechanism is based on a set of read view mechanisms implemented by the undo log version chain, and the transaction generates a ReadView:

If you update the data for the transaction, you can read it.

Or the value of the transaction modification submitted before you generate the ReadView, you can also read

But if you generate the ReadView, the transaction is already active, but if it modifies the data after you generate the ReadView and commits it, you cannot read it at this time

Or the transaction you open after you generate the ReadView modifies the data, commits it, and cannot read it.

So the above mechanism is a principle of ReadView. How to implement RC based on ReadView? Core design: when a transaction sets RC, it regenerates a ReadView every time it initiates a query!

There is a row of data in the database, which is a transaction of transaction id=50, which was inserted a long time ago, and is currently active:

Transaction A (id=60)

Transaction B (id=70)

Now transaction B initiates update and updates this piece of data to b, so the trx_id of the data becomes the id=70 of transaction B and a undo log is generated:

At this point, transaction A will generate a ReadView to initiate a query operation.

At this point, transaction An initiates a query to find the trx_id=70 of the current data. That is, between the transaction id scope of ReadView, it means that there was this active transaction before he generated ReadView, and this transaction modified the value of this data, but transaction B has not been committed at this time, so there are [60,70] two id in ReadView's m_ids active transaction list. At this time, transaction A cannot find the value b modified by transaction B according to the ReadView mechanism.

Then look down the undo log version chain, and you will find an original value and find that its trx_id is 50, which is less than the min_trx_id in the current ReadView, indicating that before he generated ReadView, a transaction inserted this value and committed it long ago, so you can find this original value.

Next, suppose transaction B commits, which means that transaction B will not be active in the database. The next time transaction A queries, you can read the modified value of transaction B. So how on earth can transaction A read the modified value of committed transaction B?

Let transaction An initiate a query next time and regenerate it into a ReadView. Transaction An is the only transaction that is active in the database, so:

Min_trx_id is 60

Mac_trx_id is 71

M_ids=60, the id=70 of transaction B will not appear in the m_ids active transaction list

At this time, transaction A queries based on this ReadView again, and it will find that the trx_id=70 of this data is between the min_trx_id and max_trx_id of ReadView, but it is not in the m_ids list at this time, indicating that transaction B has been committed before this ReadView is generated. This shows that you can find the modified value of transaction B this time, and transaction A will find the value B.

Thank you for reading! This is the end of the article on "how to achieve RC transaction isolation in MySQL". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it out for more people to see!

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.