MySQL multi-version concurrency control (MVCC)

MVCC is a variant of a row-level lock, but it avoids lock-up operations in many cases and therefore costs less. MySQL, including Oracle, PostgreSQL implements MVCC, although each relational database implementation is not the same, but most of the implementation of non-blocking read operations, write operations will only lock the necessary rows.

The implementation principle of MVCC: the implementation principle of InnoDB MVCC is achieved by saving two hidden columns after each row of records. These two columns, one saving the creation time of a row, the expiration time of a saved row, or the time of deletion. MVCC implementation types are optimistic concurrency control and pessimistic concurrency control.

How Select, INSERT, DELETE, and update are performed under MVCC:

SELECT: Two conditions to meet

1. Query only rows with row version number less than the version number of the current transaction

2. The query deletes a row that has a version number that is larger than the current transaction, or that has a deleted version number undefined

Condition one ensures that the read line is already present before it is read and not after the update is read

Condition two ensures that the query has not been deleted before

Condition one and condition two satisfy the repeatable read in MySQL isolation level

INSERT:

Each newly inserted row saves the current system version number as the line version number

DELETE:

Save the current system version for each deleted row as a delete version number.

UPDATE:

Each newly inserted row saves the current system version number as the line version number, while saving the system version number as the deleted version of the original row (which can be seen as an insert and insert operation)

MySQL multi-version concurrency control (MVCC)