Multi-version concurrency control (MMVC) of InnoDB)

InnoDB multi-version concurrency control (MMVC) the MVCC of InnoDB (optimistic lock) is achieved by saving two hidden columns in each row of records. The two columns are the storage creation time and the deletion time. the time here refers to the system version number, which is not the real time value.

The system version number increases automatically every time a new transaction starts. The system version number at the start of the transaction will be used as the transaction version number to compare with the version number of the queried records in each row.

The following describes the specific MVCC operations at the repeatable read isolation level:

SELECT

InnoDB checks each row of records based on the following two conditions:

1. InnoDB only looks for data rows whose versions are earlier than or equal to the current transaction version. This ensures that the rows read by the transaction exist before the transaction starts, or the data inserted or modified by the transaction itself.

2. the row deletion version is either undefined or later than the current transaction version. This ensures that the row won by the transaction is not deleted before the start of the transaction.

Only records that meet the preceding two conditions can be returned as query results.

INSERT

InnoDB saves the current system version number as the row version number for each newly inserted row.

DELETE

InnoDB saves the current system version number for each row to be deleted as the row deletion identifier.

UPDATE

InnoDB inserts a new row, saves the current system version number as the row version number, and saves the current system version number to the original row as the deletion identifier.

Certificate ----------------------------------------------------------------------------------------------------------------------------------------------------------

Save the two additional system versions so that most read operations do not need to be locked. This design makes reading data very simple and has good performance. It can also ensure that only the row that complies with the standard is read. The disadvantage is that each row of record requires additional storage space, more inspection and some additional maintenance work.

MVCC only works at the repeatable read (repeatable read) and read committed (committed read) isolation levels. The other two isolation levels are not compatible with MVCC. Read uncommitted (uncommitted read) always reads the latest data rows, while SERIALIZBLE locks all READ rows.