MYSQL MVCC Implementation and its mechanism

Multi-version concurrency control

Multiversion Concurrency Control

Most of the MySQL storage engines, such as Innodb,falcon, and pbxt, are not simply using the row lock mechanism. They all use row locks combined with a technique for improving concurrency, known as MVCC (multi-version concurrency control). MVCC is not just used in MySQL, other databases such as Oracle,postgresql, and other databases also use this technology.

MVCC avoids many situations that require locking and reduces consumption. It depends on how it is implemented, it allows non-blocking reads, and blocks the necessary records at the time of the write operation.

MVCC holds a snapshot of the data for a moment. It means that no matter how long things run, they can see consistent data. That is to say, at the same time, different things look at the same table data is different. If you have never had this experience, it may be a bit confusing to say that. But in the future this will be easy to understand and familiar with.

Each storage engine implements a different MVCC approach. There are many kinds of concurrency controls that include optimism (optimistic) and pessimism (pessimistic). We use simple innodb behavior to illustrate the way MVCC works.

InnoDB implements MVCC by storing two additional hidden fields for each row, each of which records the time the row was created and the time it was deleted. Each time the event occurs, the version number is stored per row, not when the event actually occurs. Each time the beginning of a thing this version number will increase. Since the recording time, each thing will save the system version number of the record. Check the version number of each line according to the version of the thing. In the case where the object isolation level is repeatable, let's look at how to apply it.

SELECT

InnoDB check each line to make sure it complies with two criteria.

InnoDB must know the version number of the line, the version number of the line is at least as old as the thing version number. (That is, it may have a version number that is less than or the same as the thing version number). This determines whether the line is present before the thing starts, or determines whether the line is created or modified.

The version of the delete operation for a row must be undefined or greater than the version number of the thing. The row was not deleted until the start of the thing was determined.

Meet the above two points. The query results are returned.

INSERT

InnoDB records the system version number of the currently added row.

DELETE

The system version number of the deleted row of the InnoDB record as the deletion ID of the row.

UPDATE

InnoDB copied a row. The version number of this new line uses the system version number. It also takes the system version number as the version of the deleted row.

The results of all other records are saved as queries that have never been locked. This way they can query the data as quickly as possible. Be sure to follow these criteria for query lines. The downside is that the storage engine stores more data for each row, more processing to check the rows, and other internal operations.

MVCC can only take effect under the isolation level of repeatable read and read-committed reads. Non-committed read cannot use it because a row version that conforms to a thing version cannot be read. They always read the latest row version. The reason that serializable cannot use MVCC is that it always locks rows.

The following table shows the modes and concurrency levels for different locks in MySQL.

Lock Policy	Concurrency of	Overhead	Engine
Table	Minimum	Minimum	Myisam,merge,memory
Yes	High	High	NDB Cluster
Line and MVCC	Highest	Highest	Innodb,falcon,pbxt,solidd

MYSQL MVCC Implementation and its mechanism