Introduction to multi-version concurrency control of MySQL database MVCC

MySQL databaseMulti-version Concurrency ControlMVCCThe knowledge is what we will introduce in this article. Let's take a look at this part.

MVCC

Most MySQL transaction storage engines, such as InnoDB, Falcon, and PBXT, do not use a simple row lock mechanism. In fact, they are always using a mechanism called "Multi-version concurrency control MVCC)" to increase concurrency. MVCC is used not only in MySQL, but also in Oracle, PostgreSQL and other data systems.

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 commit transactions can see a consistent data view, no matter how long they need to run. 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, but it will be easy to get familiar with it.

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. These two values record when this row of data is created, when the data in this row expires or is deleted ). However, InnoDB does not store the actual time when these events occur. Instead, it only stores the system version number when these events occur. 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 whether the version number of each row of data is the same as that of the transaction. 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 at the repeatable read and read commited isolation levels. 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 is not compatible with MVCC, because read Operations lock each row of data they return.

Note:

By using MVCCMulti-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, and GBase 8 m selects a version for each read operation on X. GBase 8 m has been optimized to provide better performance because it eliminates conflicts between read and write operations on data elements in the database. 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 for the write that is updating the same row of data. This 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 the database concurrency system so that the system has the highest performance when there are a large number of concurrent users, and can directly perform Hot Backup without shutting down the server.

Better 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 and read data do not conflict with the lock requirements for writing data, so reading will not block writing, writing also never blocks reading. In the database, there are also table and row-level locking mechanisms for applications that cannot easily accept MVCC behavior. However, using MVCC appropriately will always provide better performance than locking.

Features of GBase8

The query function in GBase uses the consistent non-lock read provided by MVCC, which is referred to as consistent read below) to query information through snapshots of a database at a time point. Queries are only changes made to those transactions submitted before this time point, and do not focus on 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 is invalid on alter table because GBase re-creates a new TABLE in the transaction and inserts records from the old TABLE to the new TABLE. In this way, when you execute consistent read again, no rows will be visible in the new table, because the data in the new table is outside the snapshot of the first consistent read.

The introduction of multi-version Concurrency Control for MySQL databases is here. I hope this introduction will be helpful to you!