In-Depth Analysis of the Mvcc Principle of MySQL | by Dwen | Jul, 2022

MySQL and MVCC

Picture by Liana Mikah on Unsplash

When a number of transactions function on the identical row of knowledge, numerous concurrency issues will happen. MySQL solves these issues by 4 isolation ranges.

The learn uncommitted isolation degree is the loosest, and principally, no isolation is completed, so it is extremely easy to implement.

The read-commit isolation degree is that every time a press release is executed (together with question and replace statements), a constant view is generated to make sure that the present transaction can see the information submitted by different transactions.

The implementation of the repeatable learn isolation degree is that every transaction will generate a constant view when it’s opened. When different transactions are dedicated, it is not going to have an effect on the information within the present transaction. To make sure this, MySQL is thru the multi-version management mechanism MVCC to be realized.

The isolation degree of the serializable isolation degree is comparatively excessive, which is achieved by locking, so MySQL has a set of locking mechanisms.

Each learn commit and repeatable learn isolation ranges rely upon the implementation of the MVCC multi-version management mechanism. Right now we’ll focus on the MVCC multi-version management mechanism in MySQL.

The MVCC mechanism makes use of the read-view mechanism and the undo log model chain comparability mechanism, in order that completely different transactions will learn completely different variations of the identical knowledge on the model chain in line with the information model chain comparability guidelines.

undo log model chain

When a transaction is opened, it is going to first apply for a transaction id: transaction-id

When a transaction modifies a row of knowledge, Mysql will retain the undo rollback log of the information earlier than the modification and assign the transaction id: transaction id to the sphere trx_id within the model report.

Concatenate these undo log logs to kind a historical past model chain, as proven within the determine:

Picture credit score: Creator

Word that the model recorded right here isn’t an actual bodily existence. There may be solely the newest report in actual bodily existence, and different historic information are derived from the rollback log.

Learn-view mechanism

Repeatable learn isolation degree and skim dedicated isolation degree are achieved by producing a constant view, which is read-view.

What’s a constant view?

Picture credit score: Creator

When a transaction begins, InnoDB constructs an array for the transaction to retailer all transaction ids which can be lively in the intervening time the transaction is began. Energetic implies that it was began, however not dedicated.

The smallest worth of id within the array is a low place, and the biggest worth + 1 is recorded as a excessive place, which is the consistency view.

When every transaction makes a question, it is going to deduce the corresponding knowledge within the undo log model chain in line with the visibility guidelines of the consistency view.

  • If the present transaction id falls within the purple half, it implies that this model is a dedicated transaction or is generated by the present transaction itself, and this knowledge is seen.
  • If the present transaction id falls within the blue half, it implies that this model is generated by a transaction began sooner or later, and it’s positively invisible.
  • If the present transaction id falls within the orange half, it contains two instances:

A. If the row trx_id is within the array, it implies that this model is generated by a transaction that has not but been dedicated and isn’t seen.

B. If the row trx_id isn’t within the array, it implies that this model is generated by a dedicated transaction, which is seen.

Picture credit score: Creator

What’s the i queried by transaction A within the determine? Let’s analyze it first.

In line with the order wherein transactions are opened from prime to backside, the consistency view corresponding to every transaction is as follows:

The array of consistency views for transaction A [11]

The array of consistency views for transaction B [11,12]

Consistency view array for transaction C [11, 12, 13]

In the mean time when transaction A is queried, the undo log model chain is:

trx_id=11,id=1,i=10,roll_pointer=0>>>trx_id=13,id=1,i=11,roll_pointer=1>>>trx_id=12,id=1,i=12,roll_pointer=2

stands for model report.

>>> represents the rollback log undo log.

When transaction A is queried, transaction B and transaction C belong to future transactions and are invisible to transaction A.

Due to this fact, the information queried by transaction A is the information obtained by constantly rolling ahead and rolling again in line with the undo log by the newest knowledge report: i = 10.

Picture credit score: Creator

What’s the results of transaction A question 1?

What’s the results of transaction A question 2?

In line with the evaluation of the consistency view visibility rule, for transaction A, transaction B is a future transaction, which is invisible to transaction A, so the question outcome i=10.

The results of question 2 is i = 12, why? Let’s first take a look at two ideas.

Within the repeatable learn isolation degree, the learn technique that finds the corresponding model report by rolling again the log is a constant learn.

As an alternative of rolling again, you solely must learn the newest model of the report is the present learn.

If there’s an replace assertion within the transaction, the replace assertion reads the newest knowledge within the model report within the present learn mode, after which performs the replace operation, so the question outcome within the above determine is i = 12.

The next two question strategies are additionally at present learn:

choose okay from t the place id=1 lock in share mode;

choose okay from t the place id=1 for replace;

The above is the MVCC mechanism. In line with its guidelines, this mechanism is barely accessible beneath the repeatable learn isolation degree and the learn commits isolation degree.

More Posts