MySQL Learning----Multiple Versions concurrent MVCC

Most transactional storage engines in MySQL do not implement simple row-level locks. They generally implement multi-version concurrency control (MVCC) based on the consideration of improving concurrency performance. Not only MySQL, including Oracle,postgresql and other databases have also achieved MVCC, but their respective implementation mechanisms are different, should be MVCC not a unified real standard.

So what is MVVC?

MVCC can be thought of as a variant of row-level locks, but they avoid locking operations in most cases and therefore have lower overhead. Although the implementation of the mechanism is different, but most of the implementation of non-blocking operations, write operations will only lock the necessary rows.

The implementation of MVCC is achieved by saving the data at a time snapshot. That is, the data that each thing sees is consistent no matter how long it takes to execute. Depending on the time at which things start, each thing can see different data on the same table at the same time.

Different storage engines are different for MVCC implementations, typically with optimistic concurrency control and pessimistic concurrency control.

The MVCC of InnoDB is achieved by saving two hidden columns after each row of records. These two columns, one saving the time of creation, one saving the time of expiration (or deletion time). Of course, the storage is not the actual time, but the system version number. Each new thing starts, the system's version number is automatically incremented. The system version number of the start of a thing is the version number of the thing that is used to compare the version number of each row of records queried. Below is a repeatable read isolation level, MVCC specifically how to implement the operation.

Select

InnoDB checks each row's records according to the following two criteria:

A, InnoDB only finds data rows that are older than the current version of the thing (that is, the version number of the line is less than or equal to the system version number of the Thing), which ensures that the line that the thing reads is either already present before the thing starts, or the thing itself is inserted or modified.

b, the deleted version of the row is either undefined or larger than the version number of the current thing. This ensures that the line that the thing reads to is deleted before the thing starts.

Insert

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

Delete

InnoDB saves the current version number for each row deleted as the row delete identity.

Update

Innod to insert a new record, save the current version number as the line version number, and save the current system version number to the original line as the delete identity.

Save these two additional system version numbers, which are the large number of read operations without locking. This design makes reading simple, performs well, and ensures that only rows that conform to the standard are read. The disadvantage is that each line of records requires additional space, more row checking, and some additional maintenance work.

MVCC only works under the two isolation levels of repeatable RED and READ commited. The other two isolation levels are incompatible with MVCC and should be read uncommitted always the newest row, not the data row that conforms to the current thing version. The serializable will lock all read lines.

MySQL Learning----Multiple Versions concurrent MVCC