I understand the principle of MVCC internal implementation

The MySQL InnoDB storage engine, which implements the Concurrency Control Protocol--MVCC (Multi-version Concurrency Control) based on multiple versions (note: Compared to MVCC, is lock-based concurrency control, lock-based Concurrency Control). MVCC The greatest benefits, I believe it is also familiar: Read no lock, read and write no conflict. In an OLTP application that reads and writes less, read-write conflicts are important, greatly increasing the concurrency of the system, which is why at this stage, almost all RDBMS support the MVCC.

　　

For a delete operation, InnoDB is marked for deletion by the line that is about to be deleted, rather than clearing the line immediately, because InnoDB implements the MVCC, which is used to implement the MVCC multi-version mechanism. The lock does not block read, read or block write, this greatly improves the concurrency. So how do you find the version that started with the transaction when you read the consistency?

? primary key index, each row has a transaction ID and an undo ID, which points to the location of the previous version of the line. The non-primary key index (secondary index secondary, index), and then finds the Undo segment by first Brahma the key. For the update operation, the deletion is marked first, then a new row is insert, and then if there is a consistent read, then the principle of finding the line of old version is the same as the delete operation

　　

The InnoDB row record format has a 6-byte transaction ID and a 7-byte rollback pointer that implements MVCC by adding two additional hidden values for each row of records, one that records when this row of data is created, and another that records when this row of data expires (or is deleted). However, InnoDB does not store the actual time at which these events occur, but instead only stores the system version number when these events occur. This is a growing number of transactions as they are created. Each transaction will record its own system version number at the beginning of the transaction. Each query must check whether the version number of each row of data is the same as the version number of the transaction. Let's take a look at how this policy is applied to specific operations when the isolation level is repeatable read.

* Select: When the isolation level is repeatable read when the SELECT operation, InnoDB must each row of data to ensure that it complies with two conditions: 1, InnoDB must find a version of the row, at least as old as the version of the transaction (that is, its version number is not greater than the transaction version number )。 This ensures that the data is present either before the transaction starts, or when the transaction is created, or when the row data is modified. 2. The deleted version of this line of data must be undefined or larger than the transaction version. This ensures that this row of data is not deleted before the transaction begins. Rows that meet both conditions may be returned as query results. * INSERT:
InnoDB records the current system version number for this new line. * DELETE:
InnoDB sets the current system version number to the deletion ID for this row. * UPDATE:
InnoDB will write a new copy of this line of data, the version of which is the current system version number. It also writes this version number to the deleted version of the old row. The result of this extra record is that there is no need to acquire a lock for most queries. They simply read the data as quickly as they can, ensuring that only rows that match the criteria are selected. The disadvantage of this scenario is that the storage engine must store more data for each row,
Do more inspection work and handle more aftercare operations. MVCC only works under repeatable Read and read commited isolation levels. Read uncommited is not MVCC compatible because the query cannot find the row version that is appropriate for their transactional version; they can only read the latest version at a time.
Seriablable is also not compatible with MVCC because the read operation locks each row of data that they return.

　　

Concurrency control Technology:

Lbcc:lock-based Concurrency Control, lock-based concurrency controls. Mvcc:multi-version Concurrency control, which is based on multiple versions of the Concurrency protocol. Because of the low concurrency of the purely lock-based concurrency mechanism, MVCC is an improvement on lock-based concurrency control, which mainly improves concurrency on read operations. In MVCC concurrency control, read operations can be divided into two categories: 1) snapshot read (snapshot read): Read the visible version of the record (possibly a historical version), without the locking (shared read lock S lock is not added, so it does not block the write of other transactions). 2) Current read: Reads the latest version of the record, and the record returned by the current read is added with a lock to ensure that other transactions no longer concurrently modify the record.

　　

I understand the principle of MVCC internal implementation