Implementation and Mechanism of MySQL MVCC

Multi-version concurrency control multi-version Concurrency Control
　　

Most MySQL transaction storage engines, such as InnoDB, Falcon, and pbxt, do not use a simple
Single Row lock mechanism.

In fact, they have always been used with another mechanism called "Multi-version concurrency control (MVCC)" to increase concurrency.

MVCC is not only used in MySQL
Oracle, PostgreSQL, and other data are also used by the system.
　　

You can regard MVCC as a compromise of Row-level locks. It avoids the use of locks in many cases and provides lower overhead.

Depending on the implementation, it can allow non-blocking read and only lock necessary records during write operations.
　　

MVCC saves data snapshots at a certain time point.

This means that the producer transaction can see a consistent data view, no matter what they need
How long it will take.

This also means that the data in the same table seen by different transactions at the same time may be different.

If you have never had any experience, it may be abstract,
It is easy to get familiar with this understanding.
　　

The implementation of MVCC varies by storage engine.

Some of these differences include optimistic and pessimistic concurrency control.

We will use a simplified InnoDB version to demonstrate one aspect of MVCC's work.
　　

InnoDB: MVCC is implemented by adding two additional hidden values to each row of records. The two values are one record row.
When the data is created, and when the data in the other row expires (or is deleted ). However, InnoDB does not store the actual time when these events occur. Instead, it only stores the system version when these events occur.
The current number. This is a number that continues to grow with the creation of transactions. Each transaction records its own system version number at the beginning of the transaction. Each query must check the version number of each row of data and whether the transaction version number is
Same. Let's take a look at how this policy applies to specific operations when the isolation level is Repeatable read:
Select InnoDB must have each row of data to ensure that it meets two conditions:
　　

1. InnoDB must find a row version. It must be at least as old as the transaction version (that is, its version number is not greater than the transaction version number ). This ensures that this row of data exists no matter before the transaction starts, when the transaction is created, or when the row of data is modified.
　　

2. The deleted version of this row of data must be undefined or larger than the transaction version. This ensures that the row of data is not deleted before the transaction starts.
　　

Rows that meet these two conditions may be returned as query results.
　　

Insert: InnoDB records the current system version number for this new row.
　　

Delete: InnoDB sets the current system version number to the delete ID of this row.
　　

Update: InnoDB will write a new copy of this row of data. The copied version is the current system version number.

It also writes the version number to the deleted version of the old row.
　　

The result of this additional record is that a lock is not required for most queries.

They simply read data at the fastest speed and ensure that only qualified rows are selected.

The disadvantage of this solution is that the storage engine must store more data for each row, perform more checks, and handle more aftercare operations.
　　

MVCC only works on Repeatable read and read
Under the commited isolation level.

Read
Uncommited is not compatible with MVCC, because the query cannot find the row version suitable for their transaction version;

They can only read the latest version each time.

Seriablable does not match
MVCC compatibility, because read operations will lock each row of data they return [1].

Edit description

By using MVCC (Multi-version concurrency
Control) the algorithm automatically provides concurrency control. MVCC maintains multiple versions of one data so that the read/write operations do not conflict. That is to say, each write operation on Data Element x generates a new version of X.
For each read operation in which GBASE 8 m is X, select a version. The GBASE
Optimized 8 m for better performance. Especially for database read and write methods, they do not have to wait for the same data write and read at the same time. In concurrent transactions, database write only waits
Updating and writing the same row of data is a weakness of the existing row locking method. At the same time, MVCC recycles unnecessary and long-time memory to prevent the waste of memory space. MVCC optimizes database concurrency
System to achieve the highest performance when there are a large number of concurrent users, and you can directly perform Hot Backup without shutting down the server.

This section is more advantageous than locking

The main advantage of using MVCC multi-version concurrency control over the lock model is that in MVCC, the lock requirements for retrieval (read) data do not conflict with the lock requirements for writing data, therefore, read will not block write, and write will never block read.
There are also table and row-level locking mechanisms in the database for applications that cannot easily accept MVCC behavior. However, using MVCC appropriately will always provide better performance than locking.

Edit the features of gbase8 in this section

The query function in GBASE uses MVCC
The provided consistent non-lock read (hereinafter referred to as consistent read) is to provide a snapshot of the database at a time point for information query. The query is only for the transactions committed before this time point.
The changes made do not focus on the changes or uncommitted transactions after the time point. Of course, if the transaction itself changes, the query is visible.
The default GBASE level is read committed. At this isolation level, the transaction query statements use the current timestamp for consistent read. The timestamps of each query are different.
However, at the Repeatable read isolation level, all consistent reads in the same transaction use the timestamp of the first query, this is the data snapshot created by the first read of the transaction. You can obtain a new data snapshot only by submitting the current transaction and sending a new query.
Consistent read is the default mode used by GBASE to process select statements at the Read committed and Repeatable read isolation levels. Consistent read does not set any lock on the data it reads. Therefore, other users can modify the table while reading a table in a consistent manner.
Note that consistent read is invalid when the drop table and alter table operations
. Consistent read is invalid on the drop table because GBASE cannot use a table that has been dropped and the table has been deleted. Consistent read in ALTER TABLE
It is invalid because GBASE
Creates a new table in the transaction and inserts records into the new table. In this way, when the user executes consistent read again, no rows will be visible in the new table, because the data in the new table is read for the first time.
Out of snapshots.

References

• 1

  High-performance MySQL version 2

Additional reading:

• 1

  Http://www.gbase.cn/onlineHelp/html/ManagerManual/ch04020202.html