How MySQL implements RC transaction isolation

The ReadView mechanism is a read view mechanism based on the undo log version chain. Each transaction will generate a ReadView

• If it is updated by the transaction itself Data, you can read

• or the value modified by the transaction submitted before you generatedReadView, or you can read

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

• Or if you generateReadViewand then open a transaction that modifies the data and submits it, it cannot be read

, so the above mechanism isReadViewHow to implement RC based on ReadView? Core design: When a transaction sets RC, it will regenerate a ReadView every time it initiates a query!

There is a row of data in the database, which is a transaction with transaction id=50. It was inserted a long time ago. The current active transaction is:

• Transaction A (id=60)

• Transaction B (id=70)

Now transaction B initiates an update to update this data is b, so at this time the trx_id of the data will become the id=70 of transaction B, and an undo log will be generated at the same time:

At this time, transaction A will be initiated A query operation will generate a ReadView

At this time, transaction A initiates a query and finds that the trx_id=70 of the current piece of data. That is, it belongs to the transaction ID range of ReadView, which means that there was this active transaction before it generated ReadView. It was this transaction that modified the value of this data, but transaction B has not yet been submitted at this time, so the m_ids active transaction list of ReadView Here, there are two IDs [60, 70]. At this time, according to theReadViewmechanism, transaction A cannot find the value b modified by transaction B.

Then search down the undo log version chain, and you will find an original value. You will find that its trx_id is 50, which is smaller than the min_trx_id in the current ReadView. This means that there was a transaction inserted before he generated the ReadView. This value has been obtained and submitted long ago, so the original value can be found.

Assume that transaction B has committed, which means it is no longer an active transaction in the database. The next time transaction A queries, it can read the modified value of transaction B. So how can transaction A be able to read the modified value of submitted transaction B?

Let transaction A initiate a query next time and generate a ReadView. The only active transaction in the database is transaction A, so:

• min_trx_idis 60

• mac_trx_idis 71

• ##m_ids=60, Transaction B's id=70 will not appear in them_idsactive transaction list

At this time, transaction A queries based on this ReadView again, and will find the trx_id of this data =70, although it is between the min_trx_id and max_trx_id range of ReadView, it is not in the m_ids list at this time, indicating that transaction B has been submitted before generating this ReadView. When you do a query operation, you will be able to see the update operation made by transaction B, which will cause transaction A to get the value B.

The above is the detailed content of How MySQL implements RC transaction isolation. For more information, please follow other related articles on the PHP Chinese website!