Understanding about the MVCC mechanism of InnoDB Engine in MySQL

Currently, I have read High Performance MySQL Second Edition and read the Multiversion Concurrency Control Section to explain how InnoDB implements MVCC. It is a little simple and hard to understand (of course, you may be stupid enough to understand ), so I summarized my understanding. (View translation >>>)

I have extracted a section that I personally think is important in this chapter:

<! -- [Endif] -->

SELECT

InnoDB must examine each row to ensure that it meets two criteria:

• InnoDB must find a version of the row that is at least as old as the transac-

Tion (I. e., its version must be less than or equal to the transaction's version ).

This ensures that either the row existed before the transaction began, or

Transaction created or altered the row.

• The row's deletion version must be undefined or greater than the transac-

Tion's version. This ensures that the row wasn't deleted before the transac-

Tion began.

Rows that pass both tests may be returned as the query's result.

INSERT

InnoDB records the current system version number with the new row.

DELETE

InnoDB records the current system version number as the row's deletion ID.

UPDATE

InnoDB writes a new copy of the row, using the system version number for

New row's version. It also writes the system version number as the old row's

Deletion version.

<! -- [Endif] -->
I have some understanding of the above content (mainly discussing the second point that needs to be met, marked in red): Check occurs when the transaction is committed. Because the insert operation and update deletion occur in different rows concurrently, it is certainly impossible to concurrently occur in the same row, so we will not discuss it much.

Because the update operation is divided into two parts: insert and delete, and insert new rows (concurrent at any time), the update operation can be considered as the same as delete here.

The result is as follows:

Update/Delete -------------------------------- submitted successfully

Update/Delete ----------------------------- failed (because the deletion version has been defined)

Update/Delete -------------------------------- failed (because the deletion version has been defined)

Update/Delete -------- submitted successfully

The deletion version must be a separate version number.